No matter what your level of expertise, it always helps to learn new tips and best practices from other IT professionals. This article contains a collection of the top SQL Server development tips that I have come across. Hopefully, some of these will help you with your database development and administration operations.
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 tra...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
nsaction 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.
I have seen webpages make as many as 15 database calls, with most executing stored procedures that return a single row or value. 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.
ABOUT THE AUTHOR:
[IMAGE]Roman Rehak is a senior database architect at MyWebLink.com in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. He regularly contributes SQL Server articles to Visual Studio Magazine , SQL Server Magazine and other technical publications and presents at user groups and conferences in the U.S. and Canada. He is an active member and volunteer for the Professional Association for SQL Server. Roman also serves as the technical chair for the SQL Server track at the annual DevTeach conferences in Canada and is the president of the Vermont SQL Server User Group.
