Top tricks for SQL Server database development

1) Always match datatypes in code with the columns in the database
It’s important to make sure that your datatypes match across all layers in your application. For example, if a column’s datatype is NVARCHAR (50), you should have the code in queries and stored procedures use local variables of the same datatype.

Similarly, the ADO.NET code in the data layer should specify the same datatype and length. Why is this important? Because if the datatypes and queries do not match, SQL Server needs to perform an implicit conversion of the datatypes to match them.

There are also some scenarios where SQL Server cannot use an existing index, even though the referenced column is indexed. Therefore, your query might end up using Index Scan instead of Index Seek, resulting in execution times with longer orders of magnitude than if the variables and columns were of the same type.

2) Do mass updates in batches
Developers sometimes need to modify data in one or more columns for all or most rows in a table. This is usually not an issue as long as the table is fairly small.

If the table is large, however, your update statement will lock the entire table and make it unavailable, even for data reads. Further more, a highly volatile table can bring down the entire application or website for the duration of the update. At times, a large, single transaction like this will greatly expand the size of the transaction log and — in extreme scenarios — contribute to running out of disk space on the database server.

It is therefore a good practice to do mass updates in batches, combined with frequent transaction log backups. In my experience, a batch of 10,000 or 50,000 works best. It is difficult to specify a threshold of when you should start considering batching, as it all depends on factors such as how fast you disk I/O is, how heavily the table is used, and more.

There is one guideline you can use though. A typical command timeout in ADO.NET is about 30 seconds. While the update takes place, other processes have to wait until it is finished. So if you expect that your update will take longer than 20-25 seconds, you are better off doing a batch update, otherwise you will end up with application timeouts.

Here is a sample code that shows how to update a column in a table, using 10,000 as batch size:

WHILE ( 0 = 0 )
BEGIN
        UPDATE TOP ( 10000 )
                Person
SET     Status = 2
WHERE   Status = 1
IF @@ROWCOUNT = 0
BREAK
    END

3) Utilize FOR-EACH stored procedures.
Once in a while you might need to perform the same action on all objects of a certain type. For example, you might need to assign a specific permission for all tables in the database. Developers often resort to cursors for tasks like this, but SQL Server comes with two handy stored procedures that make things a lot easier: sp_msForEachTable and sp_msForEachDB.

Each of these takes a command to be executed as a parameter. You can embed a question mark in the parameter as a placeholder for the table or database name in the command. At runtime, SQL Server replaces the question mark with the name of the table or database and executes it.

For example, the following code runs a full backup for each database on the server, except for TempDB:

EXEC sp_msforeachdb 'IF ''?'' <> ''tempdb'' BACKUP DATABASE ?
            TO DISK=''c:\backups\?.bak'' WITH INIT'

Here is another example of how these stored procedures can be useful — and somewhat dangerous. The following code deletes data in all tables in the database after disabling the foreign key. Naturally, you’ll want to exercise caution when using this code:

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
  DELETE FROM ?
else
  TRUNCATE TABLE ?
'

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

4) Version your database builds

It’s considered a good practice for developers to implement numeric versioning of databases, just like they do with applications.

It doesn’t require a lot of effort to implement versioning – you just have to create a table with a version number and additional timestamps. Once you get better at assigning a build number to each set of scripts and updating the version table when you deploy those scripts, it becomes much easier to troubleshoot and compare your databases. You could even code your scripts so that they don’t execute if the build number in the database is not higher than the build number in the script. The AWBuildVersion table in the AdventureWorks sample database is a good example to look at.

5) Minimize the number of network calls

This tip applies mainly to Web applications that pull data from a database. Less experienced developers often don’t realize that each database call is a relatively expensive operation. It’s not a big deal in small applications, but since many websites could become popular and used by thousands of simultaneous users, you need to start thinking about scalability and optimizing your page load times in advance.

One thing to keep in mind is that SQL Server can return multiple ResultSets in a single stored procedure. You can use the DataSet object in ADO.NET and populate a collection of DataTable objects in a single database call

-Yuva