A Fine Slice Of SQL Server
Rick Dobson wants a measure of variation:
This tip presents a brief introduction to Common Table Expressions (CTE), along with a few references for those seeking additional details on CTEs beyond those described and demonstrated here. We will examine CTEs that are defined by either one or two SELECT statements. Additionally, we will provide a demonstration of a recursive CTE. All the examples illustrate how to process time series datasets with CTEs.
Click through for the tip.
Comments closedErik looks at a classic performance difference between ROWS and RANGE, as well as what batch mode does to even the score. This is particularly nice because ROWS and RANGE both have their utility and focusing on one versus the other for performance differences can lead to awkward development practices to get around a window spool.
Erik also focuses primarily on batch mode on rowstore, so keep in mind the minimum requirements for it: 131,072 (or 2^17) rows in at least one table in the query, at least one operator that benefits from batch mode (which we’d cover in the window function), at least one input of the batch with 2^17 rows, and where the batch mode cost is lower than the row mode cost.
Comments closedKoen Verbeeck writes a regular expression:
I need to do some data validation in our SQL Server database. However, the validation rules are too complex for the T-SQL LIKE function, and I can’t seem to get it done either with PATINDEX or something similar. I’d like to use regular expressions as they’re more powerful. SQL Server 2025 now has a regex function regexep_like to use regular expressions.
Read on for some examples, advice on validating e-mail addresses, and more.
Comments closedLouis Davidson continues a series on regular expressions in SQL Server:
There are currently only 4 flags that SQL Server supports and they are used to change some of the fundamental ways that the expressions are applied. These flags are:
i– insensitivec– case sensitivem–^and$match end of line, not entire strings– single line, dot matches newlineIn Part 6, I covered
iandc, no[w] let’s domands. These flags are not ones I expect to use all that often, but they are definitely useful to know.
Read on to see how they work, as well as some of the issues Louis ran into along the way.
Comments closedErik Darling walks through something that can slow down your temp table loads. The video covers a scenario in which a user creates a temp table, loads some data from it, and also wants to include an identity integer. Erik shows how this can lead to sub-optimal performance, as well as one alternative if you really do need a monotonically increasing value on your temp table.
Comments closedLouis Davidson continues a series on regular expressions:
In this week’s sixth entry of my learning RegEx series, I am going to do two last intro entries for a while, this one on case sensitivity, and another on multi and single line searches. After this I will move into all of the functions that are available in SQL Server 2025 and Azure SQL (and I will come back if I learn any additional things that we need to cover either right after that, or anytime I learn something new I want to share about RegEx).
Read on to see which flags SQL Server currently supports. Of those, Louis tries out a pair.
Comments closedLouis Davidson has popped and therefore cannot stop:
The goal of this week’s entry is specifically to show how to see how multiple matches can be viewed using SQL Server’s RegEx, specifically to make the examples clearer (especially in the upcoming entries).
There are several functions that you can use where multiple matches are used as part of the output:
Click through for that list and several examples of relevant functions in action.
Comments closedJoe Celko shares some thoughts on views, as well as recursive common table expressions:
VIEWs are an undervalued and underused feature in SQL. They basically consist of a query that has been given a name, and a parameter list, so can be used like an inline macro. Technically, you’re supposed to think of it as a virtual table.
The idea of an inline macro or inline function goes way back to the first versions of Fortran and later BASIC. Depending on the product, the syntax might look like this:
FN DOUBLE (N) = (N + N). You had to give the name of the Macro, a simple optional parameter list, assignment operator, usually an =, and the text of the computation. It was often important to put parentheses around the body of the macro, or to have a compiler that would do this for you.
Read on for the article and a platform-agnostic coverage of views. My problem with views is that developers try to use them to cover a lot of ruin and then they ask why such a simple SELECT * FROM Something query is so slow.
Aaron Bertrand splits a string:
SQL Server users have been asking for native regular expression support for over two decades. There are third-party Common Language Runtime (CLR) modules that offer this functionality, but these can be complicated to install and simply aren’t possible in some environments. I want to split a string using a regular expression instead of a static string. Will that be possible in SQL Server 2025, without CLR?
Must not rant about CLR. Must not rant about CLR. Must not rant about CLR. (By the way, if you ever catch me in person, get me going about how CLR got the short end of the stick and how the ‘modern’ forms of the Common Language Runtime in SQL Server are not great.)
Aaron tries out a function built into SQL Server that allows you to split strings into result sets using a regular expression to perform the splitting, and shows off some of the more complicated scenarios that this can solve over a normal STRING_SPLIT() function call.
Louis Davidson has a few blog posts for us to catch up on. So far, this is a four-part series on regular expressions and SQL Server.
Part 1 covers simple pattern matching:
I have never once written an regular expression prior to a couple of articles on this blog. And truth be told, when I published those blogs, I got the expression wrong because it seemed to work, and it was what Copilot told me would work. If you are new like me and/or your code is important, test with lots of cases. I obviously fixed that code (thankfully the conclusions were right).
So no, I have never.
LIKEdoes 99% of what I need in a simple manner, and .8% of the time in a complex way, so I never really thought about it too much. I suspect that will be the case even now in SQL, but like any good student, it is time to change my knowledge of regular expressions.
Part 2 covers repeating patterns:
In this blog, I want to look for strings that have 1 or more instances of a repeating pattern. For example, say you want to look for something like the following:
LIKE'%FredFredFred%' --(or any fixed or unlimited length of a, and only a) LIKE'%aaaaaaaaaaaaaaa%'or'%aaaaaaa%'
Part 3 looks at matching sets of characters:
In this article, we are going to take an initial look at what are referred to as “character classes” or “character sets” in Regular Expressions. They are commonly used when looking for data to be in a certain format. For example:
We are going to look at how to set a filter for
'lll-ll-lln'and/or'lll-ll-lll'(where l is letter and n is numeric).
And part 4 deals with negation:
In Part 3, I covered some of the basics of using character classes/sets. (I do tend to say sets.) This allowed us to do things like find words that start with a, b, c, d or e. This is done using:
^[a-e]or^[abcde]. Now I want to look at two new things (one of which looks really similar to the previous classes but does things very differently.:
- Negated character classes – Look for strings that don’t have a particular character in them
- Perl character classes – shorthand for certain types of characters
Regular expressions can be very challenging to learn and even more challenging to troubleshoot and ensure there are no missing corner cases. But they offer an enormous amount of power and that makes it all worthwhile.
Comments closed