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.