A Fine Slice Of SQL Server
Louis Davidson continues a series on regular expressions:
The
REGEXP_SUBSTRfunction extracts parts of a string based on a regular expression pattern. It has some similarieties with theSUBSTRINGfunction, but with some important (and interesting) differences. This function returns Nth occurrence of a substring that matches the regex pattern.
Read on to see how it compares to the traditional SUBSTRING() function.
I saw this as a question submitted at SQL Server Central, and wasn’t sure it was correct, but when I checked, I was surprised. If you choose to designate columns as sparse, but you have a lot of data, you can use more space.
This post looks at how things are stored and the impact if much of your data isn’t null.
I consider sparse columns a relic of the mid-aughts era, when storage was a lot more expensive and compression was an Enterprise Edition-only feature. Given that you can use page compression in any edition of SQL Server nowadays, I don’t think there’s a viable reason ever to have a sparse column.
Also, definitely check out the comments, where Jeff Moden has a great one.
Leave a CommentUmut Tekin picks up on a thread:
Recently, I read Laurenz Albe’s blog about case insensitive string search. He recommended case insensitive collations saying, “it won’t be any more expensive (and maybe more intuitive) than the current techniques of using
upper()or thecitextextension are today”. In other words, a custom collation can be used instead of citext to achieve case insensivity with a lower performance penalty. Today we will be testing it.
Click through for the results of those tests.
Leave a CommentIn this video (part 1 of a new series), I explain what a window function is, as well as the components of window functions.
It’s taken a couple of months for me to get back on the video production wagon. This video serves mostly as the classroom primer for what will be primarily a demo-heavy series.
Leave a CommentDeepak Mahto performs a search:
Case-insensitive search is one of the most common issues I encounter when helping customers migrate from Oracle or SQL Server to PostgreSQL. Unlike Oracle (with
NLS_SORT) or SQL Server (with case-insensitive collations), PostgreSQL does not natively support case-insensitive search.
Read on for a few workarounds, including one that Deepak recommends never using.
Leave a CommentLouis Davidson creates a table:
Continuing on with the
REGEXP_functions series, the next one I want to cover is the table valued functionREGEXP_SPLIT_TO_TABLE. This function is definitely one of the ones you probably ought to know, especially if you are ever tasked to pull some data out of a data structure.This function is a lot like the
STRING_SPLITfunction, and unlike things like theREGEXP_LIKEfunction, you can basically use the same main parameters as you used inSTRING_SPLITfor simple cases, but from there the possibilities are a lot more endless because you can define almost any delimiters you want. It isn’t perfect, because of a few things, but we will discuss that more later on.
Read on to see how it works, including one major caveat.
Leave a CommentLouis Davidson continues a series on regular expressions in SQL Server 2025:
Okay, we have gone through as much of the RegEx filtering as I think is a a part of the SQL Server 2025 implementation. Now it is time to focus on the functions that are not
REGEXP_LIKE. We have already talked aboutREGEXP_MATCHES, which will come in handy for the rest of the series.I will start with
REGEXP_REPLACE, which is like the typical SQLREPLACEfunction. But instead of replacing based on a static delimiter, it can be used to replace multiple (or a specific) value that matches the RegEx expression. All of my examples for this entry will simply use a variable with a value we are working on, so no need to create or load any objects.
Read on to see how it works, including plenty of examples.
Leave a CommentSairam Yeturi reduces ETL and ELT requirements:
Organizations often face challenges when trying to scale analytics across large volumes of data stored in centralized SQL databases. As business teams demand faster, more tailored insights, traditional reporting pipelines can become bottlenecks. By adopting Lakehouse architecture with Microsoft Fabric, business groups can mirror their SQL data into OneLake and organize it using the Medallion architecture—Bronze, Silver, and Gold layers. Materialized lake views play a crucial role in this setup, enabling automated, declarative transformations that clean and enrich data in the Silver layer. This empowers teams to build reliable dashboards and AI-driven insights on top of curated data, all while maintaining performance, governance, and security on a scale.
In this post, we will cover how enterprises can use materialized lake views to streamline data orchestration and enhance data quality, monitoring across silver and gold layers, while mirroring their SQL DB tables to Fabric in the Bronze layer.
The best use case for this is a scenario in which your underlying data is already essentially in a star schema or at least easily transformable into one, and you have no interest in modifying the data in the view directly. Do read the limitations before digging in, though, as there are some big ones.
Leave a CommentRick 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.
Leave a CommentErik 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.
Leave a Comment