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 “What’s new with T-SQL” session for each release. Eventually, the windowing functions were such a big part of my material, that I began focusing on them.

In SQL Server 2017 and 2019, I’m 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 (IQP). With these enhancements, Microsoft has addressed some of the typical antipatterns that cause queries to run slow.

Back in my consulting days, I would often see these “red flags” and teach the customer why these patterns were usually bad while coming up with alternative solutions.

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.

As part of the SQL Server 2019 set of IQP features, Microsoft introduced Scalar UDF Inlining. In certain situations, the optimizer can treat the UDF as if the code inside it is just part of the query. Performance can drastically improve. This works for UDFs containing formulas, IF blocks, and queries. It doesn’t work for UDFs with WHILE loops, table variables, and time-dependent functions like GETDATE, for example. Actually, the list of exceptions is quite long, so be sure to take a look at the documentation.

During my session, I’ll demonstrate how query performance can improve just by upgrading. I’ll talk about what to watch out for and how to turn off the features at the database or query level when they don’t make sense.

There are seven enhancements that I’ll cover during my talk at the 2020 Virtual PASS Summit. I hope you will join me!