Category: Syntax

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.

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.

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.

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.

I have a new video:

In this video, I show how we can use the APPLY operator to reshape datasets, allowing us to unpivot tables and also calculate the greatest and least values for a row.

If you look closely at the scripts, you’ll see 08 and 10. In the source control repo, I also have a script 09 that covers splitting strings. Using APPLY to split strings has always been a bit of a niche case, but prior to SQL Server 2016’s introduction of STRING_SPLIT() and SQL Server 2022’s improvement of the function, I could make the case that it sometimes made sense to know how to split strings via APPLY. Today, not so much, which is why I tossed that demo from the video.