Don’t include code, variables, or parameters that don’t do anything. This may sound obvious, but I have seen this in some off-the-shelf SQL Server-based applications. For example, you may see code like this:
SELECT column_name FROM table_name
WHERE 1 = 0
When this query is run, no rows will be returned. Obviously, this is a simple example (and most of the cases where I have seen this done have been very long queries). A query like this (even if part of a larger query) doesn’t perform anything useful, and doesn’t need to be run. It is just wasting SQL Server resources. In addition, I have seen more than one case where such dead code actually causes SQL Server to throw errors, preventing the code from even running. [2000, 2005, 2008] Updated 1-30-2009
*****
Don’t be afraid to make liberal use of in-line and block comments in your Transact-SQL code, they will not affect the performance of your application, and they will enhance your productivity when you have to come back to the code and modify it. [2000, 2005, 2008] Updated 1-30-2009
*****
If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task. Some options are to perform the task at the client, use tempdb tables, use derived tables, use a correlated subquery, or use the CASE statement. More often than not, there are non-cursor techniques that can be used to perform the same tasks as a SQL Server cursor. [2000, 2005, 2008] Updated 1-30-2009
*****
If your users perform many ad hoc queries on your SQL Server data, and you find that many of these “poorly-written” queries take up an excessive amount of SQL Server resources, consider using the “query governor cost limit” configuration option to limit how long a query can run.
This option allows you to specify the maximum amount of “seconds” a query will run, and whenever the query optimizer determines that a particular query will exceed the maximum limit, the query will be aborted before it even begins.
Although the value you set for this setting is stated as “seconds,” it does not mean seconds like we think of seconds. Instead, it relates to the actual estimated cost of the query as calculated by the query optimizer. You may have to experiment with this value until you find one that meets your needs.
There are two ways to set this option. First, you can change it at the server level (all queries running on the server are affected by it) using sp_configure “query governor cost limit,” or you can set it at the connection level (only this connection is affected) by using the SET QUERY_GOVERNOR_COST_LIMIT command. [2000, 2005, 2008] Updated 1-30-2009
*****
You may have heard of a SET command called SET ROWCOUNT. Like the TOP operator, it is designed to limit how many rows are returned from a SELECT statement. In effect, the SET ROWCOUNT and the TOP operator perform the same function.
While in most cases, using either option works equally efficiently, there are some instances (such as rows returned from an unsorted heap) where the TOP operator is more efficient than using SET ROWCOUNT. Because of this, using the TOP operator is preferable to using SET ROWCOUNT to limit the number of rows returned by a query. [2000, 2005, 2008] Updated 1-30-2009
*****
If you have the choice of using a join or a subquery to perform the same task within a query, generally the join is faster. But this is not always the case, and you may want to test the query using both methods to determine which is faster for your particular application. [2000, 2005, 2008] Updated 1-30-2009
*****
If you need to create a primary key (using a value meaningless to the row, other than providing a unique value for the row), many developers will use either an identity column (with an integer data type) or an uniqueidentifier data type.
If your application can use either option, then you will most likely want to choose the identity column over the uniqueidentifier column.
There are two reasons for this. First, the identity column (using the integer data type) only takes up 4 bytes, while the uniqueidentifier column takes 16 bytes. Using an identifier column will create a smaller and faster index. Second, if the column is also a clustered index, the identity column will write new records sequentially, while the uniqueidentifier column will write records randomly, hurting performance. [2000, 2005, 2008] Updated 1-30-2009
*****
If your application requires you to create temporary tables for use on a global or per connection use, consider the possibility of creating indexes for these temporary tables. While most temporary tables probably won’t need, or even use an index, some larger temporary tables can benefit from them. A properly designed index on a temporary table can be as great a benefit as a properly designed index on a standard database table.
In order to determine if indexes will help the performance of your applications using temporary tables, you will probably have to perform some testing. [2000, 2005, 2008] Updated 1-30-2009
*****