Sql-Server

Day by day, we’re getting further away from needing to use 32-bit DLLs. One place where I keep coming across them, is when doing integration work. The problem is that there are many 32-bit drivers for older systems, and no-one is going to ever bother to write 64-bit drivers for them. So, if you have to use those drivers, you need to run the 32-bit versions. And SSIS has options that allow you to run 32-bit drivers.

2026-06-29

Two of the parameters in SQL Server connections are the Application Name and the Host Name. You can see these in SQL Server if you execute the following command:

SELECT HOST_NAME() AS HostName, APP_NAME() AS ApplicationName;

On my system, that returns:

I’ve always been a fan of having applications identify themselves in their connection strings. It makes tasks like tracing much easier. The tools supplied with SQL Server do a reasonable job of that as you can see above. But many other tools don’t do such a good job.

2026-06-27

One of the common challenges that arises in many applications is around how to optimize the SQL Server performance of dynamic search queries. For example, we’ve all seen applications with screens like this:

Often, there will be many, many more fields to be searched.

Perceived wisdom

What I often hear from developers is that there is no way to optimize the query because you don’t know in advance which options the user will choose. While this is partly true, it assumes that human behaviour is much more random than it really is.

2026-06-25

For general index maintenance, our friend Old Hallengren has an awesome solution for most people: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

We started to have customers using clustered columnstore indexes from SQL Server 2014, and they needed to be treated differently. At the time, I checked Ola’s latest scripts to see what happens with columnstore indexes.

It appeared that the code ignored nonclustered columnstore indexes (ie: index type of 6), which made sense as we would potentially need to rebuild them whenever the data changes, and at the time, the table was read-only. So that made lots of sense. Fortunately, that limitation is now gone.

2026-06-21

When I attend events like TechEd/Build/Ignite, like many people I usually find the networking time more valuable than the session time. There is a pretty tight limit on the number of sessions you can attend, no matter how hard you try. So I often watch the sessions later when I can.

At one of the earliest TechEd Australia events that I attended, they gave us DVDs of the TechEd USA sessions. That was great because that TechEd had around 250 sessions, and there were over 150 that I would have loved to attend. Clearly that wasn’t possible.

2026-06-19

I’ve ended up with a number of Microsoft figurines over the years. It all started with Nine Guy:

Then it went on to the whole Source Force team:

It was interesting to see how they evolved over the years. Here are the SQL Server ones:

SQL Server 2005 had a demure little lady that was all Red. She morphed into an orange version by SQL Server 2008 (shown in the header with me at TechEd USA) in 2008. Later she took on a Neo look from the Matrix. More recently, there was a guy, the Query Controller.

2026-06-17

I recently wrote about how SQL Server related tooling has been pushing us to use constraints like primary keys, check constraints, and unique constraints without naming them ourselves. But a similar issue applies to DEFAULT constraints.

With DEFAULT constraints, the issues are slightly different.

The first issue with them is the same as with the other constraints. They can cause havoc with deployment and schema-comparison tooling unless you find and use options to ignore any system-generated names.

2026-06-15

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