The 10 Basic Concepts of T-SQL
For the end of the latest Basic Querying and Programming in SQL Server course, I prepared a list of the basic concepts I recommend the students to remember and master in order to become proficient at writing T-SQL (and SQL in general). There’s no reason to keep those concepts inside the course, so here they are:
- Think in sets, not in rows
- Every part of your query is a table result, and can be referenced as such by later parts of the query
- Know the logical processing order of queries: From -> Join -> Where -> Group By -> Having -> Select -> Distinct -> Order By -> Offset/Fetch
- The more you prepare in advance, and the less calculations you perform on the fly, the better the query will run. Don’t take it to the extreme, of course
- Avoid user-defined functions as much as possible. Take the function logic out and use a set-based solution, or use an inline table-valued function if you want to keep the reuse and encapsulation a function gives you.
- Views can be evil (or to be more accurate, the way people use them). If you see a view that already queries from many tables and other views, consider whether you really want to use it, because in many cases, such views generate poor performing queries
- Keep queries simple. Don’t write “the mother of all queries”. If it’s complicated, break it down to smaller ones and use temp tables for temporary results
- In 99% of cases, temp tables are better than table variables
- Indexes will help your queries (but make sure there aren’t too many of them)
- Statistics will help them too
- Beware of things that prevent SQL Server from using an index, like wrapping a column with a function, using Like with % at the start of the predicate, or performing a manipulation of a column you filter on.