Sql-Server

For many years, SQL Server related tooling has been pushing us to use constraints without naming them ourselves. But SQL Server requires these to have names so it generates names for them. These system-generated names aren’t very helpful.

What’s the issue with system-generated names?

The first issue with them is that they can cause havoc with deployment and schema-comparison tooling unless you find and use options to ignore any system-generated names.

2026-06-13

A question came up from a developer yesterday. He could see how to set a transaction isolation level but didn’t know how to determine the current transaction isolation level. That detail is available in the sys.dm_exec_sessions DMV.

Here’s an example:

SELECT COALESCE(CHOOSE(transaction_isolation_level,
                       'Read Uncommitted',
                       'Read Committed',
                       'Repeatable Read',
                       'Serializable',
                       'Snapshot'),
                'Unspecified') AS CurrentTransactionIsolationLevel
FROM sys.dm_exec_sessions  
WHERE session_id = @@SPID;

2026-06-09

I had a lot of good feedback on my recent post about how to query group membership for a given login.

One tricky additional question was about how you could let a specfic user be able to find the group membership for another login, without the user being a sysadmin to run the code.

Now doing that is a bit trickier but can be done by creating a certificate, a login from the certificate, then assigning permissions to that login, and finally applying a digital signature to the procedure using the certificate.

2026-06-07

BACKUP TO URL was introduced as an add-on in Cumulative Update 2 for SQL Server 2012 Service Pack 1 and as a built-in feature for SQL Server 2014. I’ve talked about it in previous blog posts.

We have been using this in a variety of ways from on-premises systems:

For example, it is an easy way to distribute a backup of a database to a large number of systems. Imagine you have a chain of retail stores that needs product and other reference information updated regularly. You can keep this data in a separate database at the head office, back it up to an Azure Storage account, and have each store download it separately.  This has major bandwidth and reliability improvements over other solutions such as having each store maintain a VPN connection to the head office.

2026-06-05

I saw a question on a SQL Server mailing list about how to determine the Windows groups for a given SQL Server login.

That’s actually easy for a sysadmin login, as they have IMPERSONATE permission for other logins/users.

Here is an example procedure:

USE tempdb;
GO

CREATE OR ALTER PROCEDURE dbo.GetGroupsForALogin
@LoginName sysname
AS
BEGIN
    EXECUTE AS Login = @LoginName;

    SELECT [name] AS GroupName,
           usage AS Usage
    FROM sys.login_token 
    WHERE [type] = N'WINDOWS GROUP';
END;
GO

When I executed this on one of my SQL Server 2025 systems this way:

2026-06-03

I often need to create a T-SQL script that reliably drops and recreates a database each time it is executed. A common requirement is that I’m building a script to test come concept and I want to just re-run a script to get back to a known point after cleaning up all that I’ve done before. I could even just want to create a database in a particular way, and I want to make sure it doesn’t already exist.

2026-05-28

I recently mentioned that the AI Edition of the Data Science Summit is coming in June and that this time, I’m presenting two sessions.

The second session is Calling Ollama Based Text Embeddings Models from SQL Server 2025, and I’ll be discussing how the AI features that have been added to SQL Server in 2025 can be used with text embeddings coming from Ollama-based text embeddings models running locally on your servers.

2026-05-27

It was great to catch up with Jerry Nixon today and to have him on a SQL Down Under podcast.

I recently had Jess Pomfret on a show, discussing Data API Builder. And the logical extension of that is to talk about SQL MCP Server.

Jerry is a Principal Product Manager for Data and AI at Microsoft.

He says he is focused on the developer persona to minimize onboarding friction and maximize value.

2026-05-26

Also in June this year, the AI Edition of the Data Science Summit is coming. And this time I’m presenting two sessions.

The first session is Using AI Features in SQL Server, and I’ll be discussing the AI features that have been added to SQL Server in 2025.

Use the code DSSAI26SP20 to get 20% off Standard or Exec passes.

See you at DSS!

Find more details about the event here: https://ml.dssconf.pl/

2026-05-25

One of the things I always tell people that I love about consulting/mentoring work is that you see things that you just can’t make up. They provide interesting material for training classes.

Recently, I came across something that I wasn’t expecting. I was migrating data from DB2 and in my scripts, I had made the presumption that a table would have at least one column. Turns out that in DB2 you can have a table with no columns.

2026-05-24