Press "Enter" to skip to content

Category: T-SQL

Natural Language Querying in SQL Server

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.

Leave a Comment

Concatenation and UNISTR in SQL Server

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.

Leave a Comment

DBSCAN in SQL Server

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.

Leave a Comment

Regular Expression Counts and Positions in SQL Server 2025

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

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

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

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

K-Means Clustering in SQL Server

Sebastiao Pereira implements k-means clustering in T-SQL:

K-means clustering is an unsupervised machine learning algorithm used to group data into k distinct clusters based on their similarity, allowing for customer segmentation, anomaly detection, trend analysis, etc. The most common machine learning tutorials focus on Python or R. Normally, data is stored in SQL Server, and it is necessary to move data out of the database to apply clustering algorithms and then, if necessary, to update the original data with the cluster numbers. Is it possible to do it directly in SQL Server?

Given the work you have to do to implement this, I can’t imagine that it would be particularly fast. But it is neat to see that it’s possible.

Comments closed

Sparse Columns and Space Utilization

Steve Jones gins up a demo:

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.

Comments closed

Window Functions in SQL Server

I have a new video:

In 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.

Comments closed