SQL Server 2005

Recovery Models and backup

2009-11-04T20:11:00.000-08:00

Backups

Microsoft, in SQL Server Books Online, defines backups as:

A copy of data that is used to restore and recover data after a system failure

SQL Backups can be created a number of ways and can incorporate all or some of the data, as well as some part of the transaction log. While this article is focused on 2005 syntax, most of the concepts are applicable to 2000. This is a huge topic. At best, I'm going to scratch the surface and give you enough information so you won't start crying again. After reading this, you should be able to set up a reasonable set of backups for your system.

Recovery Models

In order to begin working on backups, the business needs define a database recovery model. In essence, a recovery model defines what you're going to do with the transaction log data.

There are three recovery models: Full, Simple and Bulk Logged. These are pretty easy to define:

Simple – in simple recovery mode, the transaction log is not backed up so you can only recover to the most recent full or differential backup.
Full – in full recovery mode you backup the database and the transaction log so that you can recover the database to any point in time.
Bulk Logged – in bulk logged mode, most transactions are stored in the transaction log, but some bulk operations such as bulk loads or index creation are not logged.

The two most commonly used modes are Simple and Full. Don't necessarily assume that, of course, you always need to use Full recovery to protect your data. It is a business decision. The business is going to tell you if you need to recover to a point in time or if you simply need the last full backup. It's going to define if your data is recoverable by other means, such as manual entry, or if you have to protect as much as possible as it comes across the wire. You use Simple recovery if you can afford to lose the data stored since the last full or differential backup and/or you just don't need recovery to a point in time. In Simple mode, you must restore all secondary read/write file groups when you restore the primary. You use Simple mostly on secondary databases that are not an absolute vital part of the enterprise or reporting systems, with read only access so there isn't a transaction log to worry about anyway. You use Full if every bit of the data is vital, you need to recover to a point in time or, usually in the case of very large databases (VLDB), you need to restore individual files and file groups independently of other files and file groups.

With both Simple and full recovery models, you can now run a Copy-Only backup which allows you to copy the database to a backup file, but doesn't affect the log, differential backup schedules or impact recovery to a point in time. I'll try to drill down on as many of these topics as possible through the article, but not the files and filegroups.

Working with Simple Recovery

Enough talk. Let's get down to running backups. Let's assume that we're in Simple recovery on a small to mid-sized database. I'm going to use AdventureWorks for all the sample scripts. To set it to simple recovery:

ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE

Your simplest backup strategy is to run, at regular intervals, the following SQL Server backup command, which will perform a full backup of the database:

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\Backups\AdventureWorks.BAK'

What's with all the typing you ask? Don't we have GUI tools to handle the work for us? Yes, most simple backups can be performed using SQL Server Management Studio. However, if you want to learn and understand what Management Studio is doing for you, or if you want some fine grained control over what is backed up, how and where, then you're going to have to break out the keyboard and put away the mouse.

The above command will precipitate a basic backup to disk. Most DBAs I know backup to file and then scrape the files onto a tape or some other media. This is because files on disk are simple and quick to recover, whereas media can sometimes be a bit of a pain. For example, we generally have two to three days worth of backups on our file systems for immediate recovery. We only go to the tape systems if we need to run restores for older backups.

What did that command do? It made a copy of all the committed data in the database. It also copied uncommitted log entries. These are used during recovery to either commit or rollback changes that occurred to the data during the backup process.

Copy-only backups

Normally, backing up a database affects other backup and restore processes. For example after running the previous command, any differential backups (a backup that only copies data changed since the last backup) would be using this as the starting point for data changes, not the backup you ran last night. As noted earlier, SQL 2005 introduces a new concept to backups, COPY_ONLY backups, which allow us to keep from interrupting the cycle:

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
WITH COPY_ONLY;

Already we've found one of those more granular moments when the Management Studio wouldn't help you. If you want a copy only backup, you have to use the command line.

Differential backups

Let's assume for a moment, that we're still in simple recovery, but we're dealing with a larger database, say something above 100 GB in size. Full backups can actually start to slow down the process a bit. Instead, after consultation with the business, we've decided to do a weekly full backup and daily differential backups. Differential backups only backup the data pages that have changed since the last full backup. Following is the SQL backup command to perform a differential backup:

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\backups\AdventureWorks.bak'
WITH DIFFERENTIAL;

Now, if we had to restore this database, we'd first go to the last full backup, restore that, and then restore the differential backups in order (more on that later).

You might have noticed, if you're trying these commands out as you go along, that we have not been changing the backup file names each time. Yet we haven't run into any errors. When running backups as we've done, SQL Server treats the file like a backup device and simply keeps appending the backups to the file.

If you want to simply overwrite the existing file you'll need to modify your backup statements:

BACKUP DATABASE Adventureworks
TO DISK = 'C:\backups\AdventureWorks.bak'
WITH INIT;

There are a number of other backup options that I won't be detailing here. Read the books online to see details on BLOCKSIZE, EXPIREDATE, RETAINDAYS, PASSWORD, NAME, STATS, and so on.

You can also run a statement that will check the integrity of a database backup. It doesn't check the integrity of the data within a backup, but it does verify that the backup is formatted correctly and accessible.

RESTORE VERIFYONLY
FROM DISK = 'C:\backups\Adventureworks.bak'

Full recovery and log backups

We've primarily been working on a database that was in Simple recovery mode (this used to be called Truncate Log on Checkpoint). In this mode, we do not backup the transaction logs for later recovery. Every backup under this mechanism is a database backup. Log backups are simply not possible.

However, you've only protected the data as of the last good backup, either full or differential. Let's change our assumptions. Now we're dealing with a large, mission critical application and database. We want to be able to recover this database up to the latest minute. This is a very important point. In theory, since the log entries are being stored and backed up, we're protected up to the point of any failure. However, some failures can cause corruption of the log, making recovery to a point in time impossible. So, we have to determine what the reasonable minimum time between log backups will be. In this case we can live with no more than 15 minutes worth of lost data.

So, let's start by putting our database in FULL recovery mode:

ALTER DATABASE AdventureWorks SET RECOVERY FULL

Then, on a scheduled basis, in this case every 15 minutes, we'll run the SQL backup command for the transaction log:

BACKUP LOG Adventureworks 
TO DISK = 'C:\backups\AdventureWorks_Log.bak';

This script will backup committed transactions from the transaction log. It has markers in the file that show the start and stop time. It will truncate the log when it successfully completes, cleaning out from the transaction log the committed transactions that have been written to the backup file. If necessary, you can use the WITH NO_TRUNCATE statement to capture data from the transaction log regardless of the state of the database, assuming it's online and not in an EMERGENCY status. This is for emergencies only.

Note that we are not using the INIT statement in this case, but you can do so if you choose. When doing log backups, you've got options:

Run all the backups to a single file, where they'll stack and all you have to do, on restore (covered later), is cycle through them.
Name the backups uniquely, probably using date and time in the string.

In that latter case, safety says, use INIT because you're exercising maximum control over what gets backed up where, and you'll be able to know exactly what a backup is, when it was taken and from where based on the name. This is yet another place where operating backups from the command line gives you more control than the GUI. We've used both approaches in our systems for different reasons. You can decide what is best for your technology and business requirements.

Most of the options available to the database backup are included in Log backup, including COPY_ONLY. This would allow you to capture a set of transaction data without affecting the log or the next scheduled log backup. This would be handy for taking production data to another system for troubleshooting etc.

If you have your database set to FULL Recovery, you need to run log backups. Sometimes, people forget and the transaction log grows to the point that it fills up the disk drive. In this case, you can run:

BACKUP LOG Adventureworks WITH NO_LOG;

Attaching NO_LOG to the log backup, and not specifying a location for the log, causes the inactive part of the log to be removed and it does this without a log entry itself, thus defeating the full disk drive. This is absolutely not recommended because it breaks the log chain, the series of log backups from which you would recover your database to a point in time. Microsoft recommends running a full backup immediately after using this statement. Further, they're warning that this statement may be deprecated in a future release.

Restoring Databases

As important as SQL Server backups are, and they are vital, they are useless without the ability to restore the database.

Restoring a full database backup

Restoring a full database backup is as simple as it was to create:

RESTORE DATABASE Adventureworks 
FROM DISK = 'C:\Backup\AdventureWorks.bak';

It's really that simple – unless, as we did originally, we are backing up everything to a file as if it were a backup device. In that case, you'll need to specify which file within the "device" you're accessing. If you don't know which file, you'll need to generate a list:

RESTORE HEADERONLY 
FROM DISK = 'C:\Backup\Adventureworks.bak';

This will give you the same list as I showed above from Management Studio. So now, if we wanted to restore the second file in the group, the COPY_ONLY backup, you would issue the following command:

RESTORE DATABASE AdventureWorks 
FROM DISK = 'C:\Backup\Adventureworks.bak' 
WITH FILE = 2;

Unfortunately, if you're following along, you may find that you just generated this error:

Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "AdventureWorks" has not been backed up.
Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do
not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

What this means is, that your database is in full recovery mode, but you haven't backed up the "tail of the log", meaning the transactions entered since the last time you ran a backup. You can override this requirement if you change the previous syntax to:

RESTORE DATABASE AdventureWorks 
FROM DISK = 'C:\Backups\Adventureworks.bak' 
WITH FILE = 2, 
REPLACE;

That's the first time we've stacked the WITH clauses (WITH FILE=2 and WITH REPLACE is represented as WITH FILE=2, REPLACE), but it won't be the last. Read through the books online. Most of the WITH clause statements can be used in combination with the others.

What happens if we want to restore to a different database than the original? For example, we want to make a copy of our database from a separate backup. Maybe we want to move it down to a production support server where we are going to do some work on it, separate from the production copy of the database. If we take the simple approach, well, try this:

RESTORE DATABASE AdventureWorks_2 
FROM DISK = 'C:\Backups\Adventureworks.bak' 
WITH FILE = 2;

In this case, you should see a whole series of errors relating to files not being overwritten. You really can create new databases from backups, but if you're doing it on a server with the existing database, you'll need to change the location of the physical files using the logical names. In order to know the logical names of the files for a given database, run this prior to attempting to move the files:

RESTORE FILELISTONLY 
FROM DISK = 'C:\Backups\Adventureworks.bak' 
WITH FILE = 2;

This can then be used to identify the appropriate logical names in order to generate this script:

RESTORE DATABASE AdventureWorks_2
FROM DISK = 'C:\Backups\Adventureworks.bak'
WITH FILE = 2,
MOVE 'AdventureWorks_Data' TO 'C:\backups\aw2_data.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\backups\aw2_log.ldf';

Restoring a differential backup

The last method is to apply the differential backup. This requires two steps. First, we'll restore the database, but with a twist and then we'll apply the differential backup:

RESTORE DATABASE AdventureWorks 
FROM DISK = 'C:\Backups\Adventureworks.bak' 
WITH FILE = 1, 
   NORECOVERY, 
   REPLACE;  

RESTORE DATABASE AdventureWorks 
FROM DISK = 'C:\Backups\AdventureWorks.bak' 
WITH FILE = 3;

Most of this is probably self-explanatory based on what we've already covered. The one wrinkle is the inclusion of the NORECOVERY keyword. Very simply, during a restore, transactions may have started during the backup process. Some of them complete and some don't. At the end of a restore, completed transactions are rolled forward into the database and incomplete transactions are rolled back. Setting NORECOVERY keeps transactions open. This allows for the next set of transactions to be picked up from the next backup in order.

We're mainly dealing with simple backups and restores in this article, but a more advanced restore in 2005 allows secondary file groups to be restored while the database is online. Its primary file group must be online during the operation. This will be more helpful for very large database systems.

Restoring SQL Server databases to a point in time

Restoring logs is not much more difficult than the differential database restore that we just completed. There's just quite a bit more involved in restoring to a moment in time. Assuming you're backing up your logs to a single file or device:

RESTORE HEADERONLY 
FROM DISK = 'C:\Backups\Adventureworks_log.bak';

Otherwise, you simply go and get the file names you need. First run the database restore, taking care to leave it in a non-recovered state. Follow this up with a series of log restores to a point in time.

RESTORE DATABASE AdventureWorks 
FROM DISK = 'C:\Backups\Adventureworks.bak' 
WITH FILE = 1, 
   NORECOVERY, 
   REPLACE, 
   STOPAT = 'Oct 23, 2006 14:30:29.000'; 

RESTORE LOG AdventureWorks 
FROM DISK = 'C:\Backups\Adventureworks_log.bak' 
WITH FILE = 1, 
   NORECOVERY, 
   STOPAT = 'Oct 23, 2006 14:30:29.000'; 
RESTORE LOG AdventureWorks 
FROM DISK = 'C:\Backups\Adventureworks_log.bak' 
WITH FILE = 2, 
   NORECOVERY, 
   STOPAT = 'Oct 23, 2006 14:30:29.000'; 
RESTORE LOG AdventureWorks 
FROM DISK = 'C:\Backups\Adventureworks_log.bak' 
WITH FILE = 3, 
   NORECOVERY, 
   STOPAT = 'Oct 23, 2006 14:30:29.000'; 
RESTORE LOG AdventureWorks 
FROM DISK = 'C:\Backups\Adventureworks_log.bak' 
WITH FILE = 4, 
   STOPAT = 'Oct 23, 2006 14:30:29.000';

Now what we have is a database that is up to the exact, last committed transaction at 14:30:29 on the 23^rd of October. Remember, during multi-step restores such as this, you have to leave the database in a recovering status. That means appending NORECOVERY to each statement until you've completed the restore process. If for some reason you've added NORECOVERY to all your statements, or you simply stop in the middle, and would like to bring the database back online, you can use this statement to complete the process:

RESTORE DATABASE Adventureworks
WITH RECOVERY;

Database snapshots

SQL Server 2005 introduced the concept of a snapshot, or a read-only, static view of a database. Snapshots are primarily created in order to supply a read-only version of a database for reporting purposes. However, they do function in a similar way to backups. The one primary difference is that all uncommitted transactions are rolled back. There is no option for rolling forward, capturing logs, etc., that backups provide, nor are very many SQL Server resources used at all. Rather, disk technology is used to create a copy of the data. Because of this they are much faster than backups both to create and restore.

NOTE:
For more details on SQL 2005 Snapshot, please refer to http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/.

A good use of snapshots, in addition to reporting, might be to create one prior to maintenance after you've already removed all the active users (and their transactions) from the system. While snapshots don't support the volatility of live backups, their speed and ease of recovery make a great tool for quick recovery from a botched rollout. Snapshots are stored on the server, so you must make sure you've got adequate storage.

The syntax is different because you're not backing up a database; you're creating a new one:

CREATE DATABASE Adventureworks_ss1430 
ON (NAME = AdventureWorks_Data,
    FILENAME = 'C:\Backups\AdventureWorks_data_1430.ss') 
AS SNAPSHOT OF AdventureWorks;

Now it will be accessible for read-only access. Since we're primarily concerned with using this as a backup mechanism, let's include the method for reverting a database to a database snapshot.

First, identify the snapshot you wish to use. If there is more than one on any database that you're going to revert, you'll need to delete all except the one you are using:

DROP DATABASE Adventureworks_ss1440;

Then you can revert the database by running a RESTORE statement (mixed metaphors, not good):

RESTORE DATABASE Adventureworks
FROM DATABASE_SNAPSHOT = Adventureworks_ss1430;

That's it. On my system, running the database snapshots of Adventureworks took 136 ms. The full backup took 5,670 ms. The restore of the snapshot took 905ms and the database restore took 13,382ms. Incorporating this into a production rollout process could result in significant benefits

Again, it's worth noting that there are some caveats to using the snapshot. You have to have enough disk space for a second copy of the database. You need to be careful dealing with snapshots since most of the syntax is similar to that used by databases themselves. Last, while there are snapshots attached to a database you can not run a restore from a database backup of that database.

Best practices

The manner in which you perform database backups should not be a technical decision. It should be dictated by the business. Small systems with low transaction rates and/or reporting systems that are loaded regularly will only ever need a full database backup. Medium sized systems and large systems become dependent on the type of data managed to determine what types of backup are required.

For a medium sized system, a daily backup with log backups during the day would probably answer most data requirements in a timely manner.

For a large database the best approach is to mix and match the backups to ensure maximum recoverability in minimum time. For example, run a weekly full backup. Twice a day during the week, run a differential backup. Every 10 minutes during the day, run a log backup. This gives you a large number of recovery mechanisms.

For very large databases, you'll need to get into running filegroup and file backups because doing a full backup or even a differential backup of the full database may not be possible. A number of additional functions are available to help out in this area, but I won't be going into them here.

You should take the time to develop some scripts for running your backups and restores. A naming convention so you know what database, from which server, from which date, in what specific backup and format will be very conducive to your sanity. A common location for backups, log, full or incremental, should be defined. Everyone responsible should be trained in both backup and recovery and troubleshooting the same. There are many ways of doing this, but you can find a few suggestions in Pop backs up and Pop Restores.

The real test is to run your backup mechanisms and then run a restore. Then try a different type of restore, and another, and another. Be sure that, not only have you done due diligence in defining how to backup the system, but that you've done the extra step of ensuring that you can recover those backups. If you haven't practiced this and documented the practice and then tested the document, in effect, you're not ready for a disaster.

Your Ad Here

SSIS Design Pattern - Custom Error Handling

2009-09-15T19:55:00.000-07:00

Error logging is crucial to troubleshooting and auditing efforts. If an error occurs in your SSIS package while it is executing production loads, transformations, or transfers; you will want to know as much as possible about it.

To demonstrate, create a new SSIS project named ErrorTest:

I encourage you to develop the practice of renaming packages as you create them. This will make your life simpler once you deploy:

I rename Package.dtsx to ErrorTestPkg.dtsx (don't change the extension):

When you rename a package the following dialog will appear:

Always answer this dialog "Yes".
Drag an Execute SQL Task onto the Control Flow:

Configure it (double-click or right-click and select Edit to open the editor) as shown below. Note any connection will suffice, so long as the SQL Server is running. Also note the SQL will generate an exception (intentionally):

Click OK to close the editor. Right-click the Task and select Execute Task to test:

The task should fail (as planned):

Stop execution and click on the Event Handlers tab:

Check to be sure the Executable dropdown is set to the ErrorTestPkg (the package) and the Event handler dropdown is set to OnError. Click the link to create a new OnError Event Handler for the package. From the Toolbox, drag and drop a Data Flow Task onto the Event Handler:

Double-click the Data Flow Task to edit it. From the toolbox, drag a Script Component transformation onto the Data Flow:

The Script Component can be configured as a Source, Transformation, or Destination. Select Source:

Since a Source has only outputs (a destination has only inputs, and a transformation has both inputs and outputs), you will need to configure the Output. Rename the generic Output 0 to ErrorOutput:

Click on Output Columns and click Add Column:

Rename the Column ErrDescription:

Edit the ErrDesription column's properties - change the datatype to String (DT_STR) and the length to 5000:

Add the ErrorDescription System variable to the ReadOnlyVariables property of the Script Component:

There are other variables you will add to your error handling script component. Remember variable names are case-sensitive, comma-separated, with no spaces in the list.
Click the Design Script button and add the following code snippet in the CreateNewOutputRows subroutine as shown below:

With ErrorOutputBuffer
.AddRow()
.ErrDescription = Me.Variables.ErrorDescription
End With

Some explanation: The name of the ErrorOutputBuffer was determined by the name you gave the Script Component Output earlier. When you changed the Output name to ErrorOutput, the script component's output buffer was changed to ErrorOutputBuffer.

To populate the buffer, you must add rows to it. Think of this as starting a new row in a table. If you're familiar with tables in Microsoft Word documents, you know when you start a new row it is empty until you populate it. The same is true here. You are creating a new, empty row in the output buffer to populate with data.

The final step is to load the row. Again, in your production-ready error event handler you will populate more columns in the output buffer than this one - this is for demonstration purposes only:

Next, drag a flat file destination onto the Data Flow and connect it to the Script Component as shown:

Note: You will likely want to store error data in a database rather than a flat file.

When you configure a flat file destination, you are prompted for which type of file. Delimited files are fairly simple to read - especially with few fields:

Select a location and name for your flat file. I chose C:\ErrorOutput1.txt:

Click Columns to view the columns the file will contain. Note: the columns would be empty if you had not connected a data path from the Script Component prior to configuring the Flat File Destination:

Click OK to proceed. Click the Mappings page to assign mappings between fields in the pipeline and fields in the destination:

Since you built the destination from the metadata contained in the pipeline, auto-mapping should occur when you click Mappings:

Click OK. Your Event Handler should appear as shown:

Start debugging the package by pressing F5 or clicking the Debug Run arow. Your ExecuteSQL task should fail as before:

Click on the Event Handlers tab to determine the run status of your Package OnError Event Handler. Double-click the Data Flow Task and note one row was written to your error file (ErrorOutput1.txt):

Open ErrorOutput1.txt file to view the error stored there:

In this example, we built a package to demonstrate logging package errors to a data destination - albeit a flat file. This technique can be used to catch errors and store them for troubleshooting posterity.

Get the code!

DTS vs SSIS

2009-09-10T20:40:00.000-07:00

In today’s blog post, we will give a basic introduction of SSIS (SQL Server Integration Services) and provide a comparison of some of those features with DTS (Data Transformation Services), it’s predecessor.

SSIS is an ETL tool provided by Microsoft for extracting the data from various sources, transforming the data according to the business requirements and loading the data into the destination. The source and the destination could be anything that you can talk to using a connection. One important thing to understand is that SQL Server Integration Services (SSIS) is a component of Microsoft SQL Server 2005 and is not a separate install. Integration Services includes graphical tools and wizards for building and debugging packages, tasks for performing workflow functions such as FTP operations, for executing SQL statements, or for sending e-mail messages, data sources and destinations for extracting and loading data, transformations for cleaning, aggregating, merging, and copying data.

In SQL Server 2000, DTS packages are created in Enterprise Manager. The DTS packages are created in the DTS designer and you have the option of saving them in SQL Server or saving them to the filesystem (by specifying the location as “Structured Storage File”). In Integration Services, the packages are created using Business Intelligence Development Studio (BIDS). BIDS is nothing but another version of Microsoft Visual Studio. It is a modified version of Visual Studio. When we create Integration Services packages using BIDS, they are saved on the client machine (local machine). We have to explicitly deploy the Integration Services Packages onto the destination server.

The following steps show how you can launch BIDS:

Go to: Start –> All Programs –> Microsoft Sql Server 2005 –> Business Intelligence Development Studio

The basic organization concept in DTS as well as SSIS is the concept of a package. A package is essentially a collection of SSIS objects which includes:

a) Connections to the data sources,
b) Data Flows,
c) Control Flows, and
d) Event Handlers

We will discuss these shortly. Let’s first look at the DTS Package Designer and BIDS to get an idea of the IDE layout.

Package Design Pane:

The following images show the DTS and SSIS design panes respectively.

The striking difference when one sees both the designers is the way the designer is designed.

In DTS, the designer consists of a single pane. And to the right we see two options: the Connections and the Workflow tasks. The Connections are used to make the connections to a wide variety of data sources and destinations like Excel, Flat Files, Sql Server and Access essentially anything that we have an ODBC, OLEDB, Jet driver etc. connectivity to. The Work Flow tasks add the functionality to the package. The thick line or what we call the data transformation task between the source and the destination in the above example transforms the data and loads into the destination. One can write custom code for the transformations or simply use the inbuilt capabilities of the data transformation task.

In SSIS, the designer is split into 4 design panes:
a) Control Flow,
b) Data Flow,
c) Event Handlers, and
d) Package Explorer.

The control flow pane is used to design the workflow inside the package. There is also a great improvement in the number of tasks and additional functionality has been added to some of the existing tasks. The control flow is like a program flow. All the workflow tasks in the DTS come under the Control Flow tasks in SSIS except the Data Transformation Task.

The Data Transformation Task in DTS has been converted into Data Flow task. As the name suggests, the Data Flow task handles the flow of data. Microsoft has introduced a wide variety of data flow tasks in SSIS for building an entire ETL. All the data transformation from the source to the destination takes place in this Data Flow task. Examples of Data Flow Task are Multicast, OLEDB Command, Sort transformation, Pivot transformation, Unpivot transformation etc.

Event Handlers is something new to SSIS. Event handlers respond to various events raised in the package like on error, on warning, on pre execute and on post execute. When an event is raised then the control goes to the Event Handler Pane, and the package does whatever is instructed in the Event Handler Pane. Again Event Handlers Contain the Control Flow Tasks because Control Flow determines the workflow of the package.

Package Explorer is also a new pane in SSIS. It gives the overall view of the package.

The image shown above shows the package explorer. As said earlier, the package explorer gives the over all gist of the entire package like Executables, Precedence Constraints, Connection Managers, Event Handlers and the Variables. Precedence Constraints and Connection Managers are described later in the post.

The Solution Explorer:

The Solution Explorer is also a new concept in SSIS. The solution explorer consists of the SSIS packages, connections and Data Source Views (DSV). The solution contains a series of projects. Each project contains a series of objects used in the projects. In DTS we were allowed to create only independent packages. There was no possibility to store the related packages of the module in a single place. The Solution Explorer allows us to do that. We can have multiple packages relation to one module in a single Solution.

Data Source View (DSV)

This is also new to SSIS. A data source view is a document that describes the schema of an underlying data source. A data source view provides a named, browseable, persisted selection of database objects that can be used to define sources, destinations, and lookup tables for SQL Server 2005 Integration Services (SSIS) tasks, transformations, data sources, and destinations. Example: In some ERPs like SAP or even in some databases, the columns might be named like A0012, A0013, AABE etc. Naming the columns this way is typically done by some designers for obfuscation (especially in packaged applications) but does not help the SSIS developers. As far as the SSIS developer is concerned it is just another column on which data transformations have to be applied. In this case we can create a data source view over this data source and can rename the columns in some meaningful way by not renaming the columns in the data source. In other words we are overlapping the table with another transparent table with change in the column names. So we see the column names of the transparent table and the data of the original table.

Connection Managers:

Connection Manager is also something new in SSIS. To understand this let us consider the following screen shot of DTS.

In this package we see two connections. The first one is to the database on the left and the other one is to the text file on the right. What happens when we move this package from one environment to another for example from development to QA? The connections to the databases and the file locations change. Therefore we have to open up each package and then each connection to configure it to point to the intended environment. Typically each DTS package might contain up to 5-10 connections. And when we are moving a large number of packages from one environment to another it becomes cumbersome. Please do note that this can easily be overcome by using dynamic properties and disconnected edits in DTS and we can either pick up the connection information from a file at run-time or even from a configuration table from a database but one needed to code for it or the configuration wasn’t that straight forward.

Now, let’s see how the connection manager helps in making this process easy in SSIS. Let us understand this with the help of an SSIS example shown in the image below:

In the above package we see two connections and one database connection. In SSIS, we have a centralized place where we can configure the connection to a particular data source or data destination called connection managers. All the connections inside the package make use of these connection managers to make the connection to the data sources and data destinations. As in DTS, SSIS also provides a wide variety of connection managers to make connections to a variety of sources and destinations. So when moving from one environment to another, we should open up the SSIS package and just make the change only in one place, which are the connection managers as opposed to multiple places as in DTS. This saves us time and also is less cumbersome. In a little while we will see how to dynamically alter the connections even without opening up the packages. This is done with Configurations.

Variables:

Variables are a powerful piece of the SSIS architecture; they allow you to dynamically control the package at runtime. There are two types of variables: system and user. System variables are ones that are built into SSIS, whereas user variables are created by the SSIS developer. Variables can also have varying scope, with the default scope being the entire package. They can also be set to be in scope of a container, task, or event handler inside the package. The addition of scope to variables is the main differentiating factor between SSIS variables and DTS global variables.

Configurations:

SSIS provides us with a way of altering the connection information and also the variable values at run time. This is done by using the Configuration Files. The following screen shot shows us how to open up the configuration editor in SSIS.

Right click on the control flow pane and click on the Configurations menu. This will open up the configuration wizard. This is also something which did not exist in the DTS. We will explore more of package configurations and how to create configuration files in the upcoming posts.

Transactions:

All Microsoft SQL Server 2005 Integration Services (SSIS) container types—packages, the For Loop, For each Loop, and Sequence containers, and the task hosts that encapsulate each task—can be configured to use transactions. Integration Services provides three options for configuring transactions: NotSupported, Supported, and Required.

• Required indicates that the container starts a transaction, unless one is already started by its parent container. If a transaction already exists, the container joins the transaction
• Supported indicates that the container does not start a transaction, but joins any transaction started by its parent container. For example, if a package with four Execute SQL tasks starts a transaction and all four tasks use the Supported option, the database updates performed by the Execute SQL tasks are rolled back if any task fails. If the package does not start a transaction, the four Execute SQL tasks are not bound by a transaction, and no database updates except the ones performed by the failed task are rolled back.
• NotSupported indicates that the container does not start a transaction or join an existing transaction. A transaction started by a parent container does not affect child containers that have been configured to not support transactions. For example, if a package is configured to start a transaction and a For Loop container in the package uses the NotSupported option, none of the tasks in the For Loop can roll back if they fail.

In this post, we covered a brief introduction to SSIS, its important features and how SSIS differs from its predecessor: DTS. In future posts, we will discuss each task in detail and will also cover the best practices as well as go into the usage of SSIS in building up business intelligence applications like explaining how to handle slowly changing dimensions etc..

Deploying Reports

2009-09-10T20:35:00.001-07:00

Introduction

Tight integration with the .NET environment is the important feature for the popularity of Microsoft SQL Server 2005. As a result, it is possible to perform the some of the common tasks by using different options. This allows the developers/administrators to do their work by selecting the options that they are comfortable with. For example, you can manage/administer the report sever using the SQL Server Management Studio OR the web-based Report Manager. In this article, will look at the various ways for deploying reports on the report server. [It is assumed that the reports are already created]

How to deploy reports?

It is possible to deploy the reports in 3 ways:

Using the BI Development studio.
Using the SQL Server Management Studio.
Programmatically. [Using the RS.EXE utility]

Using BI Development studio:

Open the Project Properties dialog-box and set the TargetServerURL. It is the URL of the report server, on which the reports will be deployed. [Figure 1]

[Figure 1]
Right-Click on the Project and select Deploy [Figure 2]. This will deploy the reports and Data-sources defined in the project on the specified report server.

[Figure 2]

Using SQL Server Management Studio:

Connect to the Reporting services database.
Right-click on Home and select the desired options. [Figure3]

[Figure 3]

Select New Folder to create a new folder on the server. Select New Data Source option for defining a data-source for the reports. Select Import File for adding/deploying the reports on the server.

Using the RS.EXE utility:

It is a Command-line utility.
Used for performing Deployment and Administrative tasks programmatically.
Executes the RSS file. [Reporting Services Script file]

Command to execute:

rs i MyRSS.rss -s http://localhost/reportserver

Parameters can also be passed. These parameters are the global variables in the RSS script file. Multiple parameters can be passed.
For example:
```
rs i MyRSS.rss -s http://localhost/reportserver -v param1="value1" -v param2="value2" 
```

How to write the RSS file:

VB.NET code file [with rss extension].
Can contain user-defined function and global parameters. The value for the global parameters has to be supplied from the command line.
It should have a Main () procedure.
By default, the namespaces System.IO, System.Xml and System.Web.Services are included.
A reference variable rs for the reporting services web service is generated automatically when the script is executed using the RS.EXE utility. This variable allows access to all the functionality of web service class library [of reporting services].

Sample code:

Extract the MyReport.zip file in the D\MyReports folder.

The sample RSS file creates a data-source, which points to the Northwind database on the (local) sql server instance using Windows authentication. The function used is: CreateDataSource(name, extension, connectionstring)

Example:

CreateDataSource("MyDataSource", "SQL", "Data Source= (local);Initial Catalog=Northwind")

It also publishes a sample report using the CreateReport function.
CreateReport(NameofReport, ParentFolder, Overwrite, ReportDefinition, Properties)

Example:

CreateReport(MyReport, /MyReports, False, definition, Nothing)

If report contains any external images, then it has to be also deployed as a resource. This is not required if the image is embedded in the report. The function used is: CreateResource(ImageName, ParentFolder, Overwrite, Definition, MIME type, Properties)

Example:

CreateResource(ImageName, parentPath, False, definition, "image/jpeg",Nothing)

For executing the rss file, open the command prompt and set the directory to D:\MyReports and run the following command:

rs -i MyRSS.rss -s http://localhost/ReportServer

Output will look like:

After successful execution, MyReports folder will be created on the report server. It can be viewed using the Report Manager.

Conclusion

The reports can be deployed using the above 3 methods. Reporting Services script files can be used to run any of the Report Server Web service operations like creating schedules, subscriptions, security settings, etc. Replicating report server would be easy using the Reporting Services script file.

Report Models in Reporting Services 2005

2009-09-10T20:33:00.000-07:00

A report model, in technical terms, is a metadata description of a data source and the relationships that reside within that data source. What it is in practical terms is so much more. For a user it's their own little sandbox. But creating a Report Model is a multi-step process.

The first process is to analyze the needs of the users so you know what fields they are going to need in thier reports. Providing too little information renders the reporting service useless, but providing too much information could eventually cause performance issues as more users access reports. The second step is to ensure you have the appropriate referential integrity in place. Having a series of reports running on a poorly normalized database or poorly designed Analysis Services cube will cause additional headaches. When the project is first created, go into the properties of the project and set the URL of the Report Server (default setting is http://localhost/reportserver):

After setting the URL of the Report you must specify a data source. Right-click Data Sources and choose New Data Source. Then choose the server you wish to connect to and the authentication method:

The next step is to create a Data Source View. Right-click Data Source Views and choose New Data Source View:

As usual, you must select the data source you wish to use or, if not created yet, do so inside the wizard:

The next screen in the wizard is one of interest, because here you can either choose from the entire list of tables and views, or narrow your selection based on the schema the object belongs to:

As stated before, you can choose either a view or one or more tables, although you should create a view if joining more than two tables is necessary. Once the choice of objects is made, click Next and provide a name for the data source view:

Now you have everything you need to create a report model. Right-click Report Models and choose Add New Report Model:

Choose the data source view you wish to create the report model for and click Next:

Then select the rules you want for metadata generation:

The wizard then gives you an option on whether you want to update data statistics for the data source view or use the existing ones (the default is to use the current ones):

Once all the information is collected for creating the report model, click Run to complete the wizard:

Now you have to deploy the Data Source and Report Model to the Report Server (there is no need to deploy the data source view). Right-click each of the objects and select Deploy. From there you can assign security permissions to ensure that only those who need to access the data are able to access it. Users can also use Report Builder to create the ad-hoc reports and deploy either to their My Reports folder or to another folder on the Report Server.

SQL Server Clustering

2009-09-10T20:23:00.000-07:00

What Are the Types of Clustering?

When you decide you want to cluster SQL Server, you have a choice of configuring what is called Active/Active or an Active/Passive cluster. Each has its own pros and cons. Let’s look at each, in the context of a two-node SQL Server cluster.

An Active/Active SQL Server cluster means that SQL Server is running on both nodes of a two-way cluster. Each copy of SQL Server acts independently, and users see two different SQL Servers. If one of the SQL Servers in the cluster should fail, then the failed instance of SQL Server will failover to the remaining server. This means that then both instances of SQL Server will be running on one physical server, instead of two.

As you can imagine, if two instances have to run on one physical server, performance can be affected, especially if the server’s have not been sized appropriately.

An Active/Passive SQL Server cluster refers to a SQL Server cluster where only one instance of SQL Server is running on one of the physical servers in the cluster, and the other physical server does nothing, other than waiting to takeover should the primary node would fail.

From a performance perspective, this is the better solution. On the other hand, this option makes less productive use of your physical hardware, which means this solution is more expensive.

Personally, I prefer an Active/Passive configuration as it is easier to set up and administer, and overall it will provide better performance. Assuming you have the budget, this is what I recommend.

Two- or Four-Node Clustering?

SQL Server can be clustered using two nodes (using Windows 2000 Advanced Server), or it can be clustered using more than two nodes (using Windows 2000 Datacenter). Since I don’t personally have any experience is three or four node clustering, I won’t be discussing it here. But for the most part, what I say about two-node clustering also applies to three- or four-node clustering.

How Does Clustering Work?

Clustering is a very complex technology, so I will focus here on the big picture. In a two-cluster node, one of the SQL Servers is referred to as the primary node, and the second one is referred to as the secondary node. In an Active/Passive cluster design, SQL Server will run on the primary node, and should the primary node fail, then the secondary node will take over.

When you build a two-node cluster using Windows 2000 Advanced Server and Microsoft Clustering Service, each node must be connected to a shared disk array using either SCSI cables or fibre channel.

Typically, this shared disk array is a stand-alone unit that houses a RAID 5 or RAID 10 disk array. All of the shared data in the cluster must be stored on this disk array, otherwise when a failover occurs, the secondary node in the cluster cannot access it. As I have already mentioned earlier, clustering does not help protect data or the shared disk array that it is stored on. Because of this, it is very important that you select a shared disk array that is very reliable and includes fault tolerance.

Besides both servers being connected to a shared disk array, both nodes of the cluster are also connected to each other via a private network. This private network is used for each node to keep track of the status of the other node. For example, if the primary node experiences a hardware failure, the secondary node will detect this and will automatically initiate a failover.

So how do clients who are accessing SQL Server know what to do when a failover occurs in a cluster? This is the cleverest part of Microsoft Cluster Service. Essentially what happens in a SQL Server cluster is that you assign SQL Server its own virtual name and virtual TCP/IP address. This name and address is shared by both of the servers in the cluster.

Typically, a client will connect to the SQL Server cluster using the virtual name used by the cluster. And as far as a client is concerned, there is only one physical SQL Server, not two. Assuming that the primary node of the SQL Server cluster is the node running SQL Server on an Active/Passive cluster design, then the primary node will respond to the client’s requests. But if the primary node fails, and failover to the secondary node occurs, the cluster will still retain the same SQL Server virtual name and TCP/IP address, although now a new physical server will be responding to client’s requests.

During the failover period, which can last several minutes (the exact amount of time depends on the number and sizes of the databases on SQL Server, and how active they are), clients will be unable to access SQL Server, so there is a small amount of downtime when failover occurs.

How the client software reacts to the failover process depends on the software. Some software will just wait the failover out, and when the failover has completed, it will continue just as nothing had happened. Some software will present a message box on the screen, describing a lost connection. Other client software will not know what to do, and users may have to exit, and then reload the client before they can access SQL Server again.

As part of the testing process when implementing a SQL Server cluster, it is important to find out how all of the client software that connects to SQL Server reacts to a failover. This way, you can inform your users of what to expect, so they are better able to deal with it when it does occur.

Once a failover occurs, you will want to find out what caused the failover, and then take the necessary action and correct the problem. Once the problem has been fixed, the next step is to failover SQL Server back to the primary node from the secondary node. You can schedule to do this anytime, preferably when user activity is light on the system.

What are the Pros and Cons of Clustering?

Implementing SQL Server clustering is a big decision, and one fraught with many gochas. Before you undertake such a large and important project, you will want to carefully evaluate the pros and cons of clustering, which include, but are not limited to:

Pros of SQL Server Clustering

Reduces downtime to a bare minimum.
Permits an automatic response to a failed server or software. No human intervention is required.
It allows you to perform upgrades without forcing users off the system for extended periods of time.
It allows you to reduce downtime due to routine server, network, or database maintenance.
Clustering doesn't require any servers to be renamed. So when failover occurs, it is relatively transparent to end-users.
Failing back is quick, and can be done whenever the primary server if fixed and put back on-line.
In some cases, clustering can be used to increase the scalability of an application. For example, if a current cluster is getting too busy, another server could be added to the cluster to expand the resources and help boost the performance of the application.

Cons of Clustering

More expensive than other failover alternatives, such as log shipping or stand-by servers.
Requires more set up time than other alternatives.
Requires more on-going maintenance than other alternatives.
Requires more experienced DBAs and network administrators.

Software Needed for Clustering

The software you need for SQL Server clustering depends on whether you want to cluster two nodes, or more than two nodes.

To cluster two nodes, you will need the following:

Two Microsoft Windows 2000 Advanced Server Licenses
One SQL Server 7.0 Enterprise or SQL Server 2000 Enterprise Licenses for Active/Passive, or two licenses for Active/Active
The latest Windows 2000 and SQL Server Service Packs

To cluster more than two nodes, you will need the following:

Two or More Microsoft Windows 2000 Datacenter Server Licenses
Two or More SQL Server 7.0 Enterprise or SQL Server 2000 Enterprise Licenses
The latest Windows 2000 and SQL Server Service Packs

I want to emphasize that you always want to go with the latest service packs, as many irritating cluster-related bugs have been fixed by them.

Hardware Needed for Clustering

Assuming you are clustering two SQL Servers, you will need at the very minimum the following:

Two servers with a minimum of 256MB RAM and a single Pentium III CPU.
One shared disk array that supports RAID 5 or 10, either SCSI or fibre channel.
Each server must have at least one local SCSI hard disk on its own SCSI controller.
Each server must have a SCSI or fiber channel adapter to talk to the shared disk array. The shared disk array cannot use the SCSI controller used by the local hard disk or CD-ROM.
Each server must have two PCI network cards (one for the private connection and one for the public connection).

How you size the physical servers (CPUs, RAM, amount of shared disk array) is very similar to how you would size a non-clustered server if you plan to use an Active/Passive configuration. But, if you intend to use an Active/Active configuration, then ideally each physical server needs to be sized to run all instances of SQL Server that run on both the primary and secondary nodes, should failover occur and both instances of SQL Server have to run on the same physical server.

Ideally, both physical servers should be identical in hardware, drivers, software, and configuration. There are some exceptions to this allowed, but I would not recommend making them. The closer each physical server is to each other, the less problems you will have.

Another very important consideration when selecting clustering hardware is that is must be on Microsoft’s Hardware Compatibility List (HCL) as a supported system. What do I mean by a supported system? Before Microsoft will support your cluster, all of the cluster hardware you select (servers, cards, shared array, etc.) must have been tested as an entire system and approved by Microsoft. If the system you purchase isn’t an approved system, then Microsoft will not help you if you call them, even if you pay them for support.

Even if all of the individual components of your cluster system have been approved individually by Microsoft for clustering, if they have not all been tested as a single system, then you will still not receive any support. That’s why you need to check out the HCL carefully before you order your equipment to ensure that you purchase an approved clustering system.

Unfortunately, Microsoft’s HCL website is often outdated. What this means is that as new equipment comes out (and we always seem to want to use the latest and the greatest), it may not be added to their approved cluster systems for months. You can of course take the risk of selecting equipment that is not part of an approved Microsoft system (hoping that it will eventually be tested and certified by Microsoft), but should you have a problem and call Microsoft, you risk not getting any help from them.

Setting Up and Managing a SQL Server Cluster

This topic could fill a book, but unfortunately, there is not enough room here for that. So what I will do here is just cover the basics so you have some feel for what you face.

Based on my experience with SQL Server clustering, you should keep the following in mind:

Buy an approved cluster system and have it delivered to you 4-8 weeks before you plan to implement it. This will give you lots of time to learn about it and work out any problems. I guarantee you, you will have problems.
Consider attending training on clustering, or at least bringing in a consultant in early in the game to help you plan your implementation. If you have never done this before, you will need all the help you can get. Unfortunately, there is not a lot of good information available on clustering, so you will have to fend for yourself more than you usually do.
Once your cluster is up and running, test it thoroughly as possible using the same databases and clients you intend to use on it when you start production.
Thoroughly document everything as you build your cluster.
Develop a plan to regularly monitor the cluster.
Develop a plan to regularly test the cluster to ensure that it will failover as expected.
Develop a formal backup and recovery plan, and test it.
Create a disaster recovery plan for what you intend to do should you loose all the nodes in your cluster.

Implementing and managing a cluster is complex. You will want to assign a team of top network and database administrators to implement and manage it.

Is Clustering for You?

Hopefully, this article has provided you with some additional information on SQL Server clustering that you didn’t have before. And I hope I haven't scared you away from SQL Server clustering.

While SQL Server clustering is not an easy challenge, it is often a worthwhile one. After installing two SQL Server clusters myself, and currently working on a third, I feel that SQL Server clustering is very valuable for many organizations, and in fact it has reduced my stress own level somewhat. Now I don’t have to worry (as much) about extended periods of down time. Now that the clusters are up, they haven’t presented any problems and are purring away.

SQL Server Replication 2005

2009-08-14T14:45:00.000-07:00

Introduction

Replication is intended to be a way of distributing data automatically from a source database to one or more recipient databases. As such, it can have obvious uses in a distributed system. It has also been used to implement high-availability systems. It is not useful for one-off synchronization, or for simply copying data. It is intended as a long-term data relationship between databases. Typical uses are in:

Data warehousing and reporting
Integrating data from several, possibly only partially connected, sites
Improving scalability and availability
Integrating heterogeneous data from other databases via OLE DB, integrating data from mobile users, getting data to and from Point-Of-Sale systems
Offloading/delegating batch processing tasks.

Examples of the use of replication within an application could be

Distributing data 'owned' by a particular application to other applications that are 'consumers' of that data. (For example, sales records to reporting services, manufacturing stock levels to purchasing systems.)
Creating several instances of a database to distribute load on it
Updating a central database with information from a number of remote Laptops that might be only partially connected, and to resynchronise the laptops.

There are three methods of replication: Snapshot, Transactional, and Merge. These are provided to try to meet the wide range of business requirements for replication.

Replication uses a 'Magazine Publishing' vocabulary. Anything from an 'Article' to an entire database can be replicated. An Article is the smallest component of distribution, and can be a table, procedure or function. If it is a table, then a filter can be applied to it so that only certain rows or columns are replicated. This 'Magazine Publishing' analogy can be confusing because, in replication, a Subscriber can sometimes make updates, and a Publisher usually sends out incremental changes to the articles in a publication.

A 'Publisher' maintains the original copy of the data. It holds the definition of the 'Publication', which defines the 'articles' that are to be 'published'. (The database with the original location of the data determines what is to be distributed).

A 'Subscriber' receives the articles from a publisher. It can subscribe to one or more publications. Any database can take on either role or even both roles at once.

A Distributor is a specialist database that runs the 'Replication agents'.

Replication is not part of the SQL Server engine, but an external application. This makes it much easier to involve other database systems in replication. Any SQL Server database, or other database system with an OLE DB provider, can be a publisher or subscriber in snapshot or transactional replication.

It is essential to plan out the replication in detail as a first stage, and to be very certain of the type of replication you wish to implement. A common mistake is to use replication in cases where a much less complex solution is possible.

A problem with production systems using replication is the difficulty of restoring the topology after a disaster.. This requires a fully documented recovery strategy, which has to be periodically tested and practiced. This means that the whole replication topology and configuration must be scripted so it can be re-created in an emergency, even if the system was originally built using the GUI tools. The Object Browser (or Enterprise Manager) makes the initial deployment relatively simple to do, and there are plenty of step-by-step guides, but it is not the best option when trying to restore an existing topology, and settings, in order to achieve a recovery from major failures.

Problems often follow from developers adding or dropping articles, changing publication properties, and changing schema on published databases. It is therefore best to create the replication once the design of the publisher is relatively stable.

Replication topologies

In most topologies, it makes sense for publishers, distributors, and subscribers to be on separate physical hardware.

Central Publisher

The commonest form of replication is to have a single publisher with the source data, with one or more subscribers. The Distributor database can be on the same, or preferably different, server.

Central Subscriber

Often, where the data from several databases need to be 'warehoused' centrally in an OLAP or reporting database, one will find a single 'reporting' database subscribing to several publications.

One can come across other topologies such as 'bi-directional' and 'peer to peer' which are really special cases of Central Publisher or Central Subscriber and use transactional replication

Publishing Subscriber

The distribution of data can be relayed to other subscribers via a publishing subscriber. This allows replication to be implemented over low-bandwidth WANs to a subscriber that, in turn, distributes it to other servers within its high-bandwidth LAN

How Replication Works

Replication begins with the initial synchronization of the published objects between the Publisher and Subscribers, using a snapshot. A snapshot is a copy of all of the objects and data specified by a publication. After the snapshot is created on the publisher, it is delivered to the Subscribers via the distributor.

For Snapshot replication, this is sufficient. For other types of replication, all subsequent data changes to the publication flow to the Subscriber as they happen, in a queue, or on request.

Snapshot Replication

The snapshot replication process provides the initial synchronization for transactional and merge publications. However, in several cases, this initial synchronization is all that is necessary. This would include circumstances where data hardly changes, or if the latest version of the data is not essential to the subscriber, where the amount of data is small, or if a large number of changes takes place rapidly.

Snapshot replication involves copying the articles that make up the publication. Normally, if they exist already on the subscriber, they are over-written, though this behavior can be changed. Snapshot replication is more expensive in terms of overhead and network traffic and only takes place at intervals. Because locks are held during snapshot replication, this can impact other users of the subscriber database. It is therefore more suitable for static data and enumerations. In SQL Server 2005, several articles can be processed in parallel, and interrupted snapshots can be recommenced from the point of interruption. Snapshots can be queued or immediate.

Data changes are not tracked for snapshot replication; each time a snapshot is applied, it completely overwrites the existing data.

Transactional Replication

Transactional replication is used if:

Changes to the data must be propagated immediately
The database application taking out a subscription needs to react to every change
The Publisher has a very high volume of insert, update, and delete activity
The Publisher or Subscriber is a different database application reached via OLE DB.

Essentially, Transaction replication distributes data in one direction, but transactional replication does offer options that allow updates at the Subscriber. Once a snapshot replication has synchronized the subscribers with the publisher, all committed transactions on the publisher are then propagated to the subscribers in sequence, via distributed transactions. One can select a queued update or immediate, depending on requirements.

Peer-to-peer Replication

This is a special type of transactional replication in which every participant is both a publisher and subscriber (2005 Enterprise only) and is most useful for up to ten databases in a load-balancing or high-availability group.

Bidirectional Replication

This is where two databases replicate the same articles to each other via a distributor. There must be loopback detection. Data conflicts aren't handled and the replication must be implemented in code, since the GUI doesn't support it.

Transactional replication tracks changes through the SQL Server transaction log

Merge Replication

Merge replication allows various sites to work autonomously and later merge updates into a single, uniform result.

Merge Replication is complex, but provides the means to implement part of a high-availability system, as well as its original purpose of serving mobile and disconnected users. It is designed for cases where the publishers are not in constant communication with the subscribers. After the initial snapshot synchronization, subsequent changes are tracked locally with triggers, and the databases are merged when in contact, using a series of rules to resolve all possible conflicts.

Merge replication is used when several Subscribers might need to update the same data at various times and propagate those changes back to the Publisher and thence to other Subscribers. It is also required in applications that involve Subscribers receiving data, making changes offline, and finally reconnecting with the publisher to synchronize changes with the Publisher and other Subscribers.

To make this possible, each Subscriber requires a different partition of data and there has to be a set of rules to determine how every conflict that takes place in the update of the data is detected and resolved. These conflicts occur when the data is merged because there can be no 'locking' and so the same data may have been updated by the Publisher and by more than one Subscriber.

Merge Replication does not use transactions. Merge replication uses a set of conflict-resolution rules to deal with all the problems that occur when two databases alter the same data in different ways, before updating the subscribers with a 'consensus' version. It normally works on a row-by-row basis but can group rows of related information into a logical record. One can specify the order in which 'articles' are processed during synchronisation.

Merge replication tracks changes through triggers and metadata tables.

Replication Agents

Replication is done by several different agents, which are separate applications each responsible for part of the process. The replication agents should not be run under the SQL Server Agent account in a production system. Instead, they need the minimal permissions necessary to perform their function.

SQL Server Agent

This manages the overall replication process via SQL Server Agent jobs.

The Snapshot agent

Snapshot.exe executes on the Distributor. It extracts the schema and data defined by the publication, which is then sent to the subscriber via a 'snapshot folder'. It also updates status information on the distribution database. . It is used in all forms of replication

The Log Reader Agent

LogRead.exe is used in transactional replication to extract relevant committed transactions from the publisher's log, repackage them and send them to the distributor in the correct sequence.

Distribution Agent

Distrib.exe takes the snapshots, and log entries from the agents we've described, and dispatches them to the subscribers.

Merge Agent

ReplMer.exe is used only in Merge Replication to send a snapshot when the subscriber is initialized, and also exchanges transactions between publisher and subscriber

Queue Reader Agent

QrDrSvc.exe is used to queue the updates in transactional or snapshot replication when queuing has been specified.

Monitoring Replication

Many problems associated with replication can be avoided by .regular checks. The most obvious check is to make sure that the data has been transferred as expected. Periodic checks with SQL Compare and SQL Data Compare can be very useful in addition to the tools that come with Replication. Additionally the replication processes and jobs need to be checked to make sure they are working.

Checking throughput

The performance of replication must be regularly monitored, and performance-tuned as necessary.

The Replication Monitor is used to check on the operational state of publications, and inspect the history, and errors. Right-clicking the replication node in Object Explorer will gain access to it.

One of the most important concerns is the time delay, or latency, of transactions from the publications appearing in the subscriber database. At times of high transaction throughput on the publisher database, bottlenecks can occur. Whereas the stored procedure sp_browseReplCmds on the distribution database can tell you how far behind the synchronisation is at any particular time, one cannot determine where the problems lies just from the data. Tracer tokens are now used to measure the actual throughput of the replication architecture at any particular time to help diagnose such bottlenecks.

Validating

There is always an element of doubt as to whether the replication has entirely worked. There are stored procedures provided to compare the 'articles' on the publisher and subscribers to make sure they are the same.

The sp_publication_validation stored procedure validates the data associated with each article by calling sp_article_validation (after the articles associated with a publication have been activated). The sp_article_validation stored procedure invokes sp_table_validation stored procedure, which calculates the number of lines and, optionally, the checksum of the published table. It is considered good practice to perform a daily row-count and weekly checksum. SQL Data Compare is ideal for mending a broken replication.

The Distribution Agent raises the '20574' system message if validation fails, or the '20575' system message if it passes. The Distribution Agent will replicate changes to a subscriber even if the validation shows that the subscriber is out of synchronization. It is a good policy to configure the Replication Alert on the '20574' message so as to send E-Mail, Pager, or Network notification.

This validation approach will only work within certain restrictions. For example, it will not work if certain filters have been applied. They should be used with caution.

Changing the settings

It is best to use the default replication settings unless there are clear performance gains to be made, or if the application design forces the issue. However, one cannot assume that the changes will be generally beneficial to the entire topology without comprehensive testing.

Articles

Articles are the smallest unit of a publication. An article can be a table, view, stored Procedure or function. Where an article is based on a table or view, it can contain all the data or just part of it. These filters of two types.: More common are the static 'WHERE' clauses, but filters can be used dynamically in Merge Replication to publish different 'content' (rows) to different 'subscribers' (databases receiving data). These latter Filters are called 'Dynamic' and can be simple Row Filters, or Join Filters, where the selection of rows to publish is based on a join with other tables, rather than a simple WHERE clause.

Normally, any alteration to an article that is a table is propagated to all the subscribers. You can also opt to propagate schema objects associated with the article such as indexes, constraints, triggers, collation and extended properties.

Updating articles

In Merge replication, the subscriber can update the article. This is, of course, a recipe for conflict, and these have to be resolved automatically. When the Merge Agent comes across a row that might have changed recently, it examines the history or 'lineage' of each site's version of the row to see if there is a conflict. If so, then the update that is finally used. Has to be based on either

A "first wins" resolution,
a user-specified priority scheme to determine the update to select,
a customised resolution, using COM and stored procedures.

The 'lineage' is a history of changes in a table row in MSmerge_contents, which is maintained automatically when a user updates a row. Each column contains one entry for each site that has updated the row.

Conflicts to the data in the base table can occur within a column or a row. Most usual are column-tracked articles this means that, within any row, updates are only recognized as conflicts if the same column is updated by more than one subscriber. Occasionally, however, the business rules of the application may treat simultaneous changes to the any column within the row as a conflict, in which case row-level tracking is used.

Programming Replication Topologies

Replication agents and replication topologies can be administered and monitored remotely via SQL Scripts or RMO scripts. The task of building and maintaining replication topologies is made much easier of all parts of the deployments are scripted, even if this is done after the other methods such as wizards or RMO are used for the initial operation.

There are other uses for a replication script. It will be required in end-user application where, for example, such as s a pull subscription is synchronized when the user clicks a button, or where a routine administration task such as monitoring replication throughput is performed from a custom console. It is also generally used for writing customized business rules that are executed when a merge subscription is synchronized.

One can use the Object Explorer in SSMS, or the Enterprise Manager in earlier versions of SQL Server, to set up replication. BOL provide worked examples. Alternatively, RMO can be used with VB or C# to script the replication. Whatever system you use, it is a good idea to use the Transact SQL script as the reference for the replication topology you create

Ultimately, the functionality in a replication topology is provided by system stored procedures. The easiest approach is to use Transact-SQL script files to perform a logical sequence of replication tasks, because it provides a permanent, repeatable, copy of the steps used to deploy the replication topology that can, for example, be used to configure more than one subscriber. It also provides a measure of documentation and disaster-recovery. A script can be stored as a query object in a SQL Server Management Studio project.

Replication scripts can be created by hand, by the script generation of the replication wizards in SQL Server Management Studio, or by using Replication Management Objects (RMOs) to programmatically generate the script to create an RMO object.

When creating scripts to configure replication, it is best to use Windows Authentication so as to avoid storing security credentials in the script file. Otherwise you must secure the script file

Database programming guidelines, conventions

2009-06-28T19:47:00.000-07:00

Databases are the heart and soul of many of the recent enterprise applications and it is very essential to pay special attention to database programming. I've seen in many occasions where database programming is overlooked, thinking that it's something easy and can be done by anyone. This is wrong. For a better performing database you need a real DBA and a specialist database programmer, let it be Microsoft SQL Server, Oracle, Sybase, DB2 or whatever! If you don't use database specialists during your development cycle, database often ends up becoming the performance bottleneck. I decided to write this article, to put together some of the database programming best practices, so that my fellow DBAs and database developers can benefit!

Here are some of the programming guidelines, best practices, keeping quality, performance and maintainability in mind. This list many not be complete at this moment, and will be constantly updated.

Decide upon a database naming convention, standardize it across your organization and be consistent in following it. It helps make your code more readable and understandable. Click here to see the database object naming convention that I follow.
Do not depend on undocumented functionality. The reasons being:

- You will not get support from Microsoft, when something goes wrong with your undocumented code
- Undocumented functionality is not guaranteed to exist (or behave the same) in a future release or service pack, there by breaking your code

Try not to use system tables directly. System table structures may change in a future release. Wherever possible, use the sp_help* stored procedures or INFORMATION_SCHEMA views. There will be situattions where you cannot avoid accessing system table though!
Make sure you normalize your data at least till 3rd normal form. At the same time, do not compromize on query performance. A little bit of denormalization helps queries perform faster.
Write comments in your stored procedures, triggers and SQL batches generously, whenever something is not very obvious. This helps other programmers understand your code clearly. Don't worry about the length of the comments, as it won't impact the performance, unlike interpreted languages like ASP 2.0.
Do not use SELECT * in your queries. Always write the required column names after the SELECT statement, like SELECT CustomerID, CustomerFirstName, City. This technique results in less disk IO and less network traffic and hence better performance.
Try to avoid server side cursors as much as possible. Always stick to 'set based approach' instead of a 'procedural approach' for accessing/manipulating data. Cursors can be easily avoided by SELECT statements in many cases. If a cursor is unavoidable, use a simpleWHILE loop instead, to loop through the table. I personally tested and concluded that a WHILE loop is faster than a cursor most of the times. But for a WHILE loop to replace a cursor you need a column (primary key or unique key) to identify each row uniquely and I personally believe every table must have a primary or unique key. Click here to see one of the many examples of using WHILE loop.
Avoid the creation of temporary tables while processing data, as much as possible, as creating a temporary table means more disk IO. Consider advanced SQL or views or table variables of SQL Server 2000 or derived tables, instead of temporary tables. Keep in mind that, in some cases, using a temporary table performs better than a highly complicated query.
Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword, as that results in an index scan, which is defeating the purpose of having an index. The following statement results in an index scan, while the second statement results in an index seek:

1. SELECT LocationID FROM Locations WHERE Specialities LIKE '%pples'
2. SELECT LocationID FROM Locations WHERE Specialities LIKE 'A%s'

Also avoid searching with not equals operators (<> and NOT) as they result in table and index scans. If you must do heavy text-based searches, consider using the Full-Text search feature of SQL Server for better performance.

Use 'Derived tables' wherever possible, as they perform better. Consider the following query to find the second highest salary from Employees table:

SELECT MIN(Salary)
FROM Employees
WHERE EmpID IN
(
SELECT TOP 2 EmpID
FROM Employees
ORDER BY Salary Desc
)

The same query can be re-written using a derived table as shown below, and it performs twice as fast as the above query:

SELECT MIN(Salary)
FROM
(
SELECT TOP 2 Salary
FROM Employees
ORDER BY Salary Desc
) AS A

This is just an example, the results might differ in different scenarios depending upon the database design, indexes, volume of data etc. So, test all the possible ways a query could be written and go with the efficient one. With some practice and understanding of 'how SQL Server optimizer works', you will be able to come up with the best possible queries without this trial and error method.

While designing your database, design it keeping 'performance' in mind. You can't really tune performance later, when your database is in production, as it involves rebuilding tables/indexes, re-writing queries. Use the graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze your queries. Make sure your queries do 'Index seeks' instead of 'Index scans' or 'Table scans'. A table scan or an index scan is a very bad thing and should be avoided where possible (sometimes when the table is too small or when the whole table needs to be processed, the optimizer will choose a table or index scan).
Prefix the table names with owner names, as this improves readability, avoids any unnecessary confusions. Microsoft SQL Server Books Online even states that qualifying tables names, with owner names helps in execution plan reuse.
Use SET NOCOUNT ON at the beginning of your SQL batches, stored procedures and triggers in production environments, as this suppresses messages like '(1 row(s) affected)' after executing INSERT, UPDATE, DELETE and SELECT statements. This inturn improves the performance of the stored procedures by reducing the network traffic.
Use the more readable ANSI-Standard Join clauses instead of the old style joins. With ANSI joins the WHERE clause is used only for filtering data. Where as with older style joins, the WHERE clause handles both the join condition and filtering data. The first of the following two queries shows an old style join, while the second one shows the new ANSI join syntax:

SELECT a.au_id, t.title
FROM titles t, authors a, titleauthor ta
WHERE
a.au_id = ta.au_id AND
ta.title_id = t.title_id AND
t.title LIKE '%Computer%'

SELECT a.au_id, t.title
FROM authors a
INNER JOIN
titleauthor ta
ON
a.au_id = ta.au_id
INNER JOIN
titles t
ON
ta.title_id = t.title_id
WHERE t.title LIKE '%Computer%'

Be aware that the old style *= and =* left and right outer join syntax may not be supported in a future release of SQL Server, so you are better off adopting the ANSI standard outer join syntax.

Do not prefix your stored procedure names with 'sp_'. The prefix sp_ is reserved for system stored procedure that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with sp_,, it first tries to locate the procedure in the master database, then looks for any qualifiers (database, owner) provided, then using dbo as the owner. So, you can really save time in locating the stored procedure by avoiding sp_ prefix. But there is an exception! While creating general purpose stored procedures that are called from all your databases, go ahead and prefix those stored procedure names with sp_ and create them in the master database.
Views are generally used to show specific data to specific users based on their interest. Views are also used to restrict access to the base tables by granting permission on only views. Yet another significant use of views is that, they simplify your queries. Incorporate your frequently required complicated joins and calculations into a view, so that you don't have to repeat those joins/calculations in all your queries, instead just select from the view.
Use 'User Defined Datatypes', if a particular column repeats in a lot of your tables, so that the datatype of that column is consistent across all your tables.
Do not let your front-end applications query/manipulate the data directly using SELECT or INSERT/UPDATE/DELETE statements. Instead, create stored procedures, and let your applications access these stored procedures. This keeps the data access clean and consistent across all the modules of your application, at the same time centralizing the business logic within the database.
Try not to use text, ntext datatypes for storing large textual data. 'text' datatype has some inherent problems associated with it. You can not directly write, update text data using INSERT, UPDATE statements (You have to use special statements like READTEXT, WRITETEXT and UPDATETEXT). There are a lot of bugs associated with replicating tables containing text columns. So, if you don't have to store more than 8 KB of text, use char(8000) or varchar(8000)datatypes.
If you have a choice, do not store binary files, image files (Binary large objects or BLOBs) etc. inside the database. Instead store the path to the binary/image file in the database and use that as a pointer to the actual binary file. Retrieving, manipulating these large binary files is better performed outside the database and after all, database is not meant for storing files.
Use char data type for a column, only when the column is non-nullable. If a char column is nullable, it is treated as a fixed length column in SQL Server 7.0+. So, a char(100), when NULL, will eat up 100 bytes, resulting in space wastage. So, use varchar(100) in this situation. Of course, variable length columns do have a very little processing overhead over fixed length columns. Carefully choose between char and varchar depending up on the length of the data you are going to store.
Avoid dynamic SQL statements as much as possible. Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan every time at runtime. IF and CASE statements come in handy to avoid dynamic SQL. Another major disadvantage of using dynamic SQL is that, it requires the users to have direct access permissions on all accessed objects like tables and views. Generally, users are given access to the stored procedures which reference the tables, but not directly on the tables. In this case, dynamic SQL will not work. Consider the following scenario, where a user named 'dSQLuser' is added to the pubs database, and is granted access to a procedure named 'dSQLproc', but not on any other tables in the pubs database. The procedure dSQLproc executes a direct SELECT on titles table and that works. The second statement runs the same SELECT on titles table, using dynamic SQL and it fails with the following error:

Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'titles', database 'pubs', owner 'dbo'.

To reproduce the above problem, use the following commands:

sp_addlogin 'dSQLuser'
GO
sp_defaultdb 'dSQLuser', 'pubs'
USE pubs
GO
sp_adduser 'dSQLUser', 'dSQLUser'
GO
CREATE PROC dSQLProc
AS
BEGIN
SELECT * FROM titles WHERE title_id = 'BU1032' --This works
DECLARE @str CHAR(100)
SET @str = 'SELECT * FROM titles WHERE title_id = ''BU1032'''
EXEC (@str) --This fails
END
GO
GRANT EXEC ON dSQLProc TO dSQLuser
GO

Now login to the pubs database using the login dSQLuser and execute the procedure dSQLproc to see the problem.

Consider the following drawbacks before using IDENTITY property for generating primary keys. IDENTITY is very much SQL Server specific, and you will have problems if you want to support different database backends for your application.IDENTITY columns have other inherent problems. IDENTITY columns run out of numbers one day or the other. Numbers can't be reused automatically, after deleting rows. Replication and IDENTITY columns don't always get along well. So, come up with an algorithm to generate a primary key, in the front-end or from within the inserting stored procedure. There could beissues with generating your own primary keys too, like concurrency while generating the key, running out of values. So, consider both the options and go with the one that suits you well.
Minimize the usage of NULLs, as they often confuse the front-end applications, unless the applications are coded intelligently to eliminate NULLs or convert the NULLs into some other form. Any expression that deals with NULL results in a NULL output. ISNULL and COALESCE functions are helpful in dealing with NULL values. Here's an example that explains the problem:

Consider the following table, Customers which stores the names of the customers and the middle name can be NULL.

CREATE TABLE Customers
(
FirstName varchar(20),
MiddleName varchar(20),
LastName varchar(20)
)

Now insert a customer into the table whose name is Tony Blair, without a middle name:

INSERT INTO Customers
(FirstName, MiddleName, LastName)
VALUES ('Tony',NULL,'Blair')

The following SELECT statement returns NULL, instead of the customer name:

SELECT FirstName + ' ' + MiddleName + ' ' + LastName FROM Customers

To avoid this problem, use ISNULL as shown below:

SELECT FirstName + ' ' + ISNULL(MiddleName + ' ','') + LastName FROM Customers

Use Unicode datatypes like nchar, nvarchar, ntext, if your database is going to store not just plain English characters, but a variety of characters used all over the world. Use these datatypes, only when they are absolutely needed as they need twice as much space as non-unicode datatypes.
Always use a column list in your INSERT statements. This helps in avoiding problems when the table structure changes (like adding a column). Here's an example which shows the problem.

Consider the following table:

CREATE TABLE EuropeanCountries
(
CountryID int PRIMARY KEY,
CountryName varchar(25)
)

Here's an INSERT statement without a column list , that works perfectly:

INSERT INTO EuropeanCountries
VALUES (1, 'Ireland')

Now, let's add a new column to this table:

ALTER TABLE EuropeanCountries
ADD EuroSupport bit

Now run the above INSERT statement. You get the following error from SQL Server:

Server: Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

This problem can be avoided by writing an INSERT statement with a column list as shown below:

INSERT INTO EuropeanCountries
(CountryID, CountryName)
VALUES (1, 'England')

Perform all your referential integrity checks, data validations using constraints (foreign key and check constraints). These constraints are faster than triggers. So, use triggers only for auditing, custom tasks and validations that can not be performed using these constraints. These constraints save you time as well, as you don't have to write code for these validations and the RDBMS will do all the work for you. * Always access tables in the same order in all your stored procedures/triggers consistently. This helps in avoiding deadlocks. Other things to keep in mind to avoid deadlocks are: Keep your transactions as short as possible. Touch as less data as possible during a transaction. Never, ever wait for user input in the middle of a transaction. Do not use higher level locking hints or restrictive isolation levels unless they are absolutely needed. Make your front-end applications deadlock-intelligent, that is, these applications should be able to resubmit the transaction incase the previous transaction fails with error 1205. In your applications, process all the results returned by SQL Server immediately, so that the locks on the processed rows are released, hence no blocking.
Offload tasks like string manipulations, concatenations, row numbering, case conversions, type conversions etc. to the front-end applications, if these operations are going to consume more CPU cycles on the database server (It's okay to do simple string manipulations on the database end though). Also try to do basic validations in the front-end itself during data entry. This saves unnecessary network roundtrips.
If back-end portability is your concern, stay away from bit manipulations with T-SQL, as this is very much RDBMS specific. Further, using bitmaps to represent different states of a particular entity conflicts with the normalization rules.
Consider adding a @Debug parameter to your stored procedures. This can be of bit data type. When a 1 is passed for this parameter, print all the intermediate results, variable contents using SELECT or PRINT statements and when 0 is passed do not print debug information. This helps in quick debugging of stored procedures, as you don't have to add and remove these PRINT/SELECT statements before and after troubleshooting problems.
Do not call functions repeatedly within your stored procedures, triggers, functions and batches. For example, you might need the length of a string variable in many places of your procedure, but don't call the LEN function whenever it's needed, instead, call the LEN function once, and store the result in a variable, for later use.
Make sure your stored procedures always return a value indicating the status. Standardize on the return values of stored procedures for success and failures. The RETURN statement is meant for returning the execution status only, but not data. If you need to return data, use OUTPUT parameters.
If your stored procedure always returns a single row resultset, consider returning the resultset using OUTPUT parameters instead of a SELECT statement, as ADO handles output parameters faster than resultsets returned by SELECT statements.
Always check the global variable @@ERROR immediately after executing a data manipulation statement (like INSERT/UPDATE/DELETE), so that you can rollback the transaction in case of an error (@@ERROR will be greater than 0 in case of an error). This is important, because, by default, SQL Server will not rollback all the previous changes within a transaction if a particular statement fails. This behavior can be changed by executing SET XACT_ABORT ON. The @@ROWCOUNT variable also plays an important role in determining how many rows were affected by a previous data manipulation (also, retrieval) statement, and based on that you could choose to commit or rollback a particular transaction.
To make SQL Statements more readable, start each clause on a new line and indent when needed. Following is an example:

SELECT title_id, title
FROM titles
WHERE title LIKE 'Computing%' AND
title LIKE 'Gardening%'

Though we survived the Y2K, always store 4 digit years in dates (especially, when using char or int datatype columns), instead of 2 digit years to avoid any confusion and problems. This is not a problem with datetime columns, as the century is stored even if you specify a 2 digit year. But it's always a good practice to specify 4 digit years even with datetime datatype columns.
In your queries and other SQL statements, always represent date in yyyy/mm/dd format. This format will always be interpreted correctly, no matter what the default date format on the SQL Server is. This also prevents the following error, while working with dates:

Server: Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

As is true with any other programming language, do not use GOTO or use it sparingly. Excessive usage of GOTO can lead to hard-to-read-and-understand code.
Do not forget to enforce unique constraints on your alternate keys.
Always be consistent with the usage of case in your code. On a case insensitive server, your code might work fine, but it will fail on a case sensitive SQL Server if your code is not consistent in case. For example, if you create a table in SQL Server or database that has a case-sensitive or binary sort order, all references to the table must use the same case that was specified in the CREATE TABLE statement. If you name the table as 'MyTable' in the CREATE TABLE statement and use 'mytable' in the SELECT statement, you get an 'object not found' or 'invalid object name' error.
Though T-SQL has no concept of constants (like the ones in C language), variables will serve the same purpose. Using variables instead of constant values within your SQL statements, improves readability and maintainability of your code. Consider the following example:

UPDATE dbo.Orders
SET OrderStatus = 5
WHERE OrdDate < '2001/10/25'

The same update statement can be re-written in a more readable form as shown below:

DECLARE @ORDER_PENDING int
SET @ORDER_PENDING = 5

UPDATE dbo.Orders
SET OrderStatus = @ORDER_PENDING
WHERE OrdDate < '2001/10/25'

Do not use the column numbers in the ORDER BY clause as it impairs the readability of the SQL statement. Further, changing the order of columns in the SELECT list has no impact on the ORDER BY when the columns are referred by names instead of numbers. Consider the following example, in which the second query is more readable than the first one:

SELECT OrderID, OrderDate
FROM Orders
ORDER BY 2

SELECT OrderID, OrderDate
FROM Orders
ORDER BY OrderDate

What are federated database servers?

2009-06-28T19:39:00.000-07:00

"Federated database servers" is a feature introduced in SQL Server 2000. A federation is a group of SQL Servers that cooperate to share the processing load of a system. Federated database servers let you scale out a set of servers to support the processing needs of large systems and websites.

Scaling out is the process of increasing the processing power of a system by adding one or more additional computers, or nodes, instead of beefing up the hardware of a single computer (Scaling up).

Federated database servers can be implemented using "Distributed Partitioned Views" (DPV). You can partition tables horizontally across several servers, and define a distributed partitioned view on one server, covering all member server partitions. This view makes it appear as if a full copy of the original table is stored on one server.

SQL Server 7.0 supports partitioned views too, but SQL Server 2000 came up with the following enhancements, that allow the views to scale out and form federations of database servers:

In SQL Server 2000, partitioned views are updateable
The SQL Server 2000 query optimizer supports new optimizations that minimize the amount of distributed data that has to be transferred. The distributed execution plans generated by SQL Server 2000 result in good performance for a larger set of queries than the plans generated by SQL Server 7.0

Why federated database servers and when to scale out?
When websites, applications generate processing loads that exceed the capacity of large individual servers, scaling out is the best option for increasing the processing capacity of the system. When the server for a particular application is at its maximum potential, and is no longer able to meet user demands, you should consider scaling out.

According to Microsoft, a federation of servers running SQL Server 2000 is capable of supporting the growth requirements of any Web site, or of the largest enterprise systems.

How are we able to gain performance by scaling out?

Distributed processing of data, which means individual member servers of the federation are working with smaller subsets of data, instead of the complete data.
More CPUs working in parallel.
Parallel disk I/O.
Availability of more RAM, as each member server is working with a smaller subset of data.

How to create distributed partitioned views?

Creation of partitioned views is explained in SQL Server Books Online, in the page titled "Creating a Partitioned View". The rules for creating updateable partitioned views are also explained in this page.

What is the impact of partitioned views on my front-end applications?

Instead of accessing the base tables directly, your front-end applications or stored procedures will access the partitioned view. SQL Server will take care of getting the right data from right servers, transparent to your application. Even in OLTP scenarios SQL Server manages the INSERT, UPDATE and DELETE commands transparently and sends them to the right partition.

What other things I should consider before implementing federated database servers?

Availability: Make sure all table partitions spread across different servers are accessible all the time, or else you will not be able to use the partitioned view.
Backup/Restore: If transactional consistency across the partitioned is not a concern, you can backup and restore some or all of your partitions individually. If you must achieve transactional consistency, perform coordinated backup and restore operations. This means that you backup all your partitions simultaneously and in case you have to restore them, you will have to restore all your partitions to a certain point in time.
Security: Since you have your data partitioned across multiple database servers, you have to follow consistent security practices across all your servers.

What is the performance gain I can expect by scaling out?

There is no easy way to calculate the performance gain, without actually implementing and testing the scenario, as the performance depends on a lot of other factors too. But in general, in a simulated DSS system, I achieved a performance gain of 25 to 30% by scaling out to just two SQL Servers.

Evaluation of fedarated databases.

2009-06-28T19:28:00.000-07:00

Okay, there are no high end systems, no quad processor boxes, no RAID, and no dedicated network. It's an evaluation scenario at it's simplest form.

I horizontally partitioned a huge table (5 Million rows, not so huge anyways!) across two database servers with 2.5 million rows on each server. Idea is to compare the response times in the following two scenarios:

Having the table on one server (5 million rows)
Having the table spread across two servers (2.5 million rows on each server)

Here is the hardware configuration of both the servers:
Server1: Server1
OS: Microsoft Windows 2000 Advanced Server with SP1
System model: DELL OptiPlex GX1
Processor: Pentium III, 500MHz
RAM: 128 MB

Server2: Server2
OS: Microsoft Windows 2000 Advanced Server with SP1
System model: DELL OptiPlex GX1
Processor: Pentium III, 500MHz
RAM: 128 MB

Here is the table structure:
CREATE TABLE [dbo].[tblPriceDefn] (
[ConfigID] [numeric](19, 0) NOT NULL ,
[PriceListID] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ValidityDate] [datetime] NOT NULL ,
[Price] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPriceDefn] WITH NOCHECK ADD
CONSTRAINT [PK_PriceDefn] PRIMARY KEY CLUSTERED
(
[ConfigID],
[PriceListID],
[ValidityDate]
) ON [PRIMARY]

Horizontal partitioning of the data:
For this evaluation purpose I took the tblPriceDefn table which has 5 Million rows. I divided the table into two equal halves depending upon the value of the PriceListID column. Stored the first half on the first server (Server1) and the second half on the second server (Server2). Then I created a distributed partioned view named View1 in the first server (Server1) which cobmines both the halves of the table tblPriceDefn. Here is the view definition:

CREATE VIEW View1
AS
SELECT * FROM [Server1].test.dbo.table1
UNION ALL
SELECT * FROM [Server2\inst1].test.dbo.table1

Performance testing:
The following queries are executed on the first server locally on both the base table and the partitioned view and the response times were recorded. Each query is executed 5 times and the average response time is recorded

Query 1:
--On the base table:
SELECT
PriceListID,
COUNT(*) [# of rows]
FROM
ArenaDBRel2.dbo.tblPriceDefn
GROUP BY
PriceListID
ORDER BY
PriceListID

Average response time on the base table with 5 million rows: 59 seconds

--On the partitioned view:
SELECT
PriceListID,
COUNT(*) [# of rows]
FROM
test.dbo.View1
GROUP BY
PriceListID
ORDER BY
PriceListID

Average response time on the partitioned view : 41 seconds
A gain of 18 seconds which is a 30% improvement in performance

Query 2:

--On the base table:
SELECT
DISTINCT PriceListID
FROM
ArenaDBRel2.dbo.tblPriceDefn
Average response time on the base table with 5 million rows: 45 seconds

--On the partitioned view:
SELECT
DISTINCT PriceListID
FROM
test.dbo.View1

Average response time on the partitioned view: 32 seconds
A gain of 13 seconds which is a 28% improvement in performance

Query 3:

--On the base table:
SELECT
PriceListID,
ConfigID,
Price
FROM
ArenaDBRel2.dbo.tblPriceDefn
WHERE
PriceListID = 'M05-05'
OR
PriceListID = 'M10-01'

Average response time on the base table with 5 million rows: 59 seconds
--On the partitioned view:
SELECT
PriceListID,
ConfigID,
Price
FROM
test.dbo.View1
WHERE
PriceListID = 'M05-05'
OR
PriceListID = 'M10-01'

Average response time on the partitioned view: 44 seconds
A gain of 15 seconds which is a 25% improvement in performance

Conclusion:
The federated database servers feature in SQL Server 2000 is a very useful one and we can be sure to see performance gains in the query response times. As I mentioned earlier, this is a very simple evaluation of the feature. I am going to try it out with more concurrent connections and complicated queries and will post more information on this site. Be sure to check back after a while! Please let me know if you have any suggestions regarding this page!

SQL Server security model

2009-06-28T18:54:00.000-07:00

Security is a major concern for the modern age systems/network/database administrators. It is natural for an administrator to worry about hackers and external attacks while implementing security. But there is more to it. It is essential to first implement security within the organization, to make sure right people have access to the right data. Without these security measures in place, you might find someone destroying your valuable data, or selling your company's secrets to your competitors or someone invading the privacy of others. Primarily a security plan must identify which users in the organization can see which data and perform which activities in the database.

SQL Server security model
To be able to access data from a database, a user must pass through two stages of authentication, one at the SQL Server level and the other at the database level. These two stages are implemented using Logins names and User accounts respectively. A valid login is required to connect to SQL Server and a valid user account is required to access a database.

Login: A valid login name is required to connect to an SQL Server instance. A login could be:

A Windows NT/2000 login that has been granted access to SQL Server
An SQL Server login, that is maintained within SQL Server

These login names are maintained within the master database. So, it is essential to backup the master database after adding new logins to SQL Server.

User: A valid user account within a database is required to access that database. User accounts are specific to a database. All permissions and ownership of objects in the database are controlled by the user account. SQL Server logins are associated with these user accounts. A login can have associated users in different databases, but only one user per database.

During a new connection request, SQL Server verifies the login name supplied, to make sure, that login is authorized to access SQL Server. This verification is called Authentication. SQL Server supports two authentication modes:

Windows authentication mode: With Windows authentication, you do not have to specify a login name and password, to connect to SQL Server. Instead, your access to SQL Server is controlled by your Windows NT/2000 account (or the group to which your account belongs to), that you used to login to the Windows operating system on the client computer/workstation. A DBA must first specify to SQL Server, all the Microsoft Windows NT/2000 accounts or groups that can connect to SQL Server

Mixed mode: Mixed mode allows users to connect using Windows authentication or SQL Server authentication. Your DBA must first create valid SQL Server login accounts and passwords. These are not related to your Microsoft Windows NT/2000 accounts. With this authentication mode, you must supply the SQL Server login and password when you connect to SQL Server. If you do not specify SQL Server login name and password, or request Windows Authentication, you will be authenticated using Windows Authentication.

Point to note is that, whatever mode you configure your SQL Server to use, you can always login using Windows authentication.

Windows authentication is the recommended security mode, as it is more secure and you don't have to send login names and passwords over the network. You should avoid mixed mode, unless you have a non-Windows NT/2000 environment or when your SQL Server is installed on Windows 95/98 or for backward compatibility with your existing applications.

SQL Server's authentication mode can be changed using Enterprise Manager (Right click on the server name and click on Properties. Go to the Security tab).

Authentication mode can also be changed using SQL DMO object model.

Here is a list of helpful stored procedures for managing logins and users:

sp_addlogin	Creates a new login that allows users to connect to SQL Server using SQL Server authentication
sp_grantlogin	Allows a Windows NT/2000 user account or group to connect to SQL Server using Windows authentication
sp_droplogin	Drops an SQL Server login
sp_revokelogin	Drops a Windows NT/2000 login/group from SQL Server
sp_denylogin	Prevents a Windows NT/2000 login/group from connecting to SQL Server
sp_password	Adds or changes the password for an SQL Server login
sp_helplogins	Provides information about logins and their associated users in each database
sp_defaultdb	Changes the default database for a login
sp_grantdbaccess	Adds an associated user account in the current database for an SQL Server login or Windows NT/2000 login
sp_revokedbaccess	Drops a user account from the current database
sp_helpuser	Reports information about the Microsoft users and roles in the current database

Now let's talk about controlling access to objects within the database and managing permissions. Apart from managing permissions at the individual database user level, SQL Server 7.0/2000 implements permissions using roles. A role is nothing but a group to which individual logins/users can be added, so that the permissions can be applied to the group, instead of applying the permissions to all the individual logins/users. There are three types of roles in SQL Server 7.0/2000:

Fixed server roles
Fixed database roles
Application roles

Fixed server roles: These are server-wide roles. Logins can be added to these roles to gain the associated administrative permissions of the role. Fixed server roles cannot be altered and new server roles cannot be created. Here are the fixed server roles and their associated permissions in SQL Server 2000:

Fixed server role	Description
sysadmin	Can perform any activity in SQL Server
serveradmin	Can set server-wide configuration options, shut down the server
setupadmin	Can manage linked servers and startup procedures
securityadmin	Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords
processadmin	Can manage processes running in SQL Server
dbcreator	Can create, alter, and drop databases
diskadmin	Can manage disk files
bulkadmin	Can execute BULK INSERT statements

Here is a list of stored procedures that are helpful in managing fixed server roles:

sp_addsrvrolemember	Adds a login as a member of a fixed server role
sp_dropsrvrolemember	Removes an SQL Server login, Windows user or group from a fixed server role
sp_helpsrvrole	Returns a list of the fixed server roles
sp_helpsrvrolemember	Returns information about the members of fixed server roles
sp_srvrolepermission	Returns the permissions applied to a fixed server role

Fixed database roles: Each database has a set of fixed database roles, to which database users can be added. These fixed database roles are unique within the database. While the permissions of fixed database roles cannot be altered, new database roles can be created. Here are the fixed database roles and their associated permissions in SQL Server 2000:

Fixed database role	Description
db_owner	Has all permissions in the database
db_accessadmin	Can add or remove user IDs
db_securityadmin	Can manage all permissions, object ownerships, roles and role memberships
db_ddladmin	Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements
db_backupoperator	Can issue DBCC, CHECKPOINT, and BACKUP statements
db_datareader	Can select all data from any user table in the database
db_datawriter	Can modify any data in any user table in the database
db_denydatareader	Cannot select any data from any user table in the database
db_denydatawriter	Cannot modify any data in any user table in the database

Here is a list of stored procedures that are helpful in managing fixed database roles:

sp_addrole	Creates a new database role in the current database
sp_addrolemember	Adds a user to an existing database role in the current database
sp_dbfixedrolepermission	Displays permissions for each fixed database role
sp_droprole	Removes a database role from the current database
sp_helpdbfixedrole	Returns a list of fixed database roles
sp_helprole	Returns information about the roles in the current database
sp_helprolemember	Returns information about the members of a role in the current database
sp_droprolemember	Removes users from the specified role in the current database

Application roles: Application roles are another way of implementing permissions. These are quite different from the server and database roles. After creating and assigning the required permissions to an application role, the client application needs to activate this role at run-time to get the permissions associated with that application role. Application roles simplify the job of DBAs, as they don't have to worry about managing permissions at individual user level. All they need to do is to create an application role and assign permissions to it. The application that is connecting to the database activates the application role and inherits the permissions associated with that role. Here are the characteristics of application roles:

There are no built-in application roles
Application roles contain no members
Application roles need to be activated at run-time, by the application, using a password
Application roles override standard permissions. For example, after activating the application role, the application will lose all the permissions associated with the login/user account used while connecting to SQL Server and gain the permissions associated with the application role.
Application roles are database specific. After activating an application role in a database, if that application wants to run a cross-database transaction, the other database must have a guest user account enabled

Here are the stored procedures that are required to manage application roles:

sp_addapprole	Adds an application role in the current database
sp_approlepassword	Changes the password of an application role in the current database
sp_dropapprole	Drops an application role from the current database
sp_setapprole	Activates the permissions associated with an application role in the current database

Now that we discussed different kinds of roles, let's talk about granting/revoking permissions to/from database users and database roles and application roles. The following T-SQL commands are used to manage permissions at the user and role level.

GRANT: Grants the specific permission (Like SELECT, DELETE etc.) to the specified user or role in the current database
REVOKE: Removes a previously granted or denied permission from a user or role in the current database
DENY: Denies a specific permission to the specified user or role in the current database

Using the above commands, permissions can be granted/denied/revoked to users/roles on all database objects. You can manage permissions at as low as the column level.

Note: There is no way to manage permissions at the row level. That is, in a given table, you can't grant SELECT permission on a specific row to User1 and deny SELECT permission on another row to User2. This kind of security can be implemented by using views and stored procedures effectively. Click here to read about row level security implementation in SQL Server databases. Just an FYI, Oracle has a feature called "Virtual Private Databases" (VPD) that allows DBAs to configure permissions at row level.
SQL Server security best practices
Here is an ideal implementation of security in a Windows NT/2000 environment with SQL Server 7.0/2000 database server:

Configure SQL Server to use Windows authentication mode
Depending upon the data access needs of your domain users, group them into different global groups in the domain
Consolidate these global groups from all the trusted domains into the Windows NT/2000 local groups in your SQL Server computer
The Windows NT/2000 local groups are then granted access to log into the SQL Server
Add these Windows NT/2000 local groups to the required fixed server roles in SQL Server
Associate these local group logins with individual user accounts in the databases and grant them the required permissions using the database roles.
Create custom database roles if required, for finer control over permissions

Here is a security checklist and some standard security practices and tips:

Restrict physical access to the SQL Server computer. Always lock the server while not in use.
Make sure, all the file and disk shares on the SQL Server computer are read-only. In case you have read-write shares, make sure only the right people have access to those shares.
Use the NTFS file system as it provides advanced security and recovery features.
Prefer Windows authentication to mixed mode. If mixed mode authentication is inevitable, for backward compatibility reasons, make sure you have complex passwords for sa and all other SQL Server logins. It is recommended to have mixed case passwords with a few numbers and/or special characters, to counter the dictionary based password guessing tools and user identity spoofing by hackers.
Rename the Windows NT/2000 Administrator account on the SQL Server computer to discourage hackers from guessing the administrator password.
In a website environment, keep your databases on a different computer than the one running the web service. In other words, keep your SQL Server off the Internet, for security reasons.
Keep yourself up-to-date with the information on latest service packs and security patches released by Microsoft. Carefully evaluate the service packs and patches before applying them on the production SQL Server. Bookmark this page for the latest in the security area from Microsoft: http://www.microsoft.com/security/
If it is appropriate for your environment, hide the SQL Server service from appearing in the server enumeration box in Query Analyzer, using the /HIDDEN:YES switch of NET CONFIG SERVER command.
Enable login auditing at the Operating System and SQL Server level. Examine the audit for login failure events and look for trends to detect any possible intrusion.
If it fits your budget, use Intrusion Detection Systems (IDS), especially on high-risk online database servers. IDS can constantly analyze the inbound network traffic, look for trends and detect Denial of Service (DoS) attacks and port scans. IDS can be configured to alert the administrators upon detecting a particular trend.
Disable guest user account of Windows. Drop guest user from production databases using sp_dropuser.
Do not let your applications query and manipulate your database directly using SELECT/INSERT/UPDATE/DELETE statements. Wrap these commands within stored procedures and let your applications call these stored procedures. This helps centralize business logic within the database, at the same time hides the internal database structure from client applications.
Let your users query views instead of giving them access to the underlying base tables.
Discourage applications from executing dynamic SQL statements. To execute a dynamic SQL statement, users need explicit permissions on the underlying tables. This defeats the purpose of restricting access to base tables using stored procedures and views.
Don't let applications accept SQL commands from users and execute them against the database. This could be dangerous (known as SQL injection), as a skilled user can input commands that can destroy the data or gain unauthorized access to sensitive information.
Take advantage of the fixed server and database roles by assigning users to the appropriate roles. You could also create custom database roles that suit your needs.
Carefully choose the members of the sysadmin role, as the members of the sysadmin role can do anything in the SQL Server. Note that, by default, the Windows NT/2000 local administrators group is a part of the sysadmin fixed server role.
Constantly monitor error logs and event logs for security related alerts and errors.
SQL Server error logs can reveal a great deal of information about your server. So, secure your error logs by using NTFS permissions.
Secure your registry by restricting access to the SQL Server specific registry keys like HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer.
If your databases contain sensitive information, consider encrypting the sensitive pieces (like credit card numbers and Social Security Numbers (SSN)). There are undocumented encryption functions in SQL Server, but I wouldn't recommend those. If you have the right skills available in your organization, develop your own encryption/decryption modules using Crypto API or other encryption libraries.
If you are running SQL Server 7.0, you could use the encryption capabilities of the Multi-Protocol net library for encrypted data exchange between the client and SQL Server. SQL Server 2000 supports encryption over all protocols using Secure Socket Layer (SSL). See SQL Server 7.0 and 2000 Books Online (BOL) for more information on this topic. Please note that, enabling encryption is always a tradeoff between security and performance, because of the additional overhead of encryption and decryption.
Prevent unauthorized access to linked servers by deleting the linked server entries that are no longer needed. Pay special attention to the login mapping between the local and remote servers. Use logins with the bare minimum privileges for configuring linked servers.
DBAs generally tend to run SQL Server service using a domain administrator account. That is asking for trouble. A malicious SQL Server user could take advantage of these domain admin privileges. Most of the times, a local administrator account would be more than enough for SQL Server service.
DBAs also tend to drop system stored procedures like xp_cmdshell and all the OLE automation stored procedures (sp_OACreate and the likes). Instead of dropping these procedures, deny EXECUTE permission on them to specific users/roles. Dropping these procedures would break some of the SQL Server functionality.
Be prompt in dropping the SQL Server logins of employees leaving the organization. Especially, in the case of a layoff, drop the logins of those poor souls ASAP as they could do anything to your data out of frustration.
When using mixed mode authentication, consider customizing the system stored procedure sp_password, to prevent users from using simple and easy-to-guess passwords.
To setup secure data replication over Internet or Wide Area Networks (WAN), implement Virtual Private Networks (VPN) . Securing the snapshot folder is important too, as the snapshot agent exports data and object scripts from published databases to this folder in the form of text files. Only the replication agents should have access to the snapshot folder.
It is good to have a tool like Lumigent Log Explorer handy, for a closer look at the transaction log to see who is doing what in the database.
Do not save passwords in your .udf files, as the password gets stored in clear text.
If your database code is proprietary, encrypt the definition of stored procedures, triggers, views and user defined functions using the WITH ENCRYPTION clause. dbLockdown is a tool that automates the insertion of the WITH ENCRYPTION clause and handles all the archiving of encrypted database objects so that they can be restored again in a single click. Click here to find out more information about this product.
In database development environments, use a source code control system like Visual Source Safe (VSS) or Rational Clear Case. Control access to source code by creating users in VSS and giving permissions by project. Reserve the 'destroy permanently' permission for VSS administrator only. After project completion, lock your VSS database or leave your developers with just read-only access.
Store the data files generated by DTS or BCP in a secure folder/share and delete these files once you are done.
Install anti-virus software on the SQL Server computer, but exclude your database folders from regular scans. Keep your anti-virus signature files up to date.
SQL Server 2000 allows you to specify a password for backups. If a backup is created with a password, you must provide that password to restore from that backup. This discourages unauthorized access to backup files.
Windows 2000 introduced Encrypted File System (EFS) that allows you to encrypt individual files and folders on an NTFS partition. Use this feature to encrypt your SQL Server database files. You must encrypt the files using the service account of SQL Server. When you want to change the service account of SQL Server, you must decrypt the files, change the service account and encrypt the files again with the new service account.

Q & A

2009-06-23T20:40:00.001-07:00

What is a self join? Explain it with an example.
A.Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.

CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)

INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'

SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid

Here's an advanced query using a LEFT OUTER JOIN that even returns the employees without managers (super bosses)

SELECT t1.empname [Employee], COALESCE(t2.empname, 'No manager') [Manager]
FROM emp t1
LEFT OUTER JOIN
emp t2
ON
t1.mgrid = t2.empid

There is a trigger defined for INSERT operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly insterted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?
A.Instantiating COM objects is a time consuming process and since you are doing it from within a trigger, it slows down the data insertion process. Same is the case with sending emails from triggers. This scenario can be better implemented by logging all the necessary data into a separate table, and have a job which periodically checks this table and does the needful.

What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?
A.Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there's no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder
Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.
Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.
Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also. Search SQL Server 2000 books online for INSTEAD OF triggers.
Also check out books online for 'inserted table', 'deleted table' and COLUMNS_UPDATED()

What is the system function to get the current user's user id?
A.USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?
A.An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement.
Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OACreate stored procedure. Also see books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy. For an example of creating a COM object in VB and calling it from T-SQL, see 'My code library' section of this site.

Can you have a nested transaction?
A.Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and @@TRANCOUNT

What is a join and explain different types of joins.
A.Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.

Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

Write down the general syntax for a SELECT statements covering all the options.
A.Here's the basic syntax:
SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC] ]

What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
A.Cursors allow row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.
Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.
Most of the times, set based operations can be used instead of cursors. Here is an example:
If you have to give a flat hike to your employees using the following criteria:
Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike
In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:
UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END
Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row.

What is database replicaion? What are the different types of replication you can set up in SQL Server?
A.Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:

Snapshot replication
Transactional replication (with immediate updating subscribers, with queued updating subscribers)
Merge replication

Explian different types of BACKUPs avaialabe in SQL Server? Given a particular scenario, how would you go about choosing a backup plan?
A.Types of backups you can create in SQL Sever 7.0+ are Full database backup, differential database backup, transaction log backup, filegroup backup.

What are the different ways of moving data/databases between servers and databases in SQL Server?
A.There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, dettaching and attaching databases, replication, DTS, BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data.

What are statistics, under what circumstances they go out of date, how do you update them
A.Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.

Some situations under which you should update statistics:
1) If there is significant change in the key values in the index
2) If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
3) Database is upgraded from a previous version

Look up SQL Server books online for the following commands: UPDATE STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP STATISTICS, sp_autostats, sp_createstats, sp_updatestats

As a part of your job, what are the DBCC commands that you commonly use for database maintenance?
A.DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC, DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there are a whole load of DBCC commands which are very useful for DBAs

How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?
A.SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal confuguration mode. Check out SQL Server books online for more parameters and their explanations.

Explain CREATE DATABASE syntax
A.Many of us are used to craeting databases from the Enterprise Manager or by just issuing the command: CREATE DATABAE MyDB. But what if you have to create a database with two filegroups, one on drive C and the other on drive D with log on drive E with an initial size of 600 MB and with a growth factor of 15%? That's why being a DBA you should be familiar with the CREATE DATABASE syntax. Check out SQL Server books online for more information.

What is blocking and how would you troubleshoot it?
A.Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.

What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
A.Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.

A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

What are the steps you will take, if you are tasked with securing an SQL Server?
A.Again this is another open ended question. Here are some things you could talk about: Preferring NT authentication, using server, databse and application roles to control access to the data, securing the physical database files using NTFS permissions, using an unguessable SA password, restricting physical access to the SQL Server, renaming the Administrator account on the SQL Server computer, disabling the Guest account, enabling auditing, using multiprotocol encryption, setting up SSL, setting up firewalls, isolating SQL Server from the web server etc.
http://vyaskn.tripod.com/sql_server_security_best_practices.htm

What are the steps you will take to improve performance of a poor performing query?
A.This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.
Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.

What is RAID and what are different types of RAID configurations?
A.RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance.
http://www.raid-advisory.com/

What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach?
A.Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.
Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.
If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
A.DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

What is lock escalation?
A.Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.

CREATE INDEX myIndex ON myTable(myColumn)
What type of Index will get created after executing the above statement?
A.Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.

Explain different isolation levels
A.An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.

What is a transaction and what are ACID properties?
A.A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction.

What are defaults? Is there a column to which a default can't be bound?
A.A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them.

Define candidate key, alternate key, composite key.
A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

A key formed by combining at least two or more columns is called composite key.

What is bit datatype and what's the information that can be stored inside a bit column?
A.Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.

What are user defined datatypes and when you should go for them?
A.User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables.

What's the difference between a primary key and a unique key?
A.Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

How you can rename a database ?
A. To rename a database first get the database into Single user mode . by using the query below ( run these query in query analyzer)

ALTER DATABASE DBMydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

then rename the database using sp_rename

sp_renamedb 'DBMydb', 'DBmydb_new'

then change the access mode of the database to multiuser

ALTER DATABASE DBmydb_new SET MULTI_USER

What is mean by suspect status of a database ?
A. SQL Server gives a database 'suspect' status when it is unable to recover the database . reasons may be many , may be a torn page detection , may be datafile location no found.

How many access mode of database are in MSSQL
A there are three access mode of a database SINGLE_USER , RESTRICTED_USER and MULTI_USER . SINGLE_USER mode restricts database access to one user Connection. RESTRICTED_USER mode restricts database access to db_owner, dbcreator, and sysadmin roles. All other users are disconnected.MULTI_USER mode lets all users with proper access connect to the database.

What are the default databases in MSSQL Server Instance after Fresh installation
A. there are 4 default databases .

Master
This is your most critical database, and is the core of your SQL Server implementation.
Limit changes and access to this database as much as possible. Without the master
database, your SQL Server instance will not start up.
MSDB
This database contains tables that control and track the elements of the SQL Server
Agent, as well as replication, log shipping (Enterprise Edition), database backup and
restore history, database maintenance plan configurations and history, and a subset of
tables participating in replication status and jobs. DTS packages saved to SQL Server are also stored within this database.
Model
The model database is a template database, which, when modified, will affect the default
values of databases that are created in the future. For example, if a table is added to the
model database, future databases created for the SQL Server instance will include this
table.
Tempdb
The tempdb database is used to hold all temporary tables and temporary stored
procedures. SQL Server also uses it for internal sorting and query operations (called
work tables, or spooling). This database is re-created each time SQL Server is started,
and cannot be used to store permanent tables.

What is replication ?
A. Replication is a process which enables data and database objects to be copied and modified from one database to another across different networks and platforms.the physical seperation of the databases and latency are integral part of the design process in replication. MSSQL Server 2000 permits 3 different kind of replication. they are snapshot,transactional and merge.

How many typf of backups are supported by MSSQL ?
A. there are three type of backup

Full Database Backup.
Transaction Log Backup
Differential Backup

Whats is Log Shipping ?
A. Log shipping is the process of backing up of database and transaction log files on a production SQL server, and then restoring them onto a standby server. The key feature of log shipping is that it will automatically backup transaction logs throughout the day (for whatever interval you specify) and automatically restore them on the standby server. This in effect keeps the two SQL Servers in "sync". Should the production server fail, all you have to do is point the users to the new server, and you are all set.

Name the types of Recovery model in SQL Server ?
A. There are thre recovery model in MSSQL Server
• Simple Recovery Model .
• Full Recovery Model.
• Bulk Logged Recovery Model.

What is a Dead Lock ?
A . Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other's object. For example, User1 has a lock on object "A" and wants a lock on object "B" and User2 has a lock on object "B" and wants a lock on object "A". In this case, SQL Server ends a deadlock by choosing the user, who will be a deadlock victim. After that, SQL Server rolls back the breaking user's transaction, sends message number 1205 to notify the user's application about breaking, and then allows the nonbreaking user's process to continue.
Note. To reduce the chance of a deadlock, you should minimize the size of transactions and transaction times.

How many type of Locks are there ?
A. There are three main types of locks that SQL Server 7.0/2000 uses
Shared locks
Update locks
Exclusive locks

A database is consists of how many type of files ? Name their extensions
A. A database can consist of three types of file :
Primary file , Seconday file and transaction logs
The primary data file is the starting point of the database and points to any other files in the database. If the primary file is not large enough to hold all the data in the database, then secondary files are required.Transaction logs files hold records of updates to the data files, and can be used to recover the database after an error. Every database has at least one transaction log file, and some databases may have more than one.
The recommended file name extension for primary data files is .mdf.
The recommended file name extension for secondary data files is .ndf.
The recommended file name extension for log files is .ldf

How many type of indexes are there ? How many Index can be created on a table ?
A. there are two type of indexes Clustered and Non-Clustured, 249 Non Clustured and 1 Clustered index

How you can get the last identity value inserted in any table ?
A. SQL Server has a System Variable @@IDENTITY which gives the last identity element value inserted in any table

How many type of Contraints are in MSSQL.
A. SQL Server 2000 supports five classes of constraints.
1) NOT NULL
2) CHECK
3) UNIQUE
4) PRIMARY KEY
5) FOREIGN KEY

How you can trap error in Sql Server
A. by using @@error .
After each Transact-SQL statement, the server sets the variable to an integer value:
0—if the statement was successful
Error number—if the statement has failed

What is @@Fetch_status ?
A. @@fetch_status is a function (or global variable) that returns the success code of the last Fetch statement executed during the current connection. It is often used as an exit criterion in loops that fetch records from a cursor.

Whats the limitation of user defined funtion ?
A User-defined functions have one serious limitation. They cannot have side effects. A function side effect is any permanent change to resources (such as tables) that have a scope outside of the function (such as a non-temporary table that is not declared in the function). Basically, this requirement means that a function should return a value while changing nothing in the database. it means in short that "user defined function can not use UPDATE / DELETE on permament table objects in MSSQL"

In how many ways you can recieve information from stored procedure
A. there are 4 ways to receive information from a stored procedure:
Resultset , Parameters , Return value , A global cursor that can be referenced outside the stored procedure.

How many type of triggers are there in Sql Server.
A. There are two type of triggers
• After Triggers
• Instead of Triggers

Whats the Difference between datetime and smalldatetime datatype in SQL Server .
A . The main difference between these two datatypes is in the amount of space they occupy. datetime occupies eight bytes and smalldatetime only four. The difference in size is due to a difference in precision. The precision of smalldatetime is one minute, and it covers dates from January 1, 1900 , through June 6, 2079 , which is usually more than enough. The precision of datetime is 3.33 ms, and it covers dates from January 1, 1753 , to December 31, 9999 .

How many type of authentication method are there in SQL Server 2005 and 2000 and what is that ?
A. There are two type of authentication method in SQL Server 2005 and 2000
Windows Authentication
SQL Server Authentication

How can you generate GUID in in Transact-SQL ?
A. GUIDs can be generated using the NEWID function.

How many system datatypes are in SQL Server.
A. 27

What is the Maximum number of input and output parameters in Stored procedure in SQL Server 2000 ?
A. 1024

How can we improve stored procedure performance? Explain the performance tuning techniques.

1. Use many WHERE clauses in the SELECT statements.
2. Select only those fields which you really require.
3. Joins are expensive in terms of time. Join the tables using related fields manly indexed fields.
4. Don't use unused tables.

NOTES: When you execute the stored proc, SQL Server generates an execution plan based on your indexes and statistics available. Now it is possible that the stored proc inserts a lot of rows into some table which is used in another statement in a join and the plan that was built is not efficient for the subsequent sql statement because the profile of the table has changed since the plan was generated. I would suggest splitting the stored proc into two and the split should be done such that the second proc deals with the updated table. When the second proc is executed, it will have a more realistic execution plan for the updated table.

TRY...CATCH in SQL Server 2005

2009-06-03T19:32:00.000-07:00

Introduction
SQL Server 2005 offers a number of new features over its predecessor, including many features aimed at making working with databases more like writing .NET application code. For example, in SQL Server 2005, stored procedures, triggers, UDFs, and so on can be written using any .NET Framework programming language (such as Visual Basic or C#). Another feature, and the focus of this article, is SQL Server 2005's support for TRY...CATCH blocks. TRY...CATCH blocks are the standard approach to exception handling in modern programming languages, and involve:

A TRY Block - the TRY block contains the instructions that might cause an exception
A CATCH Block - if an exception occurs from one of the statements in the TRY block, control is branched to the CATCH block, where the exception can be handled, logged, and so on.

See the Wikipedia Exception Handling entry for more information on the TRY...CATCH construct as well as exception handling concepts in general.

Prior to SQL Server 2005, detecting errors resulting from T-SQL statements could only be handled by checking a global error variable, @@ERROR. Because the @@ERROR variable value is reset after each SQL statement, this antiquated approach leads to rather bloated stored procedures, as the variable must be checked after each statement with code to handle any problems.

The TRY...CATCH block in SQL Server 2005 offers a much more readable syntax and one that developers are more familiar with. In this article we'll look at the new TRY...CATCH block and examine how it can be used to rollback a transaction in the face of an error. Read on to learn more!

Checking @@ERROR - the Old Way of Handling Errors in T-SQL
SQL Server provides an @@ERROR variable that indicates the status of the last completed SQL statement in a given batch. If a SQL statement is completed successfully, @@ERROR is assigned 0. If, however, an error occurs, @@ERROR is set to the number of the error message.

To see how the @@ERROR variable can be used, imagine that we have a data-driven web application that maintains employee information. Let's assume that our database has Employees and EmployeePhoneNumbers tables, among others. These two tables share a one-to-many relationship; that is, each Employees record can have an arbitrary number of related records in the EmployeePhoneNumbers table. There might be one for their office phone, one for their pager, one for their cell phone, and so on. Imagine that our database includes a stored procedure, DeleteEmployee, which is comprised of two DELETE statements - one to delete the employee's related phone numbers from the system and one to delete the actual employee record:

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )
AS

-- Delete the Employee's phone numbers
DELETE FROM EmployeePhoneNumbers
WHERE EmployeeID = @EmployeeID

-- Delete the Employee record
DELETE FROM Employees
WHERE EmployeeID = @EmployeeID

Since we want these two delete statements to be atomic and either both fail or both succeed, we need to wrap up these two statements into a transaction. By using a transaction, we can rollback the transaction in the face of an error and undo any changes made since the start of the exception. To accomplish this we might initially try to use the following syntax:

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )
AS

BEGIN TRANSACTION    -- Start the transaction

-- Delete the Employee's phone numbers
DELETE FROM EmployeePhoneNumbers
WHERE EmployeeID = @EmployeeID

-- Delete the Employee record
DELETE FROM Employees
WHERE EmployeeID = @EmployeeID


-- See if there is an error
IF @@ERROR <> 0
-- There's an error b/c @ERROR is not 0, rollback
ROLLBACK
ELSE
COMMIT   -- Success!  Commit the transaction

This stored procedure (it appears) starts a transaction, runs the two DELETE statements, and then checks to see if there was an error. If there was one, it rolls the transaction back, else it commits the transaction. I say "it appears" because this syntax, while legal, is semantically incorrect because the @@ERROR variable is set after every SQL statement. Therefore, if the first DELETE statement has an error the @@ERROR variable will be set to its error number. Then, the second DELETE will execute. If this second DELETE succeeds, @@ERROR will be set back to 0, in which case the transaction will be committed even though there was a problem with the first statement! Whoops!

Instead, a check must be made after every SQL statement to see if there has been an error. If so, the transaction must be rolled back and the stored procedure exited. This can lead to bulky scripts as a stored procedure with, say, five statements will have five checks against the @@ERROR variable. And if you forget to cut and paste a check in for a particular statement you're opening yourself up to a potential problem.

For more information on transactions and the @@ERROR syntax used for checking for errors and rolling back as needed, see Managing Transactions in SQL Server Stored Procedures.

Handling Errors With SQL Server 2005's TRY...CATCH Blocks
While SQL Server 2005 still supports the @@ERROR approach, a better alternative exists with its new TRY...CATCH blocks. As with programming languages like Visual Basic, C#, and Java, the SQL Server 2005 TRY...CATCH block executes a number of statements in the TRY block. If there are no errors in any of the statements, control proceeds to after the CATCH block. If, however, one of the statements causes an error, control branches immediately to the start of the CATCH block. Furthermore, like programming languages, nested TRY...CATCH blocks are allowed, meaning that you can have an entire TRY...CATCH block in the TRY or CATCH portions of an "outter" TRY...CATCH block.

BEGIN TRY
 Try Statement 1
 Try Statement 2
 ...
 Try Statement M
END TRY
BEGIN CATCH
 Catch Statement 1
 Catch Statement 2
 ...
 Catch Statement N
END CATCH

The following system functions are available in the CATCH block and can be used to determine additional error information:

Function	Description
`ERROR_NUMBER()`	Returns the number of the error
`ERROR_SEVERITY()`	Returns the severity
`ERROR_STATE()`	Returns the error state number
`ERROR_PROCEDURE()`	Returns the name of the stored procedure or trigger where the error occurred
`ERROR_LINE()`	Returns the line number inside the routine that caused the error
`ERROR_MESSAGE()`	Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times

Also realize that not all errors generating by the TRY block statements are passed onto the CATCH block. Any errors with a severity of 10 or less are considered to be warnings and do not branch control flow to the CATCH block. Also, any errors that sever the database connection will not cause the CATCH block to be reached.

Let's look at a quick example of using TRY...CATCH, after which we'll turn our attention to using this new construct for rolling back transactions in the face of an error. The following example shows a very simply INSERT query on the Northwind database's Products table. The Products table's ProductID column is an IDENTITY column and therefore its value can't be specified when inserting a new record. However, I've specified this value in the following INSERT statement. Hence, control is turned over to the CATCH block where error information is displayed.

BEGIN TRY
 -- This will generate an error, as ProductID is an IDENTITY column
 -- Ergo, we can't specify a value for this column...
 INSERT INTO Products(ProductID, ProductName)
 VALUES(1, 'Test')
END TRY
BEGIN CATCH
 SELECT 'There was an error! ' + ERROR_MESSAGE()
END CATCH

This query will return a single record with a single column with the contents: "There was an error! Cannot insert explicit value for identity column in table 'Products' when IDENTITY_INSERT is set to OFF."

Using TRY...CATCH to Rollback a Transaction in the Face of an Error
As discussed earlier in this article, one of the downsides of the @@ERROR variable approach is that for transactions a check against this variable must be added after each and every SQL statement to determine if an error occurred and, if so, to rollback the transaction. With SQL Server 2005's TRY...CATCH block, however, these types of scripts are greatly simplified, as the following example illustrates:

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )
AS

BEGIN TRY
 BEGIN TRANSACTION    -- Start the transaction

 -- Delete the Employee's phone numbers
 DELETE FROM EmployeePhoneNumbers
 WHERE EmployeeID = @EmployeeID

 -- Delete the Employee record
 DELETE FROM Employees
 WHERE EmployeeID = @EmployeeID

 -- If we reach here, success!
 COMMIT
END TRY
BEGIN CATCH
-- Whoops, there was an error
IF @@TRANCOUNT > 0
   ROLLBACK

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
       @ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

In the TRY block a transaction is started and the two DELETE statements are performed. If both DELETEs succeed, the COMMIT will be reached and the transaction committed. If, however, either one produces an error, control will be routed to the CATCH block where the transaction will be rolled back. Also, the CATCH block re-raises the error (using RAISERROR) so that the error information will be percolated up to the application that invoked the stored procedure. For an ASP.NET web application, that means that an exception will be raised in the .NET code that invoked this stored procedure, as chances are you not only want to rollback the transaction, but have some sort of error message handled in the web application as well so that the end user knows that their action failed.

Adding the call to RAISERROR in the CATCH block is tantamount to having a TRY...CATCH block in a programming language that re-throws the exception in the CATCH block after logging it or performing other actions. The action performed in the example above is rolling back the transaction, but could also include logging logic. If you omit the RAISERROR, the ASP.NET application won't throw an exception from executing the database command. Chances are you want to have an exception thrown on the ASP.NET side (so that you don't fail silently). If so, leave in the RAISERROR call.

Conclusion
SQL Server 2005's new TRY...CATCH block brings the familiar TRY...CATCH exception handling to T-SQL. Prior to SQL Server 2005, errors could only be detected in SQL scripts through the use of the @@ERROR variable, which annoyingly reset after each SQL statement, thereby requiring checks after each and every statement. This led to bloated script that was prone to typos or cut and paste errors leading to potentially serious problems. With SQL Server 2005's TRY...CATCH block, on the other hand, anytime an error is raised by one of the statements in the TRY block, control is directed to the CATCH block. As we saw in this article, the TRY...CATCH block allows for much more readable and cleaner error handling in transaction settings.

Common Table Expressions (CTE) in SQL Server 2005

2009-06-03T18:59:00.000-07:00

When crafting a query in SQL, there are often times when we may need to operate over a set of data that doesn't inherently exist within the system. For example, the database for an eCommerce web application would have the standard tables - Products, Customers, Orders, OrderDetails, and so on - but we may need to run reports on a particular subset of the data or against aggregate data across these tables. Or the reporting queries we need might need to group or filter by results returned by scalar subqueries. Typically, views are used to break down complex queries into digestible chunks or to provide scalar subquery results that can be grouped and filtered. Views, however, are sometimes overkill, as they are permanent objects at the system-level. If we only need to reference this complex query in a single stored procedure or UDF, another option is to use a derived table. Unfortunately, derived tables muddle the readability of the query and must be repeated for each use in a statement.

Common Table Expressions, or CTE, are a new construct introduced in Microsoft SQL Server 2005 that offer a more readable form of the derived table that can be declared once and referenced multiple times in a query. Moreover, CTEs can be recursively defined, allowing a recursive entity to be enumerated without the need for recursive stored procedures. In this article we'll examine the benefits, uses, and syntax of both recursive and non-recursive CTEs. Read on to learn more!

A Simple Common Table Expression Example
Before we dive into the syntax or gritty details of CTEs, let's start by looking at a simple example. I think you'll agree that even without knowing the syntax of CTEs, they are pretty readable and straightforward (the hallmark of a well-designed programming language construct).

 WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice) AS
(
   SELECT
      p.ProductName,
      c.CategoryName,
      p.UnitPrice
   FROM Products p
      INNER JOIN Categories c ON
         c.CategoryID = p.CategoryID
   WHERE p.UnitPrice > 10.0
)

SELECT *
FROM ProductAndCategoryNamesOverTenDollars
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC

This query creates a CTE named ProductAndCategoryNamesOverTenDollars that returns the name, category name, and price of those products whose unit price exceeds $10.00. Once the CTE has been defined, it must then immediately be used in a query. The query treats the CTE as if were a view or table in the system, returning the three fields defined by the CTE (ProductName, CategoryName, and UnitPrice), ordered alphabetically by category, then by price, and then alphabetically by product name.

In short, a Common Table Expression allows us to define a temporary, view-like construct. We start by (optionally) specifying the columns it returns, then define the query. Following that, the CTE can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Common Table Expression Syntax
A Common Table Expression contains three core parts:

The CTE name (this is what follows the WITH keyword)
The column list (optional)
The query (appears within parentheses after the AS keyword)

The query using the CTE must be the first query appearing after the CTE. That is, you cannot do the following:

 WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice) AS
(
   SELECT
      p.ProductName,
      c.CategoryName,
      p.UnitPrice
   FROM Products p
      INNER JOIN Categories c ON
         c.CategoryID = p.CategoryID
   WHERE p.UnitPrice > 10.0
)

SELECT *
FROM Products

SELECT *
FROM ProductAndCategoryNamesOverTenDollars
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC

The ProductAndCategoryNamesOverTenDollars CTE only applies to the first query following it. So when the second query is reached, ProductAndCategoryNamesOverTenDollars is undefined, resulting in an "Invalid object name 'ProductAndCategoryNamesOverTenDollars'" error message.

You can, however, define multiple CTEs after the WITH keyword by separating each CTE with a comma. For example, the following query uses two CTEs. The subsequent SELECT query then uses an INNER JOIN to match together the records from the two CTEs:

 WITH CategoryAndNumberOfProducts (CategoryID, CategoryName, NumberOfProducts) AS
(
   SELECT
      CategoryID,
      CategoryName,
      (SELECT COUNT(1) FROM Products p
       WHERE p.CategoryID = c.CategoryID) as NumberOfProducts
   FROM Categories c
),

ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice) AS
(
   SELECT
      ProductID,
      CategoryID,
      ProductName,
      UnitPrice
   FROM Products p
   WHERE UnitPrice > 10.0
)

SELECT c.CategoryName, c.NumberOfProducts,
      p.ProductName, p.UnitPrice
FROM ProductsOverTenDollars p
   INNER JOIN CategoryAndNumberOfProducts c ON
      p.CategoryID = c.CategoryID
ORDER BY ProductName

Unlike a derived table, CTEs can be defined just once, yet appear multiple times in the subsequent query. To demonstrate this, consider the following example: the Northwind database's Employees table contains an optional ReportsTo column that, if specified, indicates the employee's manager. ReportsTo is a self-referencing foreign key, meaning that, if provided, it refers back to another EmployeeID in the Employees table. Imagine that we wanted to display a list of employees including how many other employees they directly managed. This could be done using a simple, CTE-free SELECT statement, but let's use a CTE for now (for reasons which will become clear soon):

 WITH EmployeeSubordinatesReport (EmployeeID, LastName, FirstName, NumberOfSubordinates, ReportsTo) AS
(
   SELECT
      EmployeeID,
      LastName,
      FirstName,
      (SELECT COUNT(1) FROM Employees e2
       WHERE e2.ReportsTo = e.EmployeeID) as NumberOfSubordinates,
      ReportsTo
   FROM Employees e
)

SELECT LastName, FirstName, NumberOfSubordinates
FROM EmployeeSubordinatesReport

This query will return the employees records, showing each employee's last and first name along with how many other employees they manage. As the figure below shows, only Andrew Fuller and Steven Buchanan are manager material.

Now, imagine that our boss (Andrew Fuller, perhaps) comes charging into our office and demands that the report also lists each employee's manager's name and number of subordinates (if the employee has a manager, that is - Mr. Fuller is all to quick to point out that he reports to no one). Adding such functionality is a snap with the CTE - just add it in a LEFT JOIN!

 WITH EmployeeSubordinatesReport (EmployeeID, LastName, FirstName, NumberOfSubordinates, ReportsTo) AS
(
   SELECT
      EmployeeID,
      LastName,
      FirstName,
      (SELECT COUNT(1) FROM Employees e2
       WHERE e2.ReportsTo = e.EmployeeID) as NumberOfSubordinates,
      ReportsTo
   FROM Employees e
)

SELECT Employee.LastName, Employee.FirstName, Employee.NumberOfSubordinates,
   Manager.LastName as ManagerLastName, Manager.FirstName as ManagerFirstName, Manager.NumberOfSubordinates as ManagerNumberOfSubordinates
FROM EmployeeSubordinatesReport Employee
   LEFT JOIN EmployeeSubordinatesReport Manager ON
      Employee.ReportsTo = Manager.EmployeeID

With this additional LEFT JOIN, the employee's manager's results are returned; if there's no manager for the employee, NULLs are returned instead.

When to Use Common Table Expressions

When to Use Common Table Expressions
Common Table Expressions offer the same functionality as a view, but are ideal for one-off usages where you don't necessarily need a view defined for the system. Even when a CTE is not necessarily needed (as when listing just the employees and their subordinate count in the example above), it can improve readability. In Using Common Table Expressions, Microsoft offers the following four advantages of CTEs: Create a recursive query. Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata. Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access. Reference the resulting table multiple times in the same statement. Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated. Using scalar subqueries (such as the `(SELECT COUNT(1) FROM ...)` examples we've looked at in this article) cannot be grouped or filtered directly in the containing query. Similarly, when using SQL Server 2005's ranking functions - `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, and so on - the containing query cannot include a filter or grouping expression to return only a subset of the ranked results. For both of these instances, CTEs are quite handy. (For more on SQL Server 2005's ranking capabilities, be sure to read: Returning Ranked Results with Microsoft SQL Server 2005.) CTEs can also be used to recursively enumerate hierarchical data. We'll examine this next!

Common Table Expressions offer the same functionality as a view, but are ideal for one-off usages where you don't necessarily need a view defined for the system. Even when a CTE is not necessarily needed (as when listing just the employees and their subordinate count in the example above), it can improve readability. In Using Common Table Expressions, Microsoft offers the following four advantages of CTEs:

Create a recursive query.
Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
Reference the resulting table multiple times in the same statement.
Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

Using scalar subqueries (such as the (SELECT COUNT(1) FROM ...) examples we've looked at in this article) cannot be grouped or filtered directly in the containing query. Similarly, when using SQL Server 2005's ranking functions - ROW_NUMBER(), RANK(), DENSE_RANK(), and so on - the containing query cannot include a filter or grouping expression to return only a subset of the ranked results. For both of these instances, CTEs are quite handy. (For more on SQL Server 2005's ranking capabilities, be sure to read: Returning Ranked Results with Microsoft SQL Server 2005.)

CTEs can also be used to recursively enumerate hierarchical data. We'll examine this next!

Recursive Common Table Expressions
Recursion is the process of defining a solution to a problem in terms of itself. For example, a teacher needs to sort a stack of tests alphabetically by the students' names. She could process the tests one at a time and, for each test, insert it into the appropriate spot to the left (called insertion sort), probably the way most people sort a hand of cards (at least that's the way I do it). However, depending on the distribution of the tests, the size of the work space, the number of tests to sort, and so on, it may be far more efficient to break down the problem into pieces. Rather than doing an insertion sort right off the bat, it might first make sense to divide the stack of papers in half, and then do an insertion sort on one half, an insertion sort on the second half, and then a merge of the two piles. Or perhaps it would make sense to divide the tests into four piles, or eight piles. (This approach is referred to as merge sort.)

With a recursive solution you will always have the following two pieces:

The base case - what to do when you're done recursing. After dividing the tests into separate piles of say, eight elements per pile, the base case is to sort these piles via insertion sort.
The recursive step - the action to perform that involves plugging the input "back into" the system. For merge sort, the recursive step is the division of one pile into two. Then into four. Then into eight, and so on, until the base case is reached.

For more on recursion, see Recursion, Why It's Cool.

Returning to CTEs... the Employees database table holds the corporate hierarchy within its rows. Imagine that good ol' Andrew Fuller has come back and insisted on a report that would list all persons in the company along with their position in the hierarchy. Since the Employees table can capture an arbitrary number of hierarchy levels, we need a recursive solution. Enter CTEs...

Like any recursive definition, a recursive Common Table Expression requires both a base case and the recursive step. In SQL parlance, this translates into two SQL queries - one that gets the "initial" data UNIONed with one that performs the recursion. For the Employees example, the base case is returning those employees that have no manager:

 SELECT ...
FROM Employees
WHERE ReportsTo IS NULL

The recursion includes a query on the CTE itself. The following shows the CTE - with both the base case and recursive step - along with a SELECT query that returns the rows from the CTE:

 WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, ReportsTo, HierarchyLevel) AS
(
   -- Base case
   SELECT
      EmployeeID,
      LastName,
      FirstName,
      ReportsTo,
      1 as HierarchyLevel
   FROM Employees
   WHERE ReportsTo IS NULL

   UNION ALL

   -- Recursive step
   SELECT
      e.EmployeeID,
      e.LastName,
      e.FirstName,
      e.ReportsTo,
      eh.HierarchyLevel + 1 AS HierarchyLevel
   FROM Employees e
      INNER JOIN EmployeeHierarchy eh ON
         e.ReportsTo = eh.EmployeeID
)

SELECT *
FROM EmployeeHierarchy
ORDER BY HierarchyLevel, LastName, FirstName

The recursion occurs in the second query in the CTE by joining the results of Employees against the CTE itself (EmployeeHierarchy) where the employees' ReportsTo field matches up to the CTE's EmployeeID. Included in this query is the HierarchyLevel field, which returns 1 for the base case and one greater than the previous level for each recursive step down the hierarchy. As requested, this resultset clearly shows that Mr. Fuller is the alpha male in this organization. Furthermore, we can see that Steven, Laura, Nancy, Janet, and Margaret make up the second tier in the organizational hierarchy, while poor Anne, Robert, and Michael are down at the bottom:

Alternatives to Recursive Common Table Expressions
As we saw in this article, enumerating hierarchical data recursively can be accomplished via CTEs (for more on using recursive CTEs, don't forget to check out the official documentation - Recursive Queries Using Common Table Expressions). However, there are other options as well. One choice is to perform the recursion at the ASP/ASP.NET layer. That is, read in all employee information to a Recordset of DataSet in code, and then recurse there. My article Efficiently DisplayingParent-Child Data discusses this approach. If you need to perform the recursion in SQL, you can use recursive stored procedures, as discussed in The Zen of Recursion. If you are designing a data model that needs to support hierarchical data, your best bet is to bake in some lineage information directly into the table from the get-go. See SQL for Threaded Discussions and More Trees & Hierarchies in SQL for more information.

Alternatives to Recursive Common Table Expressions

As we saw in this article, enumerating hierarchical data recursively can be accomplished via CTEs (for more on using recursive CTEs, don't forget to check out the official documentation - Recursive Queries Using Common Table Expressions). However, there are other options as well. One choice is to perform the recursion at the ASP/ASP.NET layer. That is, read in all employee information to a Recordset of DataSet in code, and then recurse there. My article Efficiently DisplayingParent-Child Data discusses this approach.

If you need to perform the recursion in SQL, you can use recursive stored procedures, as discussed in The Zen of Recursion. If you are designing a data model that needs to support hierarchical data, your best bet is to bake in some lineage information directly into the table from the get-go. See SQL for Threaded Discussions and More Trees & Hierarchies in SQL for more information.

Conclusion
One of the many new features in SQL Server 2005 are Common Table Expressions (CTEs), which provide a more readable and usable approach to derived tables. Additionally, CTEs may be recursively defined, allowing a recursive entity to be enumerated without the need for recursive stored procedures. For more on the new features found in SQL Server 2005, be sure to also check out Returning Ranked Results with Microsoft SQL Server 2005 and TRY...CATCH in SQL Server 2005.

To Search all columns of all tables for a given search string

2009-05-09T13:29:00.000-07:00

CREATE PROC SearchAllTables

(

@SearchStr nvarchar(100)

)

BEGIN

CREATE TABLE #Results

(

ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName = ''

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN

SET @ColumnName = ''

SET @TableName =

(

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped') = 0

)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN

SET @ColumnName =

(

SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA =

PARSENAME(@TableName, 2)

AND TABLE_NAME = PARSENAME(@TableName, 1)

AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

AND QUOTENAME(COLUMN_NAME) > @ColumnName

)

IF @ColumnName IS NOT NULL

BEGIN

INSERT INTO #Results EXEC

(

'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

)

END

SELECT ColumnName, ColumnValue FROM #Results

END

So execute the final script as mentioned below where 'testone' is the string which has to be searched through

EXEC SearchAllTables '%testone%'

To search all tables to find a column or field name in a database

2009-05-09T12:34:00.000-07:00

---------------------------

In this query it displays LIST OF TABLES ALONG WITH TABLE COUNT in a DATABASE

SELECT so.[name] as [table name],rowcnt as ROWS,

CASE WHEN si.indid between 1 and 254

THEN si.[name] ELSE NULL END AS [Index Name] ,

si.indid FROM sysindexes si INNER JOIN sysobjects so ON

si.id = so.id WHERE si.indid < type =" 'U'">

AND so.[name] != 'dtproperties'

ORDER BY so.[name]

ROW Count for LIST OF TABLES IN A DATABASE

2009-05-09T12:19:00.000-07:00

Sometimes there is a need to get record counts from every table in your database. One way of doing this is to do a SELECT count(*) on all of your tables, but this could create a lot of overhead especially for large databases and large tables. If you don't require an exact answer, it isn't necessary to use a SELECT count(*) query on the rows in a table to get the row count.

SELECT so.[name] as [table name],
CASE WHEN si.indid between 1 and 254
THEN si.[name] ELSE NULL END
AS [Index Name] , si.indid
FROM sysindexes si
INNER JOIN sysobjects so ON si.id = so.id
WHERE si.indid < 2
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]

----------------------------------
Just the table name and no of rows

SELECT so.[name] as [table name],rowcnt as ROWS
FROM sysindexes si
INNER JOIN sysobjects so
ON si.id = so.id
WHERE si.indid < 2
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]

----------------------------------
ON A LINKEDSERVER

SELECT so.[name] as [table name],rowcnt as ROWS
FROM [SERVERNAME].
[DATABASENAME].sys.sysindexes si
INNER JOIN
[SERVERNAME].
[DATABASENAME].sys.sysobjects so
ON si.id = so.id
WHERE si.indid < 2
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]

-----------------------------------

T-SQL Snippets, Codes,Tweaks

2009-05-09T10:27:00.000-07:00

Operators Allowed in the WHERE Clause
= Equal
<> Not equal
> Greater than
<>= Greater than or equal
<= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern IN If you know the exact value you want to return for at least one of the columns

------------------------------

Taking a backup of a table,
The SQL SELECT INTO statement can be used to create backup copies of tables.

SELECT *INTO new_table_name [IN externaldatabase] FROM old_tablename

SELECT column_name(s)INTO new_table_name [IN externaldatabase]

FROM old_tablename

or

Here it creates the table name DEF and copies the content accordingly
SELECT COLA,COLB,COLC,COLD,COLE,COLF INTO DEF FROM ABC

SELECT * INTO DEF FROM ABC

We can also use the IN clause to copy the table into another database:

SELECT * INTO Persons_Backup IN 'Backup.mdb' FROM Persons

We can also copy only a few fields into the new table:

SELECT LastName,FirstName INTO Persons_Backup FROM Persons

SQL SELECT INTO - With a WHERE ClauseWe can also add a WHERE clause.

The following SQL statement creates a "Persons_Backup" table with only the persons who lives in the city "Sandnes":

SELECT LastName,Firstname INTO Persons_Backup FROM Persons WHERE

City='Sandnes'

SQL SELECT INTO - Joined TablesSelecting data from more than one table is also possible.
The following example creates a "Persons_Order_Backup" table contains data from the two tables "Persons" and "Orders":

SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id

------------------------------

Exporting the output into a text or csv file:

Syntax:
sqlcmd -S SERVERNAME -i C:\1.sql -o C:\hello.txt or [C:\hello.csv]

------------------------------

Executing select statements in bcp

The below code, iam copying the output into a directory with , delimited values
bcp "select * from [DATABASENAME]..[TABLENAME]" queryout C:\Output.csv -S SERVERNAME -c -t',' -T

NOTE: The two .. are must.

------------------------------

Filter Out unmatching records between two tables

SELECT COLA FROM ABC A WHERE NOT EXISTS (SELECT * FROM DEF D WHERE (A.COLA=D.ABC))

SELECT COLA FROM DEF D WHERE NOT EXISTS (SELECT * FROM ABC A WHERE (D.COLA=A.ABC))

SELECT A.COLA from ABC A where A.COLA NOT IN (select D.COLA from DEF D)

------------------------------

Removing 0(zeros) from the begining of a particular number COLA is a column name and ABC is the table name

select REPLACE(LTRIM(REPLACE(COLA, '0', ' ')), ' ', '0') from ABC

Select Replace(T.Col1,”@”, ‘at’) As [ColName] From MyTable T

------------------------------

Retrieve MSSQL table information

select * from information_schema.columnswhere table_name= 'ABC'

------------------------------

To Query the list of columns in a table

select column_name 'Column Name', data_type 'Data Type', character_maximum_length 'Maximum Length' from information_schema.columns where table_name = 'master'

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.ColumnsWHERE TABLE_NAME = 'master'

------------------------------

To find the name of the table in which a particular column name exists.

Here it checks for the database for the tables which have the column name as COLA

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'ABC' )

if you're unsure exactly what the column is named, but you suspect you know part of the name, then try...

SELECT name FROM sysobjects WHERE id IN(SELECT id FROM syscolumns WHERE name like '%COLA%')

------------------------------

To query the stored procedures in a table

select object_name(id) as objectname from syscommentswhere text like '%ABC%'

------------------------------

Finding a text in stored procedure

SELECT so.[name] AS 'storedProcedure' FROM sysobjects so JOIN syscomments sc ONso.[id] = sc.[id]WHERE so.[type] = 'P'AND sc.[text] LIKE '%action%'

------------------------------

checking the column if it exists and add

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ABC' AND COLUMN_NAME = 'COLA')

BEGIN

ALTER TABLE ABC ADD COLA int

END

------------------------------

Renaming a column name

EXEC sp_rename 'TABLENAME.OLD_COLUM

NNAME', 'NEW_COLUMNAME', 'COLUMN'

------------------------------

To get the columns names

ORDER BY sysobjects.name,syscolumns.colid

SELECT table_name=sysobjects.name,column_name=syscolumns.name,

datatype=systypes.name,length=syscolumns.length

FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes

ON syscolumns.xtype=systypes.xtype WHERE syscolumns.name='COLA'

------------------------------

To split a column into two using a SELECT statement based on a condition

SELECT CASE WHEN STATE = 'VA'

THEN STATE ELSE '' END as 'Higher income group',

CASE WHEN STATE = 'NY'

THEN STATE ELSE '' END as 'Lower income group',NUMBER FROM ABC

------------------------------

case query

SELECT NUMBER, STATE, priority = ( CASE STATE when 'WA' then 1 when 'VA' then 2 when 'NY' then 3 when 'CA' then 4 end) FROM ABC ORDER BY state

------------------------------

To find out the user name at SQL prompt.

Select user_name()

------------------------------

If the table exists in the database

select name from sysobjects where xtype='u' and name = 'master'

------------------------------

Determining Your Session IDs for SQL Serversession id

SELECT @@SPID

------------------------------

No of tables count in a database

SELECT COUNT(*) from information_schema.tables WHERE table_type = 'base table'

------------------------------

list of table names

SELECT TABLE_NAME from information_schema.tables WHERE table_type = 'base table' order by table_name

------------------------------

Returns list of tables that have no primary key

select name from sysobjects where id not in (select b.id from sysconstraints b, sysobjects c where c.type = 'K' and c.id = b.constid) and type = 'U' order by name

------------------------------

SQL Query to Update a Column Value in All Tables

SELECT 'UPDATE ',RTRIM(Name),' SET UserID=2 WHERE UserID=1' FROM sysobjectsWHERE Type='U'

------------------------------

Displaying structure of a table

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'DATABASENAME' AND TABLE_NAME = 'ABC'

exec sp_columns ABC

------------------------------

To determine if a table exists in a SQL Server Database

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='tablename') SELECT 'tablename exists.' ELSE SELECT 'tablename does not exist.'

------------------------------

How do I find all the foreign keys in a database?

SELECT FK_Table = FK.TABLE_NAME,FK_Column = CU.COLUMN_NAME,

PK_Table = PK.TABLE_NAME,PK_Column = PT.COLUMN_NAME,

Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN (SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT ON PT.TABLE_NAME = PK.TABLE_NAME

PS:optional: ORDER BY 1,2,3,4

------------------------------

The statement takes two tables (ABC and DEF), and figures out which columns between the two are similar:

SELECT table_name=sysobjects.name,column_name=syscolumns.name, datatype=systypes.name,length=syscolumns.length FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes ON syscolumns.xtype=systypes.xtype WHERE (sysobjects.xtype='U' OR sysobjects.xtype='v') AND syscolumns.name IN (SELECT syscolumns.name FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes ON syscolumns.xtype=systypes.xtype WHERE sysobjects.name = 'ABC') AND sysobjects.name = 'DEF'

------------------------------

How to find a text inside SQL Server procedures / triggers?

SELECT DISTINCT LEFT(so.name, 100) AS Object_Name,"object_type"= left(case so.type when 'U' then 'Table - User'

when 'S' then 'Table - System'

when 'V' then 'Table - View'

when 'TR' then 'Trigger'

when 'P' then 'Stored Procedure'

when 'C' then 'Constraint - Check'

when 'D' then 'Default'

when 'K' then 'Key - Primary'

when 'F' then 'Key - Foreign'

when 'L' then 'Log'

when 'R' then 'Rule'

when 'RF' then 'Replication Filter stp'

else '<>'

end -- case so.type ,25)

FROM syscomments sc

INNER JOIN sysobjects so

ON so.id = sc.id WHERE text Like '%empname%' ORDER BY 2,1

------------------------------

GETTING VALUES ROW BY ROW

DECLARE @cursor CURSOR
SET @cursor=CURSOR FOR SELECT * FROM abc
OPEN @cursorFETCH @cursor
WHILE (@@FETCH_STATUS=0) BEGIN FETCH @cursorEND
CLOSE @cursorDEALLOCATE @cursor

------------------------------

NO OF WAITING TASKS

SELECT COUNT(*) AS 'Number of waiting tasks'FROM sys.dm_os_waiting_tasks

------------------------------

Number of threads used with MSSQL 2005

SELECT COUNT(*) AS 'Number of threads'FROM sys.dm_os_waiting_tasks

------------------------------

Type of waiting tasks with MSSQL 2005

SELECT CAST(wait_type AS VARCHAR(30)) AS 'Waiting task',COUNT (*) AS 'Number of waiting tasks 'FROM sys.dm_os_waiting_tasks GROUP BY wait_type ORDER BY 'Number of waiting tasks' DESC WHERE wait_type <> 'THREADPOOL'

------------------------------

Resource Bottlenecks with MSSQL 2005

SELECT resource_address AS 'Resource Bottleneck',COUNT (*) AS '# of bottlenecks' FROM sys.dm_os_waiting_tasks WHERE resource_address <> 0 GROUP BY resource_address ORDER BY '# of bottlenecks' DESC

------------------------------

IO Bottlenecks with MSSQL 2005

SELECT waiting_task_address AS 'Task address', session_id AS 'Session', exec_context_id AS 'Context', wait_duration_ms AS 'Wait in millsec',CAST(wait_type AS VARCHAR(30)) AS 'Type', resource_address AS 'Resource address', blocking_task_address AS 'Blocking task', blocking_session_id AS 'Blocking session',CAST(resource_description AS VARCHAR(30)) AS 'Resource' FROM sys.dm_os_waiting_tasks WHERE wait_duration_ms > 20 AND wait_type LIKE '%PAGEIOLATCH%'

------------------------------

Has a row been changed

SELECT UNIQ_ID,CD_TYPE,CODE1,CHECKSUM(UNIQ_ID,CD_TYPE,CODE1) AS 'Checksum specific',CHECKSUM(*) AS 'Checksum general' FROM INFO

------------------------------

Create a comma delimited list (csv) of unique values

DECLARE @uniquelist VARCHAR(1000);SELECT @uniquelist = LTRIM(COALESCE(@uniquelist+',' , ' ')) + CAST(UNIQ_ID AS VARCHAR(10))FROM (SELECT DISTINCT UNIQ_ID FROM INFO) ABC SELECT @uniquelist AS 'Unique emp_short list: comma delimited'

------------------------------

Incrementing counters that already contain NULLs

SELECT counter,CASE WHEN counter IS null THEN (row_number() OVER (ORDER BY emp_short,trans_date) +(SELECT MAX(trmax.counter) FROM trans02 trmax ) + 10 ) ELSE counter END AS 'Calculated counter', emp_short,trans_date,debit,credit FROM trans02ORDER BY 'Calculated counter'

------------------------------

Adding a link server

EXEC master.dbo.sp_addlinkedserver @server = N'SERVERNAME', @srvproduct=N'SQL Server'

------------------------------

Copying the table structure without copying the data from another table

SELECT * INTO NewTable FROM abc WHERE 1=2

------------------------------

Renaming the table name and column name

EXEC sp_rename 'OldTableName', 'NewTableName'

------------------------------

Renaming Column name Syntax

EXEC sp_rename @objname = 'TableName.OldColumnName', @newname = 'NewColumnName', @objtype = 'COLUMN'

Example : XYZ and ZZZ and DEF are table namesexec sp_rename 'XYZ' , 'ZZZ' - Renaming XYZ table name to ZZZexec sp_rename 'DEF.[B]', 'GROUP' 'COLUMN' - Renaming Column B of table DEF to , GROUP

------------------------------

This script will gives row number for every table in database.
select OBJECT_NAME(object_id) TableName,st.row_countfrom sys.dm_db_partition_stats st where index_id<2>

------------------------------

Obtaining CLR Execution performance counter values.

SELECT object_name, counter_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%CLR%'

------------------------------

Query performance and time spent in SQL CLR.

SELECT (SELECT text FROM sys.dm_exec_sql_text(qs.sql_handle)) AS query_text, qs.*FROM sys.dm_exec_query_stats AS qs WHERE qs.total_clr_time > 0 ORDER BY qs.total_clr_time desc

------------------------------

Requests that are currently in SQL CLR

SELECT session_id, request_id, start_time, status, command, database_id,
wait_type, wait_time, last_wait_type, wait_resource, cpu_time,
total_elapsed_time, nest_level, executing_managed_code
FROM sys.dm_exec_requests WHERE executing_managed_code = 1

------------------------------

SQL CLR Wait Statistics

SELECT ws.* FROM sys.dm_os_wait_stats AS wsWHERE ws.wait_type LIKE '%clr%'

------------------------------

User-defined Types

User Defined Type Metadata

Includes base assembly information

SELECT st.[name] AS [Type Name] , st.max_length, st.[precision], st.scale, st.collation_name, st.is_nullable, sa.create_date, sa.[name] AS [Assembly Name], sa.permission_set_desc AS [Access]FROM sys.types AS st INNER JOIN sys.type_assembly_usages AS tau ON st.user_type_id = tau.user_type_id INNER JOIN sys.assemblies AS sa ON tau.assembly_id = sa.assembly_id

------------------------------

LIST CLR AGGREGATE FUNCTIONS

SELECT schema_name(so.schema_id) + N'.' + so.[name] AS [Name] , so.create_date, so.modify_date, sa.permission_set_desc AS [Access]FROM sys.objects AS so INNER JOIN sys.module_assembly_usages AS mau ON so.object_id = mau.object_id INNER JOIN sys.assemblies AS sa ON mau.assembly_id = sa.assembly_idWHERE so.type_desc = N'AGGREGATE_FUNCTION'

------------------------------

LIST CLR TABLE FUNCTIONS

SELECT schema_name(so.schema_id) + N'.' + so.[name] AS [Name], so.create_date, so.modify_date, sa.permission_set_desc AS [Access] FROM sys.objects AS so INNER JOIN sys.module_assembly_usages AS sau ON so.object_id = sau.object_id INNER JOIN sys.assemblies AS sa ON sau.assembly_id = sa.assembly_id WHERE so.type_desc = N'CLR_TABLE_VALUED_FUNCTION'

------------------------------

LIST CLR FUNCTIONS:

SELECT schema_name(so.schema_id) + N'.' + so.[name] AS [Name], so.create_date, so.modify_date, sa.permission_set_desc AS [Access] FROM sys.objects AS so INNER JOIN sys.module_assembly_usages AS sau ON so.object_id = sau.object_id INNER JOIN sys.assemblies AS sa ON sau.assembly_id = sa.assembly_id WHERE so.type_desc = N'CLR_SCALAR_FUNCTION'

------------------------------

Substring function

Select SUBSTRING(COLA,5,10) as NAME,SUBSTRING(COLA,152,2 )as CUSTNAME from ABC

------------------------------

Getting all the rows in one column

declare @res varchar(4000)

set @res =''

select @res = @res + A + ',' from testing

if @@rowcount > 0 select substring(@res, 1, len(@res)-1)
NOTE:Testing is the table name and 'A' is the column name

------------------------------

List of databases attached

SELECT dtb.name AS [Name] FROM master.dbo.sysdatabases AS dtb ORDER BY [Name] ASC

------------------------------

Adding strings to the output

select (CHAR('39')+COLA+CHAR('39')++CHAR('44')) from ABC

------------------------------

Generating a script using the existing table structure

SELECT CASE WHEN colid = 1 THEN 'CREATE TABLE ' + O.name + ' (' ELSE '' END+ C.name + ' ' + CASE C.xtype WHEN 167 THEN 'varchar(' + CONVERT(varchar(20), C.length) + ') 'WHEN 61 THEN 'datetime'-- ...ELSE 'UnknownType_' + CONVERT(varchar(20), C.xtype)END + ' '+ CASE WHEN C.isnullable = 1THEN 'NULL' ELSE 'NOT NULL' END+ CASE WHEN colid =(SELECT MAX(colid) FROM dbo.syscolumns C2 WHERE C2.id = O.id) THEN ')' ELSE ',' ENDFROM dbo.sysobjects O JOIN dbo.syscolumns C ON C.id = O.id WHERE O.name= 'info' AND O.type = 'U'ORDER BY colid

SSAS - SQL Server 2005 Analysis Services

2009-04-24T16:59:00.000-07:00

Analysis Services

Files needed:

AdventureWorksCube1.zip
AdventureWorksCube2.zip

So far in this course you’ve focused on getting data into a SQL Server database and then later getting the same data out of the database. You’ve seen how to create tables, insert data, and use SQL statements, views, and stored procedures to retrieve the data. This pattern of activity, where individual actions deal with small pieces of the database, is sometimes called online transaction processing, or OLTP.

But there’s another use for databases, especially large databases. Suppose you run an online book store and have sales records for 50 million book sales. Maybe books on introductory biology show a strong spike in sales every September. That’s a fact that you could use to your advantage in ordering stock, if only you knew about it.

Searching for patterns like this and summarizing them is called online analytical processing, or OLAP. Microsoft SQL Server 2005 includes a separate program called Microsoft SQL Server 2005 Analysis Services to perform OLAP analysis. In this chapter you’ll learn the basics of setting up and using Analysis Services.

SSAS Tutorial: Understanding Analysis Services

The basic idea of OLAP is fairly simple. Let’s think about that book ordering data for a moment. Suppose you want to know how many people ordered a particular book during each month of the year. You could write a fairly simple query to get the information you want. The catch is that it might take a long time for SQL Server to churn through that many rows of data.

And what if the data was not all in a single SQL Server table, but scattered around in various databases throughout your organization? The customer info, for example, might be in an Oracle database, and supplier information in a legacy xBase database. SQL Server can handle distributed heterogeneous queries, but they’re slower.

What if, after seeing the monthly numbers, you wanted to drill down to weekly or daily numbers? That would be even more time -consuming and require writing even more queries.

This is where OLAP comes in. The basic idea is to trade off increased storage space now for speed of querying later. OLAP does this by precalculating and storing aggregates. When you identify the data that you want to store in an OLAP database, Analysis Services analyzes it in advance and figures out those daily, weekly, and monthly numbers and stores them away (and stores many other aggregations at the same time). This takes up plenty of disk space, but it means that when you want to explore the data you can do so quickly.

Later in the chapter, you’ll see how you can use Analysis Services to extract summary information from your data. First, though, you need to familiarize yourself with a new vocabulary. The basic concepts of OLAP include:

Cube
Dimension table
Dimension
Level
Fact table
Measure
Schema

Cube

The basic unit of storage and analysis in Analysis Services is the cube. A cube is a collection of data that’s been aggregated to allow queries to return data quickly. For example, a cube of order data might be aggregated by time period and by title, making the cube fast when you ask questions concerning orders by week or orders by title.

Cubes are ordered into dimensions and measures. Dimensions come from dimension tables, while measures come from fact tables.

Dimension table

A dimension table contains hierarchical data by which you’d like to summarize. Examples would be an Orders table, that you might group by year, month, week, and day of receipt, or a Books table that you might want to group by genre and title.

Dimension

Each cube has one or more dimensions, each based on one or more dimension tables. A dimension represents a category for analyzing business data: time or category in the examples above. Typically, a dimension has a natural hierarchy so that lower results can be "rolled up" into higher results. For example, in a geographical level you might have city totals aggregated into state totals, or state totals into country totals.

Level

Each type of summary that can be retrieved from a single dimension is called a level. For example, you can speak of a week level or a month level in a time dimension.

Fact table

A fact table contains the basic information that you wish to summarize. This might be order detail information, payroll records, drug effectiveness information, or anything else that’s amenable to summing and averaging. Any table that you’ve used with a Sum or Avg function in a totals query is a good bet to be a fact table.

Measure

Every cube will contain one or more measures, each based on a column in a fact table that you’d like to analyze. In the cube of book order information, for example, the measures would be things such as unit sales and profit.

Schema

Fact tables and dimension tables are related, which is hardly surprising, given that you use the dimension tables to group information from the fact table. The relations within a cube form a schema. There are two basic OLAP schemas: star and snowflake. In a star schema, every dimension table is related directly to the fact table. In a snowflake schema, some dimension tables are related indirectly to the fact table. For example, if your cube includes OrderDetails as a fact table, with Customers and Orders as dimension tables, and Customers is related to Orders, which in turn is related to OrderDetails, then you’re dealing with a snowflake schema.

There are additional schema types besides the star and snowflake schemas, including parent-child schemas and data-mining schemas. However, the star and snowflake schemas are the most common types in normal cubes.

SSAS Tutorial: Introducing Business

Intelligence Development Studio

Business Intelligence Development Studio (BIDS) is a new tool in SQL Server 2005 that you can use for analyzing SQL Server data in various ways. You can build three different types of solutions with BIDS:

Analysis Services projects
Integration Services projects
Reporting Services projects

To launch Business Intelligence Development Studio, select Microsoft SQL Server 2005 > SQL Server Business Intelligence Development Studio from the Programs menu. BIDS shares the Visual Studio shell, so if you have Visual Studio installed on your computer, this menu item will launch Visual Studio complete with all of the Visual Studio project types (such as Visual Basic and C# projects).

SSAS Tutorial: Creating a Data Cube

To build a new data cube using BIDS, you need to perform these steps:

Create a new Analysis Services project
Define a data source
Define a data source view
Invoke the Cube Wizard

We’ll look at each of these steps in turn.

You’ll need to have the AdventureWorksDW sample database installed to complete the examples in this chapter. This database is one of the samples that’s available with SQL Server.

Creating a New Analysis Services Project

To create a new Analysis Services project, you use the New Project dialog box in BIDS. This is very similar to creating any other type of new project in Visual Studio.

Try It!

To create a new Analysis Services project, follow these steps:

Select Microsoft SQL Server 2005 > SQL Server Business Intelligence Development Studio from the Programs menu to launch Business Intelligence Development Studio.
Select File > New > Project.
In the New Project dialog box, select the Business Intelligence Projects project type.
Select the Analysis Services Project template.
Name the new project AdventureWorksCube1 and select a convenient location to save it.
Click OK to create the new project.

Figure 15-1 shows the Solution Explorer window of the new project, ready to be populated with objects.

Figure 15-1: New Analysis Services project

Defining a Data Source

To define a data source, you’ll use the Data Source Wizard. You can launch this wizard by right-clicking on the Data Sources folder in your new Analysis Services project. The wizard will walk you through the process of defining a data source for your cube, including choosing a connection and specifying security credentials to be used to connect to the data source.

Try It!

To define a data source for the new cube, follow these steps:

Right-click on the Data Sources folder in Solution Explorer and select New Data Source.
Read the first page of the Data Source Wizard and click Next.
You can base a data source on a new or an existing connection. Because you don’t have any existing connections, click New.
In the Connection Manager dialog box, select the server containing your analysis services sample database from the Server Name combo box.
Fill in your authentication information.
Select the Native OLE DB\SQL Native Client provider (this is the default provider).
Select the AdventureWorksDW database. Figure 15-2 shows the filled-in Connection Manager dialog box.

Figure 15-2: Setting up a connection

Click OK to dismiss the Connection Manager dialog box.
Click Next.
Select Default impersonation information to use the credentials you just supplied for the connection and click Next.
Accept the default data source name and click Finish.

Defining a Data Source View

A data source view is a persistent set of tables from a data source that supply the data for a particular cube. BIDS also includes a wizard for creating data source views, which you can invoke by right-clicking on the Data Source Views folder in Solution Explorer.

Try It!

To create a new data source view, follow these steps:

Right-click on the Data Source Views folder in Solution Explorer and select New Data Source View.
Read the first page of the Data Source View Wizard and click Next.
Select the Adventure Works DW data source and click Next. Note that you could also launch the Data Source Wizard from here by clicking New Data Source.
Select the dbo.FactFinance table in the Available Objects list and click the > button to move it to the Included Object list. This will be the fact table in the new cube.
Click the Add Related Tables button to automatically add all of the tables that are directly related to the dbo.FactFinance table. These will be the dimension tables for the new cube. Figure 15-3 shows the wizard with all of the tables selected.

Figure 15-3: Selecting tables for the data source view

Click Next.
Name the new view Finance and click Finish. BIDS will automatically display the schema of the new data source view, as shown in Figure 15-4.

Figure 15-4: The Finance data source view

Invoking the Cube Wizard

As you can probably guess at this point, you invoke the Cube Wizard by right-clicking on the Cubes folder in Solution Explorer. The Cube Wizard interactively explores the structure of your data source view to identify the dimensions, levels, and measures in your cube.

Try It!

To create the new cube, follow these steps:

Right-click on the Cubes folder in Solution Explorer and select New Cube.
Read the first page of the Cube Wizard and click Next.
Select the option to build the cube using a data source.
Check the Auto Build checkbox.
Select the option to create attributes and hierarchies.
Click Next.
Select the Finance data source view and click Next.
Wait for the Cube Wizard to analyze the data and then click Next.
The Wizard will get most of the analysis right, but you can fine-tune it a bit. Select DimTime in the Time Dimension combo box. Uncheck the Fact checkbox on the line for the dbo.DimTime table. This will allow you to analyze this dimension using standard time periods.
Click Next.
On the Select Time Periods page, use the combo boxes to match time property names to time columns according to Table 15-1.

Time Property Name	Time Column
Year	CalendarYear
Quarter	CalendarQuarter
Month	MonthNumberOfYear
Day of Week	DayNumberOfWeek
Day of Month	DayNumberOfMonth
Day of Year	DayNumberOfYear
Week of Year	WeekNumberOfYear
Fiscal Quarter	FiscalQuarter
Fiscal Year	FiscalYear

Table 15-1: Time columns for Finance cube

Click Next.
Accept the default measures and click Next.
Wait for the Cube Wizard to detect hierarchies and then click Next.
Accept the default dimension structure and click Next.
Name the new cube FinanceCube and click Finish.

Deploying and Processing a Cube

At this point, you’ve defined the structure of the new cube - but there’s still more work to be done. You still need to deploy this structure to an Analysis Services server and then process the cube to create the aggregates that make querying fast and easy.

To deploy the cube you just created, select Build > Deploy AdventureWorksCube1. This will deploy the cube to your local Analysis Server, and also process the cube, building the aggregates for you. BIDS will open the Deployment Progress window, as shown in Figure 15-5, to keep you informed during deployment and processing.

Figure 15-5: Deploying a cube

One of the tradeoffs of cubes is that SQL Server does not attempt to keep your OLAP cube data synchronized with the OLTP data that serves as its source. As you add, remove, and update rows in the underlying OLTP database, the cube will get out of date. To update the cube, you can select Cube > Process in BIDS. You can also automate cube updates using SQL Server Integration Services, which you’ll learn about in Chapter 16.

SSAS Tutorial: Exploring a Data Cube

At last you’re ready to see what all the work was for. BIDS includes a built-in Cube Browser that lets you interactively explore the data in any cube that has been deployed and processed. To open the Cube Browser, right-click on the cube in Solution Explorer and select Browse. Figure 15-6 shows the default state of the Cube Browser after it’s just been opened.

Figure 15-6: The cube browser in BIDS

The Cube Browser is a drag-and-drop environment. If you’ve worked with pivot tables in Microsoft Excel, you should have no trouble using the Cube browser. The pane to the left includes all of the measures and dimensions in your cube, and the pane to the right gives you drop targets for these measures and dimensions. Among other operations, you can:

Drop a measure in the Totals/Detail area to see the aggregated data for that measure.
Drop a dimension or level in the Row Fields area to summarize by that level or dimension on rows.
Drop a dimension or level in the Column Fields area to summarize by that level or dimension on columns
Drop a dimension or level in the Filter Fields area to enable filtering by members of that dimension or level.
Use the controls at the top of the report area to select additional filtering expressions.

In fact, if you’ve worked with pivot tables in Excel, you’ll find that the Cube Browser works exactly the same, because it uses the Microsoft Office PivotTable 11.0 control as its basis.

Try It!

To see the data in the cube you just created, follow these steps:

Right-click on the cube in Solution Explorer and select Browse.
Expand the Measures node in the metadata panel (the area at the left of the user interface).
Expand the Fact Finance node.
Drag the Amount measure and drop it on the Totals/Detail area.
Expand the Dim Account node in the metadata panel.
Drag the Account Description property and drop it on the Row Fields area.
Expand the Dim Time node in the metadata panel.
Drag the Calendar Year-Calendar Quarter-Month Number of Year hierarchy and drop it on the Column Fields area.
Click the + sign next to year 2001 and then the + sign next to quarter 3.
Expand the Dim Scenario node in the metadata panel.
Drag the Scenario Name property and drop it on the Filter Fields area.
Click the dropdown arrow next to scenario name. Uncheck all of the checkboxes except for the one next to the Budget name.

Figure 15-7 shows the result. The Cube Browser displays month-by-month budgets by account for the third quarter of 2001. Although you could have written queries to extract this information from the original source data, it’s much easier to let Analysis Services do the heavy lifting for you.

Figure 15-7: Exploring cube data in the cube browser

SSAS Tutorial: Exercises

Create a data cube, based on the data in the AdventureWorksDW sample database, to answer the following question: what were the internet sales by country and product name for married customers only?

Solutions to Exercises

To create the cube, follow these steps:

Select Microsoft SQL Server 2005 > SQL Server Business Intelligence Development Studio from the Programs menu to launch Business Intelligence Development Studio.
Select File > New > Project.
In the New Project dialog box, select the Business Intelligence Projects project type.
Select the Analysis Services Project template.
Name the new project AdventureWorksCube2 and select a convenient location to save it.
Click OK to create the new project.
Right-click on the Data Sources folder in Solution Explorer and select New Data Source.
Read the first page of the Data Source Wizard and click Next.
Select the existing connection to the AdventureWorksDW database and click Next.
Select Default and click Next.
Accept the default data source name and click Finish.
Right-click on the Data Source Views folder in Solution Explorer and select New Data Source View.
Read the first page of the Data Source View Wizard and click Next.
Select the Adventure Works DW data source and click Next.
Select the dbo.FactInternetSales table in the Available Objects list and click the > button to move it to the Included Object list.
Click the Add Related Tables button to automatically add all of the tables that are directly related to the dbo.FactInternetSales table.
Click Next.
Name the new view InternetSales and click Finish.
Right-click on the Cubes folder in Solution Explorer and select New Cube.
Read the first page of the Cube Wizard and click Next.
Select the option to build the cube using a data source.
Check the Auto Build checkbox.
Select the option to create attributes and hierarchies.
Click Next.
Select the InternetSales data source view and click Next.
Wait for the Cube Wizard to analyze the data and then click Next.
Click Next.
Accept the default measures and click Next.
Wait for the Cube Wizard to detect hierarchies and then click Next.
Accept the default dimension structure and click Next.
Name the new cube InternetSalesCube and click Finish.
Select Build > Deploy AdventureWorksCube2.
Right-click on the cube in Solution Explorer and select Browse.
Expand the Measures node in the metadata panel.
Drag the Order Quantity and Sales Amount measures and drop it on the Totals/Detail area.
Expand the Dim Sales Territory node in the metadata panel.
Drag the Sales Territory Country property and drop it on the Row Fields area.
Expand the Dim Product node in the metadata panel.
Drag the English Product Name property and drop it on the Column Fields area.
Expand the Dim Customer node in the metadata panel.
Drag the Marital Status property and drop it on the Filter Fields area.
Click the dropdown arrow next to Marital Status. Uncheck the S checkbox.

Figure 15-8 shows the finished cube.

Figure 15-8: The AdventureWorksCube2 cube

Cursor Optimization Tips

2009-03-11T16:29:00.000-07:00

Try to avoid using SQL Server cursors, whenever possible.

SQL Server cursors can results in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations.

*****

Do not forget to close SQL Server cursor when its result set is not needed.

To close SQL Server cursor, you can use CLOSE {cursor_name} command. This command releases the cursor result set and frees any cursor locks held on the rows on which the cursor is positioned.

*****

Do not forget to deallocate SQL Server cursor when the data structures comprising the cursor are not needed.

To deallocate SQL Server cursor, you can use DEALLOCATE {cursor_name} command. This command removes a cursor reference and releases the data structures comprising the cursor.

*****

Try to reduce the number of records to process in the cursor.

To reduce the cursor result set, use the WHERE clause in the cursor's select statement. It can increase cursor performance and reduce SQL Server overhead.

*****

Try to reduce the number of columns to process in the cursor.

Include in the cursor's select statement only necessary columns. It will reduce the cursor result set. So, the cursor will use fewer resources. It can increase cursor performance and reduce SQL Server overhead.

*****

Use READ ONLY cursors, whenever possible, instead of updatable cursors.

Because using cursors can reduce concurrency and lead to unnecessary locking, try to use READ ONLY cursors, if you do not need to update cursor result set.

*****

Try avoid using insensitive, static and keyset cursors, whenever possible.

These types of cursor produce the largest amount of overhead on SQL Server, because they cause a temporary table to be created in TEMPDB, which results in some performance degradation.

*****

Use FAST_FORWARD cursors, whenever possible.

The FAST_FORWARD cursors produce the least amount of overhead on SQL Server, because there are read-only cursors and can only be scrolled from the first to the last row. Use FAST_FORWARD cursor if you do not need to update cursor result set and the FETCH NEXT will be the only used fetch option.

*****

Use FORWARD_ONLY cursors, if you need updatable cursor and the FETCH NEXT will be the only used fetch option.

If you need read-only cursor and the FETCH NEXT will be the only used fetch option, try to use FAST_FORWARD cursor instead of FORWARD_ONLY cursor. By the way, if one of the FAST_FORWARD or FORWARD_ONLY is specified the other cannot be specified.

*****

Bulk Copy Optimization Tips

2009-03-11T16:12:00.000-07:00

Use nonlogged bulk copy whenever possible.

The nonlogged bulk copy is much faster than logged one, but to use it you must provide all the following conditions:

1. The database option 'select into/bulkcopy' is set to true.
2. The target table is not being replicated.
3. The TABLOCK hint is specified.
4. The target table has no indexes, or if the table has indexes, it is empty when the bulk copy starts.

*****

Use native mode bulk copy whenever possible.

This can improve performance in comparison with the character mode.

*****

Try to use BULK INSERT command instead of bcp or DTS to load data into SQL Server.

The BULK INSERT command is much faster than bcp or the data pump to perform text file import operations, however, the BULK INSERT statement cannot bulk copy data from SQL Server to a data file.

*****

Use the bcp utility instead of DTS when you need to export data from the SQL Server table into a text file.

The bcp utility is much faster than DTS, so try to use it whenever possible.

*****

Specify the number of the first and the last row to bulk copy, if you need to bulk copy not all the rows from the specified data file.

This can results in good performance benefits, because the total amount of data copied will be less.

*****

Specify the number of rows per batch of data copied, if the transaction log was filled before the bulk copy is complete.

Because each batch is copied to the server as one transaction, SQL Server commits or rolls back the transaction for every batch. When you bulk copy large data files, the transaction log can be filled before the bulk copy is complete. In this case, enlarge the transaction log, allow it to grow automatically or specify the number of rows per batch of data copied.

*****

Try to increase the packet_size option.

The packet_size option specifies the number of bytes, per network packet, sent to and from the server. The packet_size can be from 4096 to 65535 bytes with the default of 4096. Increased packet size can enhance performance of bulk copy operations. Try to set the packet_size option to 8192 bytes and continue monitoring.

*****

Use the ORDER hint, if the clustered index exists on the table and the data file is sorted according to the clustered index.

This can significantly improve performance of the bulk copy operation, because SQL Server will load data in the clustered index order without any reorders operations.

*****

If you create a new table and bulk copy data into it, try to bulk load data first and only after that create any indexes.

This can significantly improve performance of the bulk copy operation, because data will be loaded into SQL Server table without any index pages creation during the bulk copy.

*****

If you load data into an empty table with the existing nonclustered indexes, try to drop the nonclustered indexes, bulk load data and only after that re-create the nonclustered indexes.

This can significantly improve performance of the bulk copy operation, because data will be loaded into SQL Server table without any index pages creation during the bulk copy.

*****

If you load data into a nonempty table with the existing clustered and/or nonclustered indexes, and the amount of data added is large, it can be faster to drop all indexes on the table, perform the bulk copy operation, and then re-create the indexes after the data is loaded.

Check the time needed to load data with dropping/re-creating indexes and without dropping/re-creating indexes on your test server before run bulk copy operation on the production server.

*****

If your SQL Server box has multiple CPUs, try to divide loaded data into two or more sources and run multiple instances of BCP on separate clients to load data in parallel.

Because SQL Server allows data to be bulk copied into a single table from multiple clients in parallel using the bcp utility or BULK INSERT statement, try to use parallel data loads whenever possible. To bulk copy data into SQL Server in parallel, you must provide all the following conditions:

1. The database option 'select into/bulkcopy' is set to true.
2. The TABLOCK hint is specified.
3. The target table does not have any indexes.

*****

Specify the TABLOCK hint, if you bulk copy data into an empty table from a single client.

This can improve performance of the bulk copy operation, because this causes a table-level lock to be taken for the duration of the bulk copy operation.

*****

Try to avoid using CHECK_CONSTRAINTS and FIRE_TRIGGERS hints.

Using these hints can significantly degrade performance of the bulk copy operation, because for each row loaded the constraints and insert triggers defined on the destination table will be executed.

Optimization for designing tables

2009-03-11T15:54:00.000-07:00

Normalize your tables to the third normal form.

A table is in third normal form (3NF) if it is in second normal form (2NF) and if it does not contain transitive dependencies. In most cases, you should normalize your tables to the third normal form. The normalization is used to reduce the total amount of redundant data in the database. The less data there is, the less work SQL Server has to perform, speeding its performance.

*****

Consider the denormalization of your tables from the forth or fifth normal forms to the third normal form.

Normalization to the forth and fifth normal forms can result in some performance degradation, especially when you need to perform many joins against several tables. It may be necessary to denormalize your tables to prevent performance degradation.

*****

Consider horizontal partitioning of the very large tables into the current and the archives versions.

The less space used, the smaller the table, the less work SQL Server has to perform to evaluate your queries. For example, if you need to query only data for the current year in your daily work, and you need all the data only once per month for the monthly report, you can create two tables: one with the current year's data and one with the old data.

*****

Create the table's columns as narrow as possible.

This can reduce the table's size and improve performance of your queries as well as some maintenance tasks (such as backup, restore and so on).

*****

Try to reduce the number of columns in a table.

The fewer the number of columns in a table, the less space the table will use, since more rows will fit on a single data page, and less I/O overhead will be required to access the table's data.

*****

Try to use constraints instead of triggers, rules, and defaults whenever possible.

Constraints are much more efficient than triggers and can boost performance. Constraints are more consistent and reliable in comparison to triggers, rules and defaults, because you can make errors when you write your own code to perform the same actions as the constraints.

*****

If you need to store integer data from 0 through 255, use tinyint data type.

The columns with tinyint data type use only one byte to store their values, in comparison with two bytes, four bytes and eight bytes used to store the columns with smallint, int and bigint data types accordingly. For example, if you design tables for a small company with 5-7 departments, you can create the departments table with the DepartmentID tinyint column to store the unique number of each department.

*****

If you need to store integer data from -32,768 through 32,767, use smallint data type.

The columns with smallint data type use only two bytes to store their values, in comparison with four bytes and eight bytes used to store the columns with int and bigint data types respectively. For example, if you design tables for a company with several hundred employees, you can create an employee table with the EmployeeID smallint column to store the unique number of each employee.

*****

If you need to store integer data from -2,147,483,648 through 2,147,483,647, use int data type.

The columns with int data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with bigint data types. For example, to design tables for a library with more than 32,767 books, create a books table with a BookID int column to store the unique number of each book.

*****

Use smallmoney data type instead of money data type, if you need to store monetary data values from 214,748.3648 through 214,748.3647.

The columns with smallmoney data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with money data types. For example, if you need to store the monthly employee payments, it might be possible to use a column with the smallmoney data type instead of money data type.

*****

Use smalldatetime data type instead of datetime data type, if you need to store the date and time data from January 1, 1900 through June 6, 2079, with accuracy to the minute.

The columns with smalldatetime data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with datetime data types. For example, if you need to store the employee's hire date, you can use column with the smalldatetime data type instead of datetime data type.

*****

Use varchar/nvarchar columns instead of text/ntext columns whenever possible.

Because SQL Server stores text/ntext columns on the Text/Image pages separately from the other data, stored on the Data pages, it can take more time to get the text/ntext values.

*****

Use char/varchar columns instead of nchar/nvarchar if you do not need to store unicode data.

The char/varchar value uses only one byte to store one character, the nchar/nvarchar value uses two bytes to store one character, so the char/varchar columns use two times less space to store data in comparison with nchar/nvarchar columns.

*****

Consider setting the 'text in row' SQL Server 2000 table's option.

The text, ntext, and image values are stored on the Text/Image pages, by default. This option specifies that small text, ntext, and image values will be placed on the Data pages with other data values in a data row. This can increase the speed of read and write operations and reduce the amount of space used to store small text, ntext, and image data values. You can set the 'text in row' table option by using the sp_tableoption stored procedure.

*****

If you work with SQL Server 2000, use cascading referential integrity constraints instead of triggers whenever possible.

For example, if you need to make cascading deletes or updates, specify the ON DELETE or ON UPDATE clause in the REFERENCES clause of the CREATE TABLE or ALTER TABLE statements. The cascading referential integrity constraints are much more efficient than triggers and can boost performance.

*****

Transact-SQL Optimization Tips

2009-03-11T15:45:00.000-07:00

Try to restrict the queries result set by using the WHERE clause.

This can results in good performance benefits, because SQL Server will return to client only particular rows, not all rows from the table(s). This can reduce network traffic and boost the overall performance of the query.

*****

Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns.

This can results in good performance benefits, because SQL Server will return to client only particular columns, not all table's columns. This can reduce network traffic and boost the overall performance of the query.

*****

Use views and stored procedures instead of heavy-duty queries.

This can reduce network traffic, because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of large heavy-duty queries text. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see.

*****

Try to avoid using SQL Server cursors, whenever possible.

SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations.

*****

If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement.

Because SELECT COUNT(*) statement make a full table scan to return the total table's row count, it can take very many time for the large table. There is another way to determine the total row count in a table. You can use sysindexes system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*): SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < style="text-align: center; font-weight: bold;">*****

Try to use constraints instead of triggers, whenever possible.

Constraints are much more efficient than triggers and can boost performance. So, you should use constraints instead of triggers, whenever possible.

*****

Use table variables instead of temporary tables.

Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. The table variables are available in SQL Server 2000 only.

*****

Try to avoid the HAVING clause, whenever possible.

The HAVING clause is used to restrict the result set returned by the GROUP BY clause. When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired aggregated groups. In many cases, you can write your select statement so, that it will contain only WHERE and GROUP BY clauses without HAVING clause. This can improve the performance of your query.

*****

Try to avoid using the DISTINCT clause, whenever possible.

Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is necessary.

*****

Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.

This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.

*****

Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.

This can improve performance of your queries, because the smaller result set will be returned. This can also reduce the traffic between the server and the clients.

*****

Use the FAST number_rows table hint if you need to quickly return 'number_rows' rows.

You can quickly get the n rows and can work with them, when the query continues execution and produces its full result set.

*****

Try to use UNION ALL statement instead of UNION, whenever possible.

The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist.

*****

Do not use optimizer hints in your queries.

Because SQL Server query optimizer is very clever, it is very unlikely that you can optimize your query by using optimizer hints, more often, this will hurt performance.

*****

MS SQL Server Shortcut keys

2009-03-04T19:57:00.001-08:00

Menu Activation Keyboard Shortcuts

Action	Standard	SQL Server 2000
Move to the SQL Server Management Studio menu bar	ALT	ALT
Activate the menu for a tool component	ALT+HYPHEN	ALT+HYPHEN
Display the context menu	SHIFT+F10	SHIFT+F10
Display the New File dialog box to create a file	CTRL+N	No equivalent
Display the New Project dialog box to create a new project	CTRL+SHIFT+N	CTRL+SHIFT+N
Display the Open File dialog box to open an existing file	CTRL+O	CTRL+SHIFT+INS
Display the Open Project dialog box to open an existing project	CTRL+SHIFT+O	No equivalent
Display the Add New Item dialog box to add a new file to the current project	CTRL+SHIFT+A	No equivalent
Display the Add Existing Item dialog box to add an existing file to the current project	ALT+SHIFT+A	No equivalent
Display the Query Designer	CTRL+SHIFT+Q	CTRL+SHIFT+Q
Close a menu or dialog box, canceling the action	ESC	ESC

Windows Management and Toolbar Keyboard Shortcuts

Action	Standard	SQL Server 2000
Close the current MDI child window	CTRL+F4	CTRL+F4
Print	CTRL+P	CTRL+P
Exit	ALT+F4	ALT+F4
Toggle full screen mode	SHIFT+ALT+ENTER	SHIFT+ALT+ENTER
Close the current tool window	SHIFT+ESC	SHIFT+ESC
Cycle through the next MDI child windows	CTRL+TAB	CTRL+TAB
Cycle through the previous MDI child windows	CTRL+SHIFT+TAB	CTRL+SHIFT+TAB
Move to the current tool window toolbar	SHIFT+ALT	No equivalent
Move to the next tool window	ALT+F6	ALT+F6
Move to the previously selected window	SHIFT+ALT+F6	SHIFT+ALT+F6
Opens a new query editor window	No equivalent	CTRL+O
Display Object Explorer	F8	F8
Display Registered Servers	CTRL+ALT+G	No equivalent
Display Template Explorer	CTRL+ALT+T	CTRL+ALT+T
Display Solution Explorer	CTRL+ALT+L	CTRL+ALT+L
Display the Summary Window	F7	F7
Display the Properties Window	F4	F4
Display the Toolbox	CTRL+ALT+X	CTRL+ALT+X
Display the Bookmarks Window	CTRL+K, CTRL+W	No equivalent
Display the Browser Window	CTRL+ALT+R	CTRL+ALT+R
Display the Error List Window (Transact-SQL Editor only)	CRTL+\, CTRL+E	CRTL+\, CTRL+E

Cursor Movement Keyboard Shortcuts

Action	Standard	SQL Server 2000
Move the cursor left	LEFT ARROW	LEFT ARROW
Move the cursor right	RIGHT ARROW	RIGHT ARROW
Move the cursor up	UP ARROW	UP ARROW
Move the cursor down	DOWN ARROW	DOWN ARROW
Move the cursor to the beginning of the line	HOME	HOME
Move the cursor to the end of the line	END	END
Move the cursor to the beginning of the document	CTRL+HOME	CTRL+HOME
Move the cursor to the end of the document	CTRL+END	CTRL+END
Move the cursor up one screen	PAGE UP	PAGE UP
Move the cursor down one screen	PAGE DOWN	PAGE DOWN
Moves the cursor one word to the right	CTRL+ RIGHT ARROW	CTRL+ RIGHT ARROW
Moves the cursor one word to the left	CTRL+ LEFT ARROW	CTRL+ LEFT ARROW

Text Selection Keyboard Shortcuts

Action	Standard	SQL Server 2000
Select text from the cursor to the beginning of the document	CTRL+SHIFT+ HOME	CTRL+SHIFT+ HOME
Select text from the cursor to the end of the document	CTRL+SHIFT+END	CTRL+SHIFT+END
Select text from the cursor to the start of the current line	SHIFT+HOME	SHIFT+HOME
Select text from the cursor to the end of the current line	SHIFT+END	SHIFT+END
Select text down line by line starting from the cursor	SHIFT+ DOWN ARROW	SHIFT+ DOWN ARROW
Select text up line by line starting from the cursor	SHIFT+UP ARROW	SHIFT+UP ARROW
Move the cursor up one line, extending the selection	SHIFT+ALT+ UP ARROW	SHIFT+ALT+ UP ARROW
Extend selection up one page	SHIFT+ PAGE UP	SHIFT+ PAGE UP
Extend selection down one page	SHIFT+ PAGE DOWN	SHIFT+ PAGE DOWN
Select the entire current document	CTRL+A	CTRL+A
Select the word containing the cursor, or the closest word	CTRL+W	CTRL+W
Select the current location in the editor, back to the previous location in the editor	CTRL+=	CTRL+=
Extend the selection to the top of the current window	CTRL+SHIFT+ PAGE UP	CTRL+SHIFT+ PAGE UP
Move the cursor to the last line in view, extending the selection	CTRL+SHIFT+ PAGE DOWN	CTRL+SHIFT+ PAGE DOWN
Extend the selection one word to the right	CTRL+SHIFT+ RIGHT ARROW	CTRL+SHIFT+ RIGHT ARROW
Extend the selection one word to the left	CTRL+SHIFT+ LEFT ARROW	CTRL+SHIFT+ LEFT ARROW
Move the cursor to the right one word, extending the selection	CTRL+SHIFT+ALT+ RIGHT ARROW	CTRL+SHIFT+ALT+ RIGHT ARROW
Move the cursor to the left one word, extending the selection	CTRL+SHIFT+ALT+ LEFT ARROW	CTRL+SHIFT+ALT+ LEFT ARROW

Bookmark Keyboard Shortcuts

Action	Standard	SQL Server 2000
Set or remove a bookmark at the current line	CTRL+K, CTRL+K	CTRL+F2
Next bookmark	CTRL+K, CTRL+N	F2
Previous bookmark	CTRL+K, CTRL+P	SHIFT+F2
Clear bookmarks	CTRL+K, CTRL+L	CTRL+SHIFT+F2

Tree Control Keyboard Shortcuts

Action	Standard	SQL Server 2000
Collapse tree nodes	- (on the numeric keypad)	- (on the numeric keypad)
Expand all tree nodes	* (on the numeric keypad)	* (on the numeric keypad)
Scroll the tree control up in the window	CTRL+UP ARROW	CTRL+UP ARROW
Scroll the tree control down in the window	CTRL+ DOWN ARROW	CTRL+ DOWN ARROW

Code Editor Keyboard Shortcuts

All shortcuts are not implemented in all types of code editors.

Action

Standard

SQL Server 2000

Toggle the full-screen display

SHIFT+ALT+ENTER

Scroll text up one line

CTRL+UP ARROW

Scroll text down one line

CTRL+

DOWN ARROW

CTRL+

DOWN ARROW

Reverse the last editing action

CTRL+Z

Restore the previously undone edit

CTRL+SHIFT+Z

CTRL+Y

ALT+SHIFT+BACKSPACE

CTRL+SHIFT+Z

CTRL+Y

ALT+SHIFT+BACK SPACE

Insert the body of the specified file into the current window.

The window name is not changed to match the specified file.

Note:

No equivalent

CTRL+SHIFT+P

Save the selected item

CTRL+S

Save all

CTRL+SHIFT+S

CTRL+F4

CTRL+P

Exit

ALT+F4

Delete all text in the current file

CTRL+SHIFT+DEL

Display the Go To Line dialog box

CTRL+G

Increase line indent

TAB

Decrease line indent

SHIFT+TAB

Make the selected text upper case

CTRL+SHIFT+U

Make the selected text lower case

CTRL+SHIFT+L

Make the selected text a comment

CTRL+K, CTRL+C

CTRL+SHIFT+C

Uncomment the selected text

CTRL+K, CTRL + U

CTRL+SHIFT+R

Open a new query with current connection

CTRL+Q

CTRL+N

Open database in Object Explorer

ALT+F8

Specify values for template parameters

CTRL+SHIFT+M

Execute the selected portion of the query editor or the entire query editor if nothing is selected

CTRL+E

ALT+X

CTRL+E

ALT+X

Parse the selected portion of the query editor or the entire query editor if nothing is selected

CTRL+F5

Display the estimated execution plan

CTRL+L

Cancel the executing query

ALT+BREAK

Include actual execution plan in the query output

CTRL+M

CTRL+K

Open the Query Option dialog box

No equivalent

CTRL+SHIFT+O

Output results in a grid

CTRL+D

Output results in text format

CTRL+T

Output results to a file

CTRL+SHIFT+F

Show or hide the query results pane

CTRL+R

Toggle between query and results pane

Move to the next active window in Management Studio

CTRL+F6

Auto hide all

ALT+ENTER

No equivalent

Windows

CTRL+W

No equivalent

Open SQL Server Profiler

CTRL+ALT+P

Text Manipulation in Code Editor Keyboard Shortcuts

Action	Standard	SQL Server 2000
Insert a new line	ENTER or SHIFT+ENTER	ENTER or SHIFT+ENTER
Swap the characters on either side of the cursor (Does not apply to the SQL Editor.)	CTRL+T	No equivalent
Delete on character to the right of the cursor	DELETE	DELETE
Delete one character to the left of the cursor	BACKSPACE or SHIFT+ BACKSPACE	BACKSPACE or SHIFT+ BACKSPACE
Insert the number of spaces configured for the editor	TAB	TAB
Insert a blank line above the cursor	CTRL+ENTER	CTRL+ENTER
Insert a blank line below the cursor	CTRL+SHIFT+ ENTER	CTRL+SHIFT+ ENTER
Change the selected text to lowercase	CTRL+SHIFT+L	CTRL+SHIFT+L
Change the selected text to uppercase	CTRL+SHIFT+U	CTRL+SHIFT+U
Toggle between insertion mode and overtype mode	INSERT	INSERT
Move selected lines to the left on tab stop	SHIFT+TAB	SHIFT+TAB
Delete the word to the right of the cursor	CTRL+DELETE	CTRL+DELETE
Delete the word to the left of the cursor	CTRL+BACKSPACE	CTRL+BACKSPACE
Transpose the words on either side of the cursor (Does not apply to the SQL Editor.)	CTRL+SHIFT+T	CTRL+SHIFT+T

Transact-SQL Debugger Keyboard Shortcuts

Action	Standard	SQL Server 2000
Start or continue debugging	ALT+F5	No equivalent
Stop debugging	SHIFT+F5	No equivalent
Step into	F11	No equivalent
Step over	F10	No equivalent
Step out	SHIFT+F11	No equivalent
Implement the Run To Cursor command	CTRL+F10	No equivalent
Display the QuickWatch dialog box	CTRL+ALT+Q	No equivalent
Toggle breakpoint	F9	No equivalent
Delete all breakpoints	CTRL+SHIFT+F9	No equivalent
Display the Breakpoints window	CTRL+ALT+B	No equivalent
Break all	CTRL+ALT+BREAK	No equivalent
Display the Watch 1 window	CTRL+ALT+W, 1	No equivalent
Display the Watch 2 window	CTRL+ALT+W, 2	No equivalent
Display the Watch 3 window	CTRL+ALT+W, 3	No equivalent
Display the Watch 4 window	CTRL+ALT+W, 4	No equivalent
Display the Autos window	CTRL+ALT+V, A	No equivalent
Display the Locals window	CTRL+ALT+V, L	No equivalent
Display the Immediate window	CTRL+ALT+I	No equivalent
Display the Call Stack window	CTRL+ALT+C	No equivalent
Display the Threads window	CTRL+ALT+H	No equivalent

Microsoft IntelliSense Keyboard Shortcuts

Action	Standard	SQL Server 2000
List members	CTRL+SPACE or CTRL+J	CTRL+J
Complete word	ALT+RIGHT ARROW	ALT+RIGHT ARROW
Parameter information	CTRL+SHIFT+SPACE	No equivalent
Jump between syntax pairs	CTRL+]	No equivalent

Document Window and Browser Keyboard Shortcuts

Action	Standard	SQL Server 2000
Toggle full-screen mode	SHIFT+ALT+ENTER	SHIFT+ALT+ENTER
Move to the next pane of a split pane view of a document	F6	F6
Move to the previous document in the editor or designer	CTRL+SHIFT+F6 CTRL+SHIFT+TAB	CTRL+SHIFT+F6 CTRL+SHIFT+TAB
Move to the previous pane of a document in split pane view	SHIFT+F6	SHIFT+F6
Display the Browser Window	CTRL+ALT+R	CTRL+ALT+R
Back, display the previous page in the viewing history	ALT+LEFTARROW	ALT+LEFTARROW
Forward, display the next page in the viewing history	ALT+RIGHTARROW	ALT+RIGHTARROW

Solution Explorer Keyboard Shortcuts

Action	Standard	SQL Server 2000
Display Solution Explorer	CTRL+ALT+L	CTRL+ALT+L
Display the New File dialog box to create a new file	CTRL+N	CTRL+SHIFT+INS
Display the New Project dialog box to create a new project	CTRL+SHIFT+N	CTRL+SHIFT+N
Display the Open File dialog box to open an existing file	CTRL+O	No equivalent

Help and Books Online Keyboard Shortcuts

Action	Standard	SQL Server 2000
Help	F1	F1
Help on "How Do I"	CTRL+F1	CTRL+F1
Books Online Contents	CTRL+ALT+F1	CTRL+ALT+F1
Books Online Index	CTRL+ALT+F2	CTRL+ALT+F2
Help Search	CTRL+ALT+F3	CTRL+ALT+F3
Dynamic Help	CTRL+ALT+F4	CTRL+ALT+F4
Help Favorites	CTRL+ALT+F	CTRL+ALT+F

Search Keyboard Shortcuts

Action	Standard	SQL Server 2000
Display the Find dialog box	CTRL+F	CTRL+F
Display the Replace dialog box	CTRL+H	CTRL+H
Start incremental search. Type the characters to search for or press CTRL+I to search for characters from the previous search	CTRL+I	CTRL+B
Find the next occurrence of the previous search text	F3	F3
Find the previous occurrence of the search text	SHIFT+F3	SHIFT+F3
Find the next occurrence of the currently selected text	CTRL+F3	CTRL+F3
Find the previous occurrence of the currently selected text	CTRL+SHIFT+F3	CTRL+SHIFT+F3
Display the Replace in Files dialog box	CTRL+SHIFT+H	CTRL+SHIFT+H
Reverse incremental search so it starts at the bottom of the file and searches to the top	CTRL+SHIFT+I	CTRL+SHIFT+I
Select or clear the Search up option in Find and Replace	ALT+F3, B	ALT+F3, B
Stop the Find in Files search	ALT+F3, S	ALT+F3, S
Select or clear the Find whole word option in Find and Replace	ALT+F3, W	ALT+F3, W
Selects or clears the Wildcard option in Find and Replace	ALT+F3, P	ALT+F3, P

Cut and Paste Keyboard Shortcuts

Action	Standard	SQL Server 2000
Cut (delete the currently selected item and place it to the Clipboard)	CTRL+X or SHIFT+DELETE	CTRL+X or SHIFT+DELETE
Copy to the Clipboard	CTRL+C or CTRL+INSERT	CTRL+C or CTRL+INSERT
Paste from the Clipboard at the insertion point	CTRL+V or SHIFT+INSERT	CTRL+V or SHIFT+INSERT

Action

Standard

SQL Server 2000

Cut (delete the currently selected item and place it to the Clipboard)

CTRL+X

SHIFT+DELETE

CTRL+X

SHIFT+DELETE

Copy to the Clipboard

CTRL+C

CTRL+INSERT

CTRL+C

CTRL+INSERT

Paste from the Clipboard at the insertion point

CTRL+V

SHIFT+INSERT

CTRL+V

SHIFT+INSERT

Log Viewer Keyboard Shortcuts

Action	Standard	SQL Server 2000
Refresh	F5	F5
Move between the Select logs pane and the Log file summary pane	F6	F6
Move to the Log file summary pane	ALT+S	ALT+S
Load a new log	CTRL+SHIFT+L	CTRL+SHIFT+L
Export a log	CTRL+SHIFT+E	CTRL+SHIFT+E
Filter a log	CTRL+SHIFT+F	CTRL+SHIFT+F
Search in a log	CTRL+SHIFT+S	CTRL+SHIFT+S

Activity Monitor Keyboard Shortcuts

Action	Standard	SQL Server 2000
Refresh	F5	F5
Filter the monitor display	CTRL+SHIFT+F	CTRL+SHIFT+F

Replication Monitor Keyboard Shortcuts

Action	Standard	SQL Server 2000
Refresh	F5	F5
Open a detail window from a grid	ENTER	ENTER

Replication Conflict Viewer Keyboard Shortcuts

Action	Standard	SQL Server 2000
Define filter	F6	F6
Apply filter	F7	F7
Show all columns	F8	F8

Sysobjects Information

2009-03-04T19:53:00.000-08:00

Use Sysobjects in SQL Server to Find Useful Database Information

SQL Server sysobjects Table contains one row for each object created within a database. In other words, it has a row for every constraint, default, log, rule, stored procedure, and so on in the database. Therefore, this table can be used to retrieve information about the database. We can use xtype column in sysobjects table to get useful database information. This column specifies the type for the row entry in sysobjects.

For example, you can find all the user tables in a database by using this query:

 select * from sysobjects where xtype='U'

Similarly, you can find all the stored procedures in a database by using this query:

 select * from sysobjects where xtype='P'

This is the list of all possible values for this column (xtype):

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

Stored procedure which were created in last 7days

2009-02-09T19:10:00.000-08:00

Following script will provide name of all the stored procedure which were created in last 7 days, they may or may not be modified after that.

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7

To get the row count in a faster way

2009-02-09T19:08:00.000-08:00

To get the row count in a faster way , the conventional way would be very slow.

SELECT rows FROM sysindexes
WHERE id = OBJECT_ID('TABLEA') AND indid < 2

TABLEA : Is the name of the table

Unmatching records between two tables

2009-02-09T19:04:00.000-08:00

SELECT SID FROM TABLEA T1 WHERE NOT EXISTS (
SELECT * FROM TABLEB T2
WHERE (T1.SID=T2.SID))

(or)

(select TABLEA.SID from TABLEA) EXCEPT
(select TABLEB.SID from TABLEB)

Exporting the output into a text or csv file

2009-02-09T19:01:00.000-08:00

Syntax

C:\>sqlcmd -S \\Server_pathname -i C:\script.sql -o C:\test.txt

Here Script.sql is a sql script in which the desired script is written and the related output is exported into test.txt file at the following location.