Sql-Server

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

Most SQL Server developers are aware that the EXEC statement can be used to:

• Execute a stored procedure (system, user-defined, extended)
• Execute some dynamic SQL

And most understand that you can SELECT from a scalar user-defined function.

But the option that many people don’t seem to be aware of, is that you can also use EXEC to call a scalar function.

I remember noticing this in the documentation for the EXEC command some years back. Prior to that, it had never dawned on me that you could use EXEC to call a scalar UDF. It’s also in the oldest documentation that I was able to check, so I’d say it’s worked for a long time.

2026-04-12

The WITH EXECUTE AS clause was a great addition for defining stored procedures and functions, to change the execution context, just for the duration of the stored procedure or function. For example:

CREATE PROC SomeSchema.SomeProc
WITH EXECUTE AS USER = 'Fred'
AS
... 

Mostly I use this with the OWNER option:

CREATE PROC SomeSchema.SomeProc
WITH EXECUTE AS OWNER
AS
... 

It’s also useful during testing, where I can temporarily change my execution context during testing. For example:

EXEC AS USER = 'Fred';

\-- Try some code here while running as Fred

REVERT;

But the option that most people don’t realize is possible, is that you can set the execution context for a single execution like this:

2026-04-10

It’s common advice that most SQL Server tables should have a clustered index. There are some exceptions to this but it’s a pretty general rule, and if in doubt, you should follow it. (Note that this is not the same as having a primary key).

I regularly come across tables without clustered indexes for all the wrong reasons. So, in our free SDU Tools for developers and DBAs, we added a tool that can look for user tables that don’t have a clustered index. No surprise, it’s called ListUserHeapTables because a table without a clustered index is a heap.

2026-04-08

Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. Version 27 adds the first set of views and functions for working with Nepali dates. These are useful in Nepal and in a number of Buddhist-related areas.

The first tool added is a view called NepaliMonths. It returns the Nepali names for months. You can see it in the main image above.

2026-04-06

Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. The TokenSetSimilarity function calculates token set similarity for two strings.

It answers the question: Do these two strings contain mostly the same words, even if the order, spacing, or repetition differs?

It is useful where word order varies, or extra or missing words are common. It can also help where character-level typos are less important than the presence of words.

2026-04-04