ASP.NET Tips #14 - T-SQL tips

While much of your code may be generated, at least some of it will have to be written by hand. If you are writing some or all of your T-SQL code, these tips will help you avoid problems.

1. SELECT * is not always a bad thing, but it's a good idea to only move the data you really need to move and only when you really need it, in order to avoid network, disk, and memory contention on your server.
2. Keep transactions as short as possible and never use them unnecessarily. The longer a lock is held the more likely it is that another user will be blocked. Never hold a transaction open after control is passed back to the application – use optimistic locking instead.
3. For small sets of data that are infrequently updated such as lookup values, build a method of caching them in memory on your application server rather than constantly querying them in the database.
4. If doing processing within a transaction, leave the updates until last if possible, to minimize the need for exclusive locks.
5. Cursors within SQL Server can cause severe performance bottlenecks. Avoid them. The WHILE loop within SQL Server is just as bad as a cursor.
6. Ensure your variables and parameters are the same data types as the columns. An implicit or explicit conversion can lead to table scans and slow performance.
7. A function on columns in the WHERE clause or JOIN criteria means that SQL Server can't use indexes appropriately and will lead to table scans and slow performance.
8. Don't use DISTINCT, ORDER BY, or UNION unnecessarily.
9. Table variables have no statistics within SQL Server. This makes them useful for working in situations where a statement level recompile can slow performance. But, that lack of statistics makes them very inefficient where you need to do searches or joins. Use table variables only where appropriate.
10. Multi-statement user-defined functions work through table variables. Because of this, they also don't work well in situations where statistics are required. If a join or filtering is required, avoid using them, especially if you are working with more than approximately 50 rows in the data set.
11. Query hints are actually commands to the query optimizer to control how a query plan is generated. These should be used very sparingly and only where appropriate.
12. One of the most abused query hints is NO_LOCK. This can lead to extra or missing rows in data sets. Instead of using NO_LOCK consider using a snapshot isolation level such as READ_COMMITTED_SNAPSHOT.
13. Avoid creating stored procedures that have a wide range of data supplied to them as parameters. These are compiled to use just one query plan.
14. Try not to interleave data definition language with your data manipulation language queries within SQL Server. This can lead to recompiles which hurts performance.
15. Temporary tables have statistics which get updated as data is inserted into them. As these updates occur, you can get recompiles. Where possible, substitute table variables to avoid this issue.
16. If possible, avoid NULL values in your database. If not, use the appropriate IS NULL and IS NOT NULL code.
17. A view is meant to mask or modify how tables are presented to the end user. These are fine constructs. But when you start joining one view to another or nesting views within views, performance will suffer. Refer only to tables within a view.
18. If you need to insert many rows at once into a table, use, where possible, the multi-row VALUES clause in INSERT statements.
19. Use extended events to monitor the queries in your system in order to identify slow running queries. If you’re on a 2005 or earlier system you’ll need to use a server-side trace.