Sql-Server

We work with many customers that are moving some of their applications to cloud-based systems, and mostly on Azure. One message that I often hear about using Azure SQL Database (ASD) is that all you need to do is point your application’s connection string to the cloud and all will be good. While there are occasional cases where that is true, that generally isn’t going to give you a great outcome. And it’s generally very misleading. To really get a great outcome, you generally will need to check out how your application has been designed.

2026-05-18

I have days where I can’t decide if I’m frustrated or sad about how I see SQL Server being used by applications, or if I’m happy that this keeps us in ongoing work.

Recently, I was looking at a system that was having performance issues. There were three key applications on the system. Each came from a different vendor and when I looked at how each one of them interacts with the server, it was just really frustrating.

2026-05-16

The default action when performing a backup is to append to the backup file yet the default action when restoring a backup is to restore just the first file. This has never made sense to me.

Apparent loss of data ?

I constantly come across customer situations where they are puzzled that they seem to have lost data after they have completed a restore. Invariably, it’s just that they haven’t restored all the backups contained within a single OS file.

2026-05-10

I’ve recently been writing about the need for stored procedures to have contracts . Temporary tables add another dimension to that discussion.

Tempory tables are visible within the scope where they are declared but also in sub-scopes. This means that you can declare a temp table in one stored procedure but access it in another stored procedure that is executed from within the first stored procedure.

There are two reasons that people do this. One reason is basically sloppy code, a bit like having all your variables global in a high level programming language. But the more appropriate reason is to avoid the overhead of moving large amounts of data around, and because we only have READONLY table valued parameters.

2026-05-08

A few days ago, I wrote about SQL CLR and how I don’t normally use it now, but if I did, which types of objects make sense for it. I briefly mentioned user-defined data types but today, I wanted to call out another limitation of these that I’d like to see addressed (if we keep on using SQL CLR).

Early versions of the user-defined data types in SQL CLR had a limitation on size, where they needed to be serializable within 8KB. That limit is now long gone and so the ability to define new data types using SQL CLR integration was now almost at a usable level, apart from one key omission: indexes.

2026-05-06

It was great to catch up with Jess Pomfret today and to have her on a SQL Down Under podcast.

Jess is a Data Platform Engineer and a dual Microsoft MVP. She started working with SQL Server in 2011, and she says she enjoys the problem-solving aspects of automating processes with PowerShell.

Jess also enjoys contributing to dbatools and dbachecks, two open source PowerShell modules that aid DBAs with automating the management of SQL Server instances.

2026-05-05

I’ve recently been talking to clients about SQL CLR objects. When these were first introduced in SQL Server 2005, many of us had high hopes for them. SQL Server has never been great in regard to extensibility and this provided some way to extend the product.

Nowadays, I avoid SQL CLR. And that’s a real pity. But it’s no longer supported in Azure SQL Database, apart from the system CLR objects of geometry, geography, and hierarchyid. (Note: I’m also not a fan of hierarchyid). I need to use extensibility methods that are available in the different environments that I work in, and Azure SQL Database is one of those. The same applies to Fabric SQL Database.

2026-05-04

Increasingly, developers are using tools that try to automate code generation when dealing with databases. Stored procedures have been a thorn in the side of this. Mostly that’s because it’s difficult to obtain the metadata that is really needed.

You should not need to be able to read the code of a stored procedure to know how to use it. And that includes the common exceptions it might throw. None of the existing tools for dealing with this currently do what’s needed.

2026-04-30

On a client site some years back, I came across a situation (unfortunately too common) where a column in a table was being used for two purposes. It could either hold an integer value or a string. Only about 100 rows out of many millions had the integer value. Some of the client code needed to calculate the maximum value when it was an integer. First step I tried was to add a persisted calculated column like so:

2026-04-24

I fell for this one this week. If you execute the following code in SQLCMD mode, what would you expect the output to be?

:SETVAR PrincipalServer WINSERVERBASE
:SETVAR MirrorServer WINSERVERBASE\\SQLDEV02
:SETVAR WitnessServer WINSERVERBASE\\SQLDEV03

:CONNECT $(PrincipalServer)
SELECT @@SERVERNAME;

:CONNECT $(MirrorServer)
SELECT @@SERVERNAME;

:CONNECT $(WitnessServer)
SELECT @@SERVERNAME;

I’m guessing you might not have expected:

WINSERVERBASE\SQLDEV03

WINSERVERBASE\SQLDEV03

WINSERVERBASE\SQLDEV03

The problem is the lack of a batch separator. What I should have written was this:

:SETVAR PrincipalServer WINSERVERBASE
:SETVAR MirrorServer WINSERVERBASE\\SQLDEV02
:SETVAR WitnessServer WINSERVERBASE\\SQLDEV03

:CONNECT $(PrincipalServer)
SELECT @@SERVERNAME;
GO

:CONNECT $(MirrorServer)
SELECT @@SERVERNAME;
GO

:CONNECT $(WitnessServer)
SELECT @@SERVERNAME;
GO

While this may be strictly correct, I can’t imagine it’s the behaviour anyone would wish for. Do you think that a :CONNECT statement in a SQLCMD batch should also be treated as a batch separator? Does it ever make sense for it not to?

2026-04-22