The Bit Bucket

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:

A storage failure occurs and during recovery, a data file from your database was lost.

Transaction log backups are taken every 5 minutes.

Periodically, when data is being loaded into the database, Bulk Logged mode is used. At the time of the failure, the database was still in bulk logged mode.

2025-07-31

When I am writing my own code using a .NET (or other) language, I have a great deal of control of how the connection string that my application uses to connect to SQL Server is configured.

In particular, I might need to add another parameter or two.

As a simple example, I might be working with a multi-subnet Availability Group, spread across a production site and a disaster recovery site. It’s common to then have an Availability Group Listener in both subnets.

2025-07-30

I posted a while back about moving to using Hugo-based Azure Static Websites for my blog and other simple sites.

One of the first things I noticed when my site was deployed, was that there were Hugo credits in the footer of the site. While that’s not necessarily a bad thing, I wanted to remove them. I went looking in all the places that I thought I could change to override this, to no avail.

2025-07-29

In previous posts, I’ve been talking about how to use snippets in SSMS and how to create your own. There are several types of snippets and one of the special types of snippets that I want to mention are the “surround with” snippets.

If you look at the following block of code:

Imagine that you want to execute the four highlighted lines only when a condition is true. If I hit Ctrl-K and Ctrl-S while they are highlighted, I’m prompted with this:

2025-07-28

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: Development Level: Medium

Question:

You are reviewing T-SQL code and in one query, you find the following:

OPTION (FORCE ORDER)

What is the point of this code, and is it likely to be helpful?

Answer:

FORCE ORDER tells SQL Server to join the tables that are involved in a query, in the order that they are written in the query.

2025-07-27

In an earlier post, I showed how to create a DROP DATABASE template in SSMS. I mentioned that a template wasn’t the best option because a command like this is normally inserted into a script; it’s not the whole script.

That’s where snippets shine. Let’s create a snippet for it.

First let’s open Code Snippets Manager (Tools > Code Snippets Manager):

You’ll see the existing snippet folders. I’ve clicked Add, then created a new folder called GL_Snippets. I created a new folder and pointed to it. For this temporary one, I’ve created it in C:\Temp but you would normally use an appropriate folder.

2025-07-26

I get a large volume of email every day. Part of that is always quite a lot of spam.

Now I don’t begrudge someone trying to make a living, and I understand that sometimes, they’re going to send me something unexpected.

But there are a few trends that annoy me.

The incorrectly targeted email

There’s a company out there selling access to email lists that has my company listed as a recruitment company. We’re not. I won’t shame this company by naming them, at least not today.

2025-07-25

Have you ever started to create an object using T-SQL in SQL Server, and thought what’s the right syntax for this?

I’ve worked with SQL Server since 1992 (version 4.2) and yet almost every time I go to create a function, I have to spend a few moments thinking about what the correct syntax is, because there are different types of functions (scalar vs table-valued, inline vs multi-statement).

SSMS has had templates for a long time, and they are useful. In fact, a few days ago, I wrote about how you can create your own.

2025-07-24

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: Security Level: Medium

Question:

You have code in stored procedures that records which users have called the procedures. The stored procedures are defined as:

WITH EXECUTE AS OWNER

You find that when you log the caller of the procedure, you get the owner of the procedure, rather than the caller.

2025-07-23

In another post, I mentioned how useful templates can be. I said that I’d discuss how to create them later. Well, that’s today.

I thought I’d take dropping a database as an example. The supplied template doesn’t work for me, so let’s create a new one.

Note: SQL Server 2016 SP1 introduced DROP DATABASE IF EXISTS but I find that option quite useless. It fails if anyone is connected to the database. And to disconnect people beforehand, you need to first check if it exists, so the statement is pointless.

2025-07-22