Sometimes abbreviated T-SQL, Transact-SQL is Microsoft’s and Sybase’s proprietary extension to the SQL language. In order to make it more powerful, SQL has been enhanced with additional features such as: Control-of-flow language and Local variables, Various support functions for string processing, date processing, mathematics, etc. Improvements to DELETE and UPDATE statements
Following are some tips for T-SQL scripts
- Use the SQL Server Profiler Create Trace Wizard to run the “T-SQL 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.
- In your queries, don’t return column data you don’t need. For example, you should not use SELECT * to return all the columns from a table if you don’t need all the data from each column. In addition, using SELECT * prevents the use of covered indexes, further potentially hurting query performance.
- 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 is most cases, using either option works equally efficiently, there are some instances (such as rows returned from anunsorted 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.
- In a WHERE clause, the various operators used directly affect how fast a query is run. This is because some operators lend themselves to speed over other operators. Of course, you may not have any choice of which operator you use in your WHERE clauses, but sometimes you do.
- 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 or others come back to the code and try to modify it.
- 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 rowby- row operations, try to find another method to perform the task.
- If you know that there are duplicate records, and this presents a problem for your application, then by all means use the UNION statement to eliminate the duplicate rows. But if not, use UNION ALL, which is less resource intensive.
- Carefully evaluate whether your SELECT query needs the DISTINCT clause or not. Some developers automatically add this clause to every one of their SELECT statements, even when it is not necessary.
- Always include a WHERE clause in your SELECT statement to narrow the number of rows returned. Only return those rows you need.
- When you have a choice of using the IN or the BETWEEN clauses in your Transact-SQL, you will generally want to use the BETWEEN clause, as it is more efficient.
- If you need to write a SELECT statement to retrieve data from a single table, don’t SELECT the data from a view that points to multiple tables. Instead, SELECT the data from the table directly, or from a view that only contains the table you are interested in. If you SELECT the data from the multi-table view, the query will experience unnecessary overhead, and performance will be hindered.
- If your application allows users to run queries, but you are unable in your application to easily prevent users from returning hundreds, even thousands of unnecessary rows of data, consider using the TOP operator within the SELECT statement. This way, you can limit how many rows are returned, even if the user doesn’t enter any criteria to help reduce the number or rows returned to the client.
Chosen from SQL Server DBA best practices By Brad M.mcGehee