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:

1. Think in sets, not in rows
2. Every part of your query is a table result, and can be referenced as such by later parts of the query
3. Know the logical processing order of queries: From -> Join -> Where -> Group By -> Having -> Select -> Distinct -> Order By -> Offset/Fetch
4. 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
5. 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.
6. 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
7. 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
8. In 99% of cases, temp tables are better than table variables
9. Indexes will help your queries (but make sure there aren’t too many of them)
10. Statistics will help them too
11. 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.

The post The 10 Basic Concepts of T-SQL appeared first on Madeira Data Solutions.