T-SQL Requires the Right Approach

Some of the most problematic T-SQL code I’ve seen over the years originated because the developer approached T-SQL with techniques more suitable for developing applications. I often saw these issues in my consulting days and always took the opportunity to teach the customer instead of only fixing the problems.

One reason is that SQL is a declarative language; you say what you want, and it’s up to the database engine to figure out how to do it. When writing code in an imperative or procedural language such as C#, you give instructions to the computer, and it follows those instructions, while databases benefit from a “set-based” approach to writing code.

I ran into a good example of this early in my career. There was a process to update employee information in a database with a table from the HR system each night to keep changes such as title, department, name, etc., up to date. It was a long time ago, and hardware was much slower then, but it took about 10 minutes to keep 2000 employee records up to date because it updated the target table with a nested loop approach. Every single row was compared column by column. If there were changes to two columns (i.e., department and title), the row would be updated twice -- once for each column.

From a programming perspective, updating in a piecemeal fashion made sense – break the problem down into small pieces and only update what needs to be updated. Intuitively it seems like the best way to approach the problem, however, it’s not the best way to work with SQL Server or any database platform. The solution was to update all the columns (except for the primary key) in those 2000 rows in one statement, which took a few seconds. Today, a MERGE statement might be the solution but wasn’t available in SQL Server 6.5.

Another problem I’ve seen frequently is the overuse of user-defined functions (UDFs), especially nesting scalar UDFs. When writing software, it makes sense to use functions for code reuse and to hide complexity, but it’s often a bad idea with SQL Server. For example, a customer had a UDF with just one parameter. The parameter’s value was determined early in the script, something like the current date. The UDF called another UDF, which called another UDF, and there were a couple of queries in the UDFs as well. The top UDF was used in the SELECT list of a query returning millions of rows, so it was called millions of times. Since the parameter value was not dependent on the query, just running the UDF once and saving the returned value in a variable used in the query instead of the function call made a huge difference. After moving the UDF out of the SELECT list, we “unwound” the UDF into a few statements at the top of the script. The resulting script was not as pretty, but it performed so much better.

Many other anti-patterns cause poor query performance, but these two examples happened because T-SQL was treated like application code.

Kathi Kellenberger

Join the debate, and respond to the editorial on the forums

Read what Grant, Steve and Kathi predict will be the biggest challenges for data professionals to overcome this year.

Placeholders come in very handy in Flyway, but troubleshooting the SQL migration and callback scripts that use them can be tricky. This articles demonstrates how to develop, test and debug these scripts in a tool designed for these tasks, such as SSMS, leaving Flyway to do what it's designed for, which is running the scripts.

For T-SQL Tuesday #146, I talk about a couple of c...

First things first–if you are reading this, and not regularly running consistency checks on your SQL Server databases, you should drop everything you are doing and go do that....

The first T-SQL Tuesday invitation post of 2022 comes from Andy Yun. Andy’s invitation asks to describe a time when you learned something new that then changed your mind...

This article includes a PowerShell script that can help you quickly inventory all of your SQL Servers in your environment to get service accounts, version, ports and more.

Here listed is the current percentages of SQL server versions running our Daily Check up with Database Health Monitor. Compare this month’s percentages to Last Month’s Percentages The post SQL...

SQL Server provides two ways to include an incrementing number in a table. Greg Larsen explains how to replace an identity column with a sequence number.

Are you starting to experiment with Azure SQL Data...

Introduction In this article, we will talk about Azure SQL. The article will compile the FAQ about this topic. The article will cover FAQ related to concepts for newbies,...

Today Data Exposed goes live at 9AM PT on LearnTV. Every 4 weeks, we’ll do a News Update. We’ll include product updates, videos, blogs, etc. as well as upcoming...

This blog post is one of the 2 posts in the tiny SQL Managed Instance series on how-to determine the status of the connectivity from inside the Azure SQL Managed Instance....

The fifth and final installment in this series data lakehouse and the PaaS data warehouse...

A data lake is a scalable data storage repository ...

In this article we look at the Azure General Purpo...

SQL Server is not the only database you build and ...

Get flexible with Powershell & BUILTINAdminist...

In this article we look at how to use Azure Data S...

Through virtual and in-person workshops around the globe, we have taught tens of thousands of people how to communicate effectively with data. This series captures some of the noteworthy...

Reading Time: 3 minutes For this months T-SQL Tue...

This article is to explain how to perform ETL usin...

This article shows how to load multiple Excel sheets into multiple SQL Server database tables using C# code along with code samples.

MEDIAN: Returns the 50th percentile of values in a column. https://dax.guide/median/ MEDIANX: Returns the 50th percentile of an expression values in a table. https://dax.guide/medianx/

This is part three in a series on low-code machine...

How familiar are you with the SQL Server buffer po...

Jammy Jam There’s a bit of magic to index tunin...

The syntax for creating databases and tables in Po...

In the first post in this series I showed how it w...

Have you checked out sparklines in Power BI? Patrick explores how to set them up and also how you can be creative with them to add additional context to...

In this article we look at how to use Windows Task Scheduler to run Python scripts on a scheduled basis along with examples.

Learn how to get started with Python by setting up your environment using Anaconda, VS Code, Power BI and SQL Server.

This article will explain how to create a SQL Serv...

SQL Server 2017 Cumulative Update #28 is available...

Data breaches in the U.S. are on the rise, with mi...

In this article we look at different ways to creat...

Let’s say we have two tables, Parent and Child, ...

I saw a question the other day, questioning why th...

I am trying to get started on blogging again this ...

There’s a lot of guidance out there that states that TRY_CAST is a faster, more modern version of TRY_PARSE and that TRY_PARSE should only be used if you need...