SQL Server Transact-SQL General Tips Part I

By : Brad McGehee

Don't include code, variable, 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. [6.5, 7.0, 2000, 2000, 2005] Updated 1-24-2006

*****

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 try to modify it. [6.5, 7.0, 2000, 2005] Updated 1-24-2006

*****

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. [6.5, 7.0, 2000, 2005] Updated 1-24-2006

*****

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. [7.0, 2000, 2005] Updated 1-24-2006

*****

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. [6.5, 7.0, 2000, 2005] Updated 1-24-2006

*****

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. [6.5, 7.0, 2000, 2005] Updated 1-24-2006

*****

If you need to create a primary key (using a value meaningless to the record, other than providing a unique value for a record), many developers will use either an identity column (with an integer data type) or an uniqueindentifier data type.

If your application can use either option, then you will most likely want to choose the identity column over the uniqueindentifier column.

The reason for this is that the identity column (using the integer data type) only takes up 4 bytes, while the uniqueindentifier column takes 16 bytes. Using an identifier column will create a smaller and faster index. [7.0, 2000, 2005] Updated 1-24-2006

*****

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. [6.5, 7.0, 2000, 2005] Updated 1-24-2006

*****

Suppose you have data in your table that represents the logical information of "Yes" and "No" and you want to give the results of a query to someone who isn't working all day with computers. For such people, they may not know that a 1 is the logical representation of TRUE while a 0 represents FALSE. Sure, you can do this at the presentational layer. But what if someone comes to your desk, begging for immediate help? Here's a little trick to make BITs (or any other 0 and 1 data) look a bit more intuitive:

CREATE TABLE MyBits 

(

id INT IDENTITY(1,1) PRIMARY KEY 

, bool BIT

)

GO

INSERT INTO MyBits

SELECT 0  

UNION ALL 

SELECT 1

GO

SELECT 

id 

, bool 

, SUBSTRING('YesNo', 4 - 3 * bool, 3) as YesNo

FROM

MyBits

GO

DROP TABLE MyBits 

id          bool YesNo 

----------- ---- ----- 

1           0    No

2           1    Yes

(2 row(s) affected)

How does this work? The trick happens inside the SUBSTRING function. Precisely, when calculating the start value for the SUBSTRING. If our column "bool" contains a 0, the calculation looks like SUBSTRING('YesNo', 4 - 3 * 0, 3), which resolves to SUBSTRING('YesNo', 4, 3) and therefore, correctly returns 'No'. We actually use here another feature of SUBSTRING. If the string is shorter than our requested length, SUBSTRING simply returns the shorter string without filling up the missing spaces. Finally, in case a 1 is in our "bool" column, the calculation goes like SUBSTRING('YesNo', 4 - 3 * 1, 4), which is SUBSTRING('YesNo, 1, 3) and that is 'Yes'. [7.0, 2000, 2005] Added 5-9-2005