T-SQL is my favorite aspect of working with SQL Server. The release of SQL Server 2005 coincided with the beginning of my involvement with the community as a presenter and author. For a while, I would put together a \u201cWhat\u2019s new with T-SQL\u201d session for each release. Eventually, the windowing functions were such a big part of my material, that I began focusing on them.<\/p>\n
In SQL Server 2017 and 2019, I\u2019m less excited about the new things one can do in T-SQL than I am about the optimizer enhancements that improve performance without making code changes. This group of features is called Intelligent Query Processing<\/a> (IQP). With these enhancements, Microsoft has addressed some of the typical antipatterns that cause queries to run slow.<\/p>\n Back in my consulting days, I would often see these \u201cred flags\u201d and teach the customer why these patterns were usually bad while coming up with alternative solutions.<\/p>\n One example involved scalar UDFs (user defined functions) that were nested several layers deep in a query. There was no advantage to using the UDFs, except for making the query look nicer. It was also a dev shop with no database expert on staff, and they were approaching SQL Server they way that they wrote C# by using functions to hide complexity. By replacing the UDFs with code in the query and adding a couple of appropriate indexes, the query went from 60 minutes down to a few seconds. Until the version of 2019 of SQL Server, that was always the best approach.<\/p>\n