Sql-Server

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

I often work with reports either in SQL Server Reporting Services (SSRS or now Power BI Reporting Services) and Fabric Paginated Reports. At a site this week, the complexity of the reports we were working on reminded me that I really, really don’t like seeing T-SQL code (or really any complex business logic) embedded in reports.

Don’t make refactoring difficult or impossible

DBAs tend to be considered a conservative bunch. One thing they’re usually conservative about is refactoring their databases. In many cases, this is because they have little idea what they will break when they make database changes.

2026-04-18

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Advanced

Question:

You have a SQL Server server attached to multiple storage volumes. One of the volumes has been lost. It contained the files for the tempdb database.

You know that tempdb is recreated on each restart of SQL Server, but will SQL Server still start when the files are missing? Will it just automatically create new files? If not, how could you work around it?

2026-04-14