SQL Server Transact-SQL General Tips Part III

By : Brad McGehee

When using the WHILE statement, don't avoid the use of BREAK just because some people consider it bad programming form. Often when creating Transact-SQL code using the WHILE statement, you can avoid using BREAK by moving a few lines of code around. If this works in your case, then by all means don't use BREAK. But if your efforts to avoid using BREAK require you to add additional lines of code that makes your code run slower, then don't do that. Sometimes, using BREAK can speed up the execution of your WHILE statements. [6.5, 7.0, 2000, 2005] Updated 6-12-2006

*****

One of the advantages of using SQL Server for n-tier applications is that you can offload much (if not most) of the data processing work from the other tiers and place it on SQL Server. The more work you can perform within SQL Server, the fewer the network roundtrips that need to be made between the various tiers and SQL Server. And generally the fewer the network roundtrips, the more scalable and faster the application becomes.

But in some applications, such as those than involve complex math, SQL Server has traditionally been weak. In these cases, complex math often could not be performed within SQL Server, instead it had to be performed on another tier, causing more network roundtrips than desired.

By using user-defined functions (UDFs), this is becoming less of a problem. UDFs allow developers to perform many complex math functions from within SQL Server, functions that previously could only be performed outside of SQL Server. By taking advantage of UDFs, more work can stay with SQL Server instead of being shuttled to another tier, reducing network roundtrips, and potentially boosting your application's performance.

Obviously, boosting your application's performance is not as simple as moving math functions to SQL Server, but it is one feature of SQL Server 2000/2005 that developers can take advantage of in order to boost their application's scalability and performance. [2000, 2005] Updated 6-12-2006

*****

SQL Server 2000/2005 offers a data type called "table." Its main purpose is for the temporary storage of a set of rows. A variable, of type "table," behaves as if it is a local variable. And like local variables, it has a limited scope, which is within the batch, function, or stored procedure in which it was declared. In most cases, a table variable can be used like a normal table. SELECTs, INSERTs, UPDATEs, and DELETEs can all be made against a table variable.

For better performance, if you need a temporary table in your Transact-SQL code, consider using a table variable instead of creating a conventional temporary table. Table variables are often faster, but not always. In addition, table variables found in stored procedures result in fewer compilations (than when using temporary tables), and transactions using table variables only last as long as the duration of an update on the table variable, requiring less locking and logging resources. [2000, 2005] Updated 10-02-2006

*****

Don't repeatedly reuse the same function to calculate the same result over and over within your Transact-SQL code. For example, if you need to reuse the value of the length of a string over and over within your code, perform the LEN function once on the string, and this assign the result to a variable, and then use this variable, over and over, as needed in your code. Don't recalculate the same value over and over again by reusing the LEN function each time you need the value, as it wastes SQL Server resources and hurts performance. [6.5, 7.0, 2000, 2005] Updated 10-02-2006

*****

Many developers choose to use an identify column at their primary key. By design, an identity column does not guarantee that that each newly created row will be consecutively numbered. This means there will most likely be occasional gaps in the identity column numbering scheme. For most applications, occasional gaps in the identity column present no problems.

On the other hand, some developers don't like these occasional gaps, trying to avoid them. With some clever use of INSTEAD OF triggers in SQL Server 2000,2005, it is possible prevent these numbering gaps. But at what cost?

The problem with trying to force an identify column to number consecutively without gaps can lead to locking and scalability problems, hurting performance. So the recommendation is not to try to get around the identify column's built-in method of working. If you do, expect performance problems. [2000, 2005] Updated 10-02-2006

*****

If you use the BULK INSERT to import data into SQL Server, seriously consider using the TABLOCK hint along with it. This will prevent SQL Server from running out of locks during vary large imports, and also boost performance due to the reduction of lock contention. [7.0, 2000, 2005] Added 11-22-2004

*****

To help identify long running queries, use the SQL Server Profiler Create Trace Wizard to run the "TSQL By Duration" trace. You can specify the length of the long running queries you are trying to identify (such as over 1000 milliseconds), and then have these recorded in a log for you to investigate later. [7.0]