Press "Enter" to skip to content

Category: T-SQL

Time Zone Conversions in SQL Server

Published 2025-09-25 by Kevin Feasel

Louis Davidson knows what time it is:

Recently a topic came up at work, and I remember seeing it on Reddit, so you know, that definitely is a sign that I had to write a blog on it.

For a lot of people, we typically standardize on a single time zone for our life, and in turn our data. Maybe it is the time zone of our headquarters, our house, and sometimes it is the standard time zone UTC. It is often suggested that every organization should use UTC, and that is a great suggestion for many things.

However…if you store all your times as UTC, this becomes a nightmare for some kinds of reporting.

Click through for a primer on the capabilities of the DATETIMEOFFSET data type and some of the functionality you can use with it. And Louis hits on one important note around daylight savings time changes: the date changes over the years. Another is that what’s in the Windows or Linux time zone database isn’t always historically accurate. For example, in 1974 and 1975, the US extended DST and began it in January and Feburary, respectively. But if you use Louis’s query, it claims DST started in April, which would have followed the 1966-1973 and 1976-2006 patterns but is historically inaccurate. You’ll find these sorts of things for a variety of countries because Daylight Savings Time adherence is kind of wild.

Comments closed

More Types of Window Functions in SQL Server

Published 2025-09-24 by Kevin Feasel

I continue a series on window functions:

In this video, I continue a dive into each category of window function, quickly reviewing the four categories of window function (plus ordered set functions). Then, I cover offset window functions, statistical window functions, and ordered set functions.

This video includes some of the window functions I use most often (LAG(), LEAD()), some of the window functions I use least often, and even a few ordered set functions to boot. Combined, it’s about 45 minutes of content between this video and the prior.

Comments closed

Grouping Options in T-SQL

Published 2025-09-24 by Kevin Feasel

Chad Callihan rolls up the data:

When learning T-SQL, I’d wager that learning GROUP BY comes up early in the process. What may not be mentioned are the variations that can be added to a GROUP BY clause. Are you familiar with GROUP BY GROUPING SETS, GROUP BY ROLLUP, and GROUP BY CUBE? If you’ve never seen these used, or if you have and want a refresher, read on as we look at an example of each.

Of the three, CUBE is the one that I’ve used the least. I’ve found good instances where ROLLUP gives me exactly what I want for reporting purposes, and GROUPING SETS is powerful enough that I’ve made use of it a fair number of times. But CUBE just returns back too many combinations for what I’ve needed.

Comments closed

Natural Language Querying in SQL Server

Published 2025-09-23 by Kevin Feasel

Hadi Fadlallah shells out to an API:

Data is usually the most important asset in organizations, but only SQL developers can frequently access that data. Technical teams often write queries for non-technical users. This restricts agility, slows decision-making, and creates a bottleneck in data accessibility. One possible remedy is natural language processing (NLP), which enables users to ask questions in simple English and receive answers without knowing any code. Still, the majority of NLP-to-SQL solutions are cloud-based, which raises issues with cost and privacy.

This particular solution has nothing to do with the embedding features in SQL Server 2025. Instead, it essentially shells out to an Ollama API and runs the resulting SQL query. It’s reasonably neat but I’d have so many qualms putting anything like this into production.

Comments closed

Concatenation and UNISTR in SQL Server

Published 2025-09-22 by Kevin Feasel

Koen Verbeeck takes a peek at a pair of new features in SQL Server 2025:

Often new T-SQL functionality is released first in the Azure cloud environment, before it makes its way to the on-premises SQL Server product. Recently, a new T-SQL function and operator went general available in Azure SQL DB (and Azure SQL Managed Instance): UNISTR and the ANSI SQL string concat operator (||). In this tip, we’ll introduce you to both concepts.

Koen looks at these in Azure SQL Database, though if you have the public preview of SQL Server 2025, you can try both out there as well.

Comments closed

DBSCAN in SQL Server

Published 2025-09-19 by Kevin Feasel

Sebastiao Pereira is a mad lad and I love it:

Is it possible to have the DBSCAN algorithm in SQL Server without the use of external tools? If so, can you please provide a working example?

DBSCAN is a neat algorithm for clustering and it is reasonably popular in the literature. I cannot imagine that it would perform well at all in SQL Server on a large dataset, though in fairness, I did try out the Mail_Customers example Sebastiao noted. This dataset includes 196 rows after you eliminate four duplicate combinations of annual income and spending score, and the procedure returned in less than a second. Now, getting the execution plan for this took a while, but it was neat to see this working.

Comments closed

Regular Expression Counts and Positions in SQL Server 2025

Published 2025-09-18 by Kevin Feasel

Louis Davidson wraps up a series on regular expressions:

I am only combining them into a short version because they are, in how they work, very similar to all the other functions. I certainly will demonstrate all the functionality for each function, but not to the extra level I have in previous blogs.

This time, I will cover:

  • REGEXP_INSTR Returns the starting or ending position of the matched substring, depending on the option supplied.
  • REGEXP_COUNT Returns a count of the number of times that regex pattern occurs in a string.

Read on to see how these work in SQL Server 2025.

Comments closed

Types of Window Functions in SQL Server

Published 2025-09-17 by Kevin Feasel

I have the first of a two-part video up:

In this video, I walk through four categories of window function, plus a somewhat-related type of function in SQL Server. From there, I demonstrate how aggregate window functions and ranking window functions work.

The second part will come out next week and will cover the other types of window function. Otherwise, this was shaping up to be a 40-minute video and that’s a bit too long.

Comments closed

Performing Data Validation with RegEx in SQL Server 2025

Published 2025-09-15 by Kevin Feasel

Reitse Eskens tries out regular expression support in SQL Server 2025:

One of the new features in SQL Server 2025 is that you can now use regular expressions directly in your T-SQL queries. Now, regular expressions (or RegEx) have never been a syntax that’s easy to read. There are a lot of brackets, dashes and other symbols that make no sense when you first see them. Before delving into how these can be used in SQL Server, a few basics are provided to get you started, along with a link to a website for further learning.

Read on for a quick primer and a bit of pain when it comes to performance.

Comments closed

Substring Search with Regular Expressions in SQL Server

Published 2025-09-11 by Kevin Feasel

Louis Davidson continues a series on regular expressions:

The REGEXP_SUBSTR function extracts parts of a string based on a regular expression pattern. It has some similarieties with the SUBSTRING function, 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.

Comments closed