Top tips and tricks for SQL Server database development
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.
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
-
This was first published in July 2009
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.
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
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. 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.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation