SQL Server T-SQL

SQL Server Triggers

If you need to implement cascading referential integrity (such as cascading deletes) in your SQL databases, use the cascading referential integrity constraint instead of triggers to perform the cascading delete, as they are much more efficient and can boost performance. If you have an older (7.0 and older) application that you have moved to SQL […]

SQL Server Performance Tuning for Stored Procedures

Whenever a client application needs to send Transact-SQL to SQL Server, send it in the form of a stored procedure instead of a script or embedded Transact-SQL. Stored procedures offer many benefits, including: Reduced network traffic and latency, boosting application performance. Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing […]

User-Defined Functions

While user defined functions can offer great convenience, they can also sometimes hurt performance. This problem with them is that they use row-by-row processing, similar to how a cursor works, instead of working as a set-based operation. So if the result set of your query, which is using a user defined function, is very small, […]

SQL Server Temp Table Performance Tuning

In general, temp tables should be avoided, if possible. Because they are created in the tempdb database, they create additional overhead for SQL Server, slowing overall performance. As an alternative to temp tables, consider the following alternatives: Rewrite your code so that the action you need completed can be done using a standard query or […]

SQL Server Transact-SQL WHERE

This tip may sound obvious to most of you, but I have seen professional developers, in two major SQL Server-based applications used worldwide, not follow it. And that is to always include a WHERE clause in your SELECT statement to narrow the number of rows returned. If you don’t use a WHERE clause, then SQL […]

SQL Server Transact-SQL General Tips

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 […]

Performance Tuning SQL Server Cursors

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. Here are some alternatives to using a cursor: Use WHILE LOOPS Use temp tables […]

SQL Server Transact-SQL SELECT Statement

When using the UNION statement, keep in mind that, by default, it performs the equivalent of a SELECT DISTINCT on the final recordset. In other words, UNION takes the results of two like recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. This process occurs even if there […]

SQL Server Transact-SQL DML

Performing UPDATES takes extra resources for SQL Server to perform. When performing an UPDATE, try to do as many of the following recommendations as you can in order to reduce the amount of resources required to perform the UPDATE. The more of the following suggestions you can do, the faster the UPDATE will perform. If […]

Performance Tuning SQL Server Constraints

The Query Optimizer can use constraints to help it select the best performing query. In order to get the best performance, it is important that you use them appropriately throughout your database application, such as using them for primary keys, unique keys, primary keys, and so on. [6.5, 7.0, 2000, 2005] Updated 11-6-2006 ***** If […]