Press "Enter" to skip to content

Category: T-SQL

Handling NULL in T-SQL

Published 2025-04-08 by Kevin Feasel

Chad Callihan deals with non-existent data:

Handling NULL values can be a challenge and may lead to unexpected query results when mixed in with non-NULL values. Thankfully, there are a few SQL Server functions devoted to handling NULL values. Let’s look at two of them. The first, NULLIF, will help you to return a NULL value. The second, ISNULL, will help you with an alternative to a NULL value if you need another value in its place.

For the longest time, I strongly preferred COALESCE() over ISNULL() because of how it can handle multiple scenarios, as well as it being ANSI SQL syntax. But it turns out that, if you do only have two things to compare, ISNULL() is a little bit faster.

Comments closed

Setting TEXTSIZE in SQL server

Published 2025-04-08 by Kevin Feasel

Steve Jones limits results:

There is a SET command in SQL Server that changes how much data is returned from some fields. This short post shows what I learned about the SET TEXTSIZE command.

I’m pretty sure that, if I’ve ever used this command before, it was by accident. It’s kind of a neat concept in how it can limit the result size of larger blocks of text, but I think it’s too limiting to be a good solution save for some very specific circumstances.

Comments closed

RegEx Performance in Azure SQL DB

Published 2025-03-20 by Kevin Feasel

Brent Ozar breaks the bad news:

Regular expressions are a way of doing complex string searches. They can be really useful, but they have a reputation: they’re hard to write, hard to read, and they’re even harder to troubleshoot. Once you master ’em, though, they come in handy for very specific situations.

This post isn’t about their complexity, though. This post is about Azure SQL DB & SQL Server 2025’s regex performance.

Brent’s testing hurts, because I want to use regular expressions, and based on what he’s seen so far, we’re probably still better off using CLR-based regex in SQLSharp.

Comments closed

Linting SQL with SQLFluff

Published 2025-03-19 by Kevin Feasel

Josephine Bush busts out a linter:

I thought I didn’t care about linting, and lately, I haven’t written a lot of SQL, but for the SQL I do write, I have SQLFluff to help me format it. A friend of mine is big into SQLFluff and finally talked me into installing and using it. For more information about SQLFluff itself, visit here.

Josephine shows off some of the configuration for PostgreSQL’s psql as well as SQL Server’s T-SQL.

Comments closed

SQL Server Views and Implicit Data Types

Published 2025-03-17 by Kevin Feasel

Kendra Little takes a peek at a view:

Views let you do dumb things by accident in SQL Server. Then they make you have to think way too hard to fix them.

Most of the time when people create views, they start by refining a SELECT query, then turn it into a view. People also often create multiple views that pull different slices of data and UNION the results together.

Combined, these two things easily lead to undeclared datatypes in views with problematic implicit conversions.

Read on for an example of this problem in action. Kendra’s example involved a view and a separate table, but you can also see this kind of thing pop up in a view that itself contains set operators like UNION.

Comments closed

SQL Server Performance Office Hours

Published 2025-03-17 by Kevin Feasel

Erik Darling answers a set of user questions:

You have said that table variables, CTEs, Change Tracking, and Azure Managed Instances all suck. Do you have a full list of “features” to avoid?

Click through for a video of Erik answering questions around deadlocks, terrible things, UTF-8, and more. And I like the nuance behind Erik’s answer of this particular question. It’s easy to say “this thing is awful” and be done with it, but often times, the answer is more of “In this particular circumstance, don’t use this thing because of reasons X, Y, and Z; instead, use this thing.” That’s a rather different answer.

Comments closed

RIGHT OUTER JOIN and MERGE

Published 2025-03-14 by Kevin Feasel

Lukas Eder merges lanes:

It’s not unreasonable to expect these two statements to produce the same execution plan on most RDBMS, given that they’re logically equivalent. Since we’ve grown used to reading things from left to right and top to bottom, I don’t think RIGHT JOIN will become more popular any time soon.

There is, however, one place in the SQL language where RIGHT JOIN is surprisingly ubiquitous!

Read on to see how the MERGE operator includes as part of its operations the equivalent of a RIGHT OUTER JOIN. MERGE in SQL Server had justifiably built a bad reputation in its early years, but it’s surprisingly okay nowadays, with most of the bugs being fixed over time.

Comments closed

Alternative Means to Build a Tally Table

Published 2025-03-13 by Kevin Feasel

Steve Jones tries different methods:

We published an article recently at SQL Server Central on Tally Tables in Fabric from John Miner. In it he showed how this can be efficient. A day after he published it, he sent me an addendum to note that GENERATE_SERIES was available in Fabric and that could be used.

I ran a few tests last week, but as I read the comments on John’s article, I realized that there were 3 ways of setting up these tally tables that I’ve used and thought I’d summarize them a bit in this post. There’s a fourth way, but I haven’t used it.

My take on this is, it doesn’t really matter which technique you choose, especially if you’re using it to build a permanent tally table that you can later query from any app. In that case, it’s a one-time cost. If you’re building these on the fly often enough that the performance matters, then my first question, instead of “Which of these is fastest?” is, “Why can’t I just have a permanent tally table?”

Comments closed

The Order of Data Conversion and Aggregation Functions

Published 2025-03-12 by Kevin Feasel

I have a new video:

In this video, I demonstrate how order of operations matters when it comes to casting or converting a data type and performing an aggregation on that result. I’ll use the specific example of converting binary data to a number and show where the fast version might lead you astray.

This is something pretty easy to miss, especially when the code returns fast enough. But over a large enough number of calls, these sorts of things add up, as I note in the video.

Comments closed

Solving Linear Equations in SQL Server

Published 2025-03-11 by Kevin Feasel

Sebastiao Pereira implements a function:

Solving linear equations is essential for solving real-world problems in Science, Engineering, Data Analysis, Machine Learning, Economics, Finance, and other areas. Is it possible to have a tool to solve linear equations directly in SQL Server? We will look at how to create a Gauss-Seidel method function for SQL Server.

This is one way to solve a series of linear equations, and it’s a pretty neat implementation.

Comments closed