Contact us

Madeira Data Solutions

Your Data, Our Solutions

The 10 Basic Concepts of T-SQL

Written By: Matan Yungman 29/08/2017

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.



One response to “The 10 Basic Concepts of T-SQL”

  1. Jeff Moden says:

    You wrote “Avoid user-defined functions as much as possible”. That’s actually not correct advice. There are 3 basic types of user defined functions and a couple of implied “sub-types”. In most cases, I’ll agree that you should avoid Scalar UDFs and mTVFs (Multi-statement Table Value Functions), but iTVFs (Inline Table Value Functions) and the closely relate iSFs (Inline Scalar Functions, which are a special sub-type of iTVFs) can be the miracle answer to many performance problems. Further, iTVFs can be very effectively used to replace certain views as a form of “parameterized inline view”.

    How can you instantly know if you have a high performance iTVF (or iSF) instead of an mTVF or Scalar function? It’s easy. If the function contains the word BEGIN, this it cannot be an iTVF.

    While I do agree that Scalar User Defined Functions should generally be avoided, they’ve actually gotten more of a bad rap than they deserved. People that use SET STATISTICS are frequently unaware of the fact that measuring performance with SET STATISTICS will make Scalar UDFs look 200 times worse than they actually are, which is “only” 7 times slower than doing the same thing directly in code or using an “iSF”. Please see the following article on that subject.

    Thanks for listening,
    –Jeff Moden

Leave a Reply

Your email address will not be published. Required fields are marked *