Press "Enter" to skip to content

Category: T-SQL

A Deep Dive into IDENTITY Columns

Published 2025-07-24 by Kevin Feasel

Vlad Drumea performs a deep dive:

In SQL Server, IDENTITY is a column-level property that is used to provide an auto-incremented value for every new row inserted.

All you have to do is provide a seed value and an increment value when defining said column, and SQL Server will handle it from there.

Unlike sequences, identity columns do not require additional objects like default constraints or triggers to ensure the column is populated.

I’m glad that Vlad made a demo showing how @@IDENTITY works and how it can give you unexpected outputs if you’re not aware of a trigger working with a separate identity column. That one tends to get people.

Comments closed

The PRODUCT() Function in SQL Server 2025

Published 2025-07-22 by Kevin Feasel

Ed Pollack points out a new function:

With each version of SQL Server, there are always a few new features introduced that we applaud as we finally have access to a useful function that is already available elsewhere.

Introduced in SQL Server 2025 CTP 1.3, the PRODUCT() function acts similarly to SUM(), but multiplies values rather than adds them. It is an aggregate function in SQL Server and therefore operates on a data set, rather than on scalar values.

Ed notes that there are aggregate and window function versions of PRODUCT() and shows examples of how it works.

Comments closed

Grouping Sets in T-SQL

Published 2025-07-21 by Kevin Feasel

Erik Darling has a new video.

Erik mentions that he doesn’t often see GROUPING SETS in the wild. I’ve used them several times. And the use of the term “several times” probably gives you exactly the feeling that I intended. I really like grouping sets for very specific analytical system purposes (at least for moderate-sized datasets), so I’m glad that syntax is there. But outside of reporting queries, it’s a really uncommon bit of syntax.

Comments closed

Percentage Splits with Window Functions

Published 2025-07-21 by Kevin Feasel

Andy Brownsword breaks things up:

Sometimes you want to segment records. It may be splitting a customer base for marketing purposes, or segmenting a user base for a new feature. Good segmentation makes clean divisions in the data.

In this post we’ll see a way to achieve that with a great deal of help from Window Functions.

Click through for Andy’s motivation, which is a way that absolutely will not work the way you want it to.

Comments closed

Ways to Debug T-SQL Scripts

Published 2025-07-16 by Kevin Feasel

Simon Frazer shares some tips:

At some point, every SQL developer or DBA will need to debug T-SQL scripts, either to verify that they behave as expected or to track down the root cause of a problem. Whether you’re building something new or investigating a production issue, debugging is an essential part of the process.

There are several techniques available for troubleshooting, and it’s important to approach this differently depending on whether you’re working in a production or non-production environment. Each environment has its own risks and constraints.

Click through for Simon’s process. I also echo Simon’s sentiments at the end regarding the SSMS debugger—I know people who are passionate about it and mourn its passing, but I was never one of those people. It was far too easy to get in trouble with it, especially in shared environments.

Comments closed

Regular Expressions in SQL Server 2025

Published 2025-07-15 by Kevin Feasel

Ed Pollack digs into some new functionality:

String-searching in SQL Server has always been a mighty hassle. Balancing performance and horribly-complex queries is a compromise that no one enjoys. 

Generally speaking, a relational database is not an ideal place to search large amounts of text. Even when leveraging features such as Full-Text Indexing, the ability for an application to leverage speedy text-searching decreases as data becomes larger. If a service optimized for text-search can be used, such as Elasticsearch or Azure AI Search, then it will be far easier to deliver accurate results quickly. 

Ed focuses on the mechanisms available rather than performance, and that’s the current sticking point. Whether regular expression queries will get faster in subsequent CTPs or SQL Server 2025 RTM, we’ll see.

Comments closed

Upsert Patterns and Duplicate Keys in T-SQL

Published 2025-06-30 by Kevin Feasel

Ajay Dwivedi runs into an error:

I work with developers quite a lot. There are scenarios where data is received from various sources in an application, and asynchronously pushed to database in multiple sessions/connections.

There are situations where concurrency and transaction speed is high enough to cause below UPSERT code blocks to fail with error message like Cannot insert duplicate key row in object dbo.person with unique index ‘pk_person’

Click through for one way to do things. I will note that Ajay has concerns about the MERGE operator, but Hugo Kornelis took a deep dive into all of the known problems in MERGE and found that most of them were fixed. Hugo’s post does make it clear when using MERGE is a bad idea, as there are still some situations in which it won’t work effectively, but for something like this, it would be fine.

I’ll say that I’m generally not a fan of app locking. There are specific circumstances in which it’s the best answer, but those are rare. Here, I’d rather just use a ROWLOCK table hint or change the serialization level.

Comments closed

The Costs of Implicit Conversion

Published 2025-06-27 by Kevin Feasel

Andy Brownsword changes things around:

Implicit conversion happens in SQL Server when the engine detects a mismatch in data types and automatically converts from one type to another. This can be helpful as it makes different types interchangeable and is generally transparent to the client, but it can come with issues.

Usually the downside from implicit conversion is seen through performance impact. But we’ve got something a little different today – an inconsistent result set.

Read on for Andy’s example.

Comments closed

Inline Scalar UDFs in Microsoft Fabric Warehouse

Published 2025-06-24 by Kevin Feasel

Srdjan Martin has an announcement:

SQL native Scalar user-defined functions (UDFs) in Microsoft Fabric Warehouse and SQL analytics endpoint are now in preview.

A scalar UDF is a custom code implemented in T-SQL that accepts parameters, performs an action such as complex calculation, and returns a result of that action as a single value.

I wonder if the same advice for SQL Server will apply to these: don’t use them because they are performance sinks. Knowing that these are scalar functions, I’d be inclined to do some considerable performance testing before rolling them out.

Comments closed

Building a Better Trigger

Published 2025-06-19 by Kevin Feasel

Steve Jones is done with mousetraps:

This seemed to work, but did it really?

The Problem

Let’s illustrate the big problem with this change. I’ll run this code:

Steve hit on one of the problems in trigger design, but there’s another problem that we often see. Steve’s code doesn’t have this problem, but quite often, people assume that rows update one at a time. But if you write a T-SQL UPDATE statement that affects five rows, the trigger fires once for all five. Steve correctly uses the inserted and deleted pseudo-tables to handle all changes, rather than just assuming a single row.

Comments closed