Login or Sign Up to become a member!

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

From Wiki

This page contains a collection of best practices in regards to SQL Server programming. These best practices have been suggested by the members of LessThanDot, some of them are really common sense and most people will know them, there are, of course, always some that you didn't know of....hopefully you will find some of those in this list.

There are also pages for SQL Server Administration Best Practices and SQL Server Data Modeling And Design Best Practices, so check those out after you are done with this page. As always this is a work in progress, make sure to bookmark this page and come back often because we will add more content on a regular basis.

Contents [Hide] 1 Dates 2 Dynamic SQL 3 Query Optimization 4 Testing 5 Transactions and Error Handling 6 Triggers 7 Stored Procedures

Dates

Query Optimizations With Dates

Setting a standard DateFormat for SQL Server by George Mastros

How Does Between Work With Dates In SQL Server? by Denis Gobo

Bad habits to kick : mis-handling date / range queries by Aaron Bertrand

Use ISO formats for dates

Date and time formats for input by Tibor Karaszi

There are many formats available to specify a date/time/datetime value. Some are "better" than others and as you read further through this article I hope you will understand what I mean by "better". Interestingly enough, all formats are available for all types. So even a time-only format is allowed for a date-only type etc.

Dynamic SQL

Good Coding Practices and Tips for Dynamic SQL by Erland Sommarskog

Writing dynamic SQL is a task that requires discipline to avoid losing control over your code. If you just go ahead, your code can become very messy, and be difficult to read, troubleshoot and maintain. In this section, we will look at how to avoid this.

Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec by Denis Gobo

The procedure sp_executesql is better than exec or ad hoc queries when you have to deal with conversions in execution plans.

Changing exec to sp_executesql doesn't provide any benefit if you are not using parameters correctly by Denis Gobo

The problem is that when use sp_executesql without internal parameters the query plan will not be reused when you change the values of the parameters. When a new plan is generated everytime your values change you will bloat SQL Server's procedure cache and less memory will be available for data.

Query Optimization

Don't select value(s) into variable(s) when you can just use a join with the same conditions. * stub

SQL Server efficient handling of divide by zero by George Mastros

T-SQL Best Practices – Parameter Sniffing by Gregory A. Larsen

Parameter Sniffing by Plamen Ratchev
What is "parameter sniffing"? When a stored procedure is first executed SQL Server looks at the input parameters and uses this as guidance to build the query plan. This is known as "parameter sniffing".This is good as long as the input parameters for the first invocation are typical for future invocations. But if that is not the case this will cause performance problems.

Use SET NOCOUNT ON to minimize SQL Server messages * stub

Don't Use (select *), but List Columns
If you list just the columns that you need you could incur less reads than if you used SELECT *

Write set based queries instead of queries that use cursors * stub

Write sargable queries
Sargable queries are queries written in such a way that the optimizer can use the index for faster data retrieval.

A Beginner's Guide to Indexing by Adam Haines

SQL Server Perfmon (Performance Monitor) Best Practices by Brent Ozar

Database administrators need to know each SQL Server’s weakest link so we know where to focus their hardware budgets and time. On any given server, we need to know what the slowest component is, and where it’ll pay off to invest hours and dollars. This is performance tuning, and the first place to start is by using Performance Monitor.

Testing

Your testbed has to have the same volume of data as on production in order to simulate normal usage by Denis Gobo

How can you expect anything to behave the same when you compare 100 rows in staging against 13 million rows in production? This is one of the fundamental flaws when people design a database, move it to production and then find out that it blows up/breaks down/is unusable on production.

Transactions and Error Handling

SQL Server 2008 error handling best practice by Anthony Bloesch

Error handling in SQL Server 2008 needs careful implementation. The Microsoft “Oslo” Repository’s API has the further problem that we cannot mandate the error handling logic in our callers. Thus a stored procedure call could be in a transaction or not and in a try-catch block or not. Below is the pattern we have chosen based on experimentation and best practice guidance from the SQL Server team and other SQL Server experts

Use XACT ABORT to roll back non trappable error transactions

Implementing Error Handling with Stored Procedures and Error Handling in SQL Server – a Background. by Erland Sommarskog

Two articles on error handling in SQL Server. I have split the topic on two articles, so if you only want to learn how to implement your error handling you can start there. The background article is more of interest to intermediate and advanced SQL users. Both articles also cover ADO and ADO .Net. The articles are focused on SQL 2000, and the new features in SQL 2005 are covered very briefly

Triggers

Coding SQL Server triggers for multi-row operations by Denis Gobo

Post explaining what can go wrong when you don't code your triggers to handle multi-row operations

General best practices for SQL Server triggers

Stored Procedures

General best practices for SQL Server stored procedures *stub

Stored procedure "best practices" checklist by Aaron Bertrand

Don't start your procedures with SP_ by George Mastros