T-SQL is the core language for server-side data access and manipulation in SQL Server. Applying best practices will help you get the most out of T-SQL. You can use the following checklist to make sure that your T-SQL code is efficient and easy to maintain.
Use set-based solutions. Set-based solutions are usually faster, simpler, and shorter than cursor-based solutions. Set-based solutions let SQL Server's optimizer evaluate multiple execution plans and choose an efficient one instead of forcing a particular approach. Cursors require a lot of overhead, so avoid them whenever possible. Iterative solutions are adequate in a few cases; learn how to identify those situations rather than always considering an iterative solution first.
Write multiple solutions. Don't be satisfied with the first solution that works. Always come up with multiple solutions, compare their performance, simplicity, and scalability, then choose the one that best suits your needs.
Stick to ANSI-compliant code. Use ANSI-compliant code as much as possible. Writing standard code has many advantages. It allows easier porting, not just between database platforms, but also to future versions of SQL Server. Also, the meaning of standard code is clear, unlike some proprietary constructs, such as using *= or =* for outer joins. Consider nonstandard code only where T-SQL uses a proprietary feature that gives significant performance benefits or functionality that has no standard alternative.
Use stored procedures. Use stored procedures instead of implementing all logic in the application. Stored procedures let you share application logic and they provide encapsulation, so you can revise their implementation without affecting the application as long as the procedure's interface remains the same. Stored procedures give you power to control data security and integrity, let you reduce network traffic by avoiding multiple roundtrips between client and server, and let you reuse execution plans.
Specify column names in your query's SELECT list. Always specify an explicit column list; don't use * in the SELECT list of your queries and table expressions (e.g., views, derived tables, inline functions). By specifying just the names of the columns you really need, you let the optimizer consider covering indexes. And you don't need to worry when the schema of the underlying objects change.
Never assume a physical order of the data. A common mistake inexperienced T-SQL programmers make is relying on a certain physical order of the data. Remember that T-SQL is based on the relational model, which in turn is based on set theory. A table (set) has no predetermined order to its rows. When you need data sorted, specify ORDER BY.
Use ORDER BY only if you really need the data sorted. Conversely, if you don't need to return sorted data to the client, don't specify ORDER BY—sorting data has a cost.
Specify filters. Rather than filtering the data at the client, specify a filter in your query to allow the use of indexes and limit the physical data that SQL Server accesses.
Don't use T-SQL for everything. T-SQL was designed mainly for data access and manipulation. Some tasks (e.g., formatting) work better when you perform them at the client.
Use search arguments. Search arguments are logical expressions you can use as filters to let the optimizer consider using indexes. Any manipulation you perform on a base column that's being filtered prevents the use of an index. Remember that SQL Server might implicitly convert a column because of data-type precedence.
Specify an object owner. Always specify object owners instead of letting SQL Server implicitly resolve them. This practice makes your code more readable and improves performance. You save a step in the resolution phase, and you prevent SQL Server from needing to grab special internal locks to resolve the object owner.
Use a semicolon (;) to suffix T-SQL statements. The use of a semicolon is ANSI-compliant and is mandatory with other database platforms. Although it's not mandatory in SQL Server 2000, some statements in SQL Server 2005 will require the semicolon, so start getting used to it now.
Get involved in SQL Server communities. People often ask me how to improve their T-SQL programming abilities in addition to reading books and articles. The best way I know is getting involved in the SQL Server communities (newsgroups, user groups, and so on). For T-SQL–related topics, I mainly recommend the public SQL Server programming newsgroup (news://msnews.microsoft.com/microsoft.public.sqlserver.programming).
