The Bit Bucket

SQL Interview: 68 Converting to local time zones

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 working with an Azure SQL Database. When you display dates and times, you notice that they do not match your local time zone of CEST.

Can you change the server settings to Central European Standard Time (CEST)?

2025-08-04

SSMS Tips and Tricks 4-3: Changing connections in a query window

I commonly run into a few connection-related scenarios:

  • I’m working on a large query and need to run it against several servers, not concurrently, but one after the other.
  • I’ve just made a database connection, and got my query ready, only to discover that I’ve connected to the wrong server.

Either way, what I’ve seen people do in these scenarios is to:

  • Select all the text in the current query
  • Copy it
  • Open a new query window
  • Paste the code

That’s all good but SSMS has had a simpler way of doing this for quite a while.

2025-08-03

SQL: There is no default output order for SQL queries

There is a really common misconception about output order for T-SQL queries. I can’t tell you how often I’ve heard people say that if you don’t specify an ORDER BY clause, then you’ll see the rows returned in the order of the primary key.

The assumption they’re also making is that the primary key is the clustering key. And that’s not necessarily true either. You can have a non-clustered primary key on a table.

2025-08-02

SSMS Tips and Tricks 4-2: Using colors to avoid running scripts against the wrong server

Everyone who’s worked with SQL Server for any length of time, has had the experience of executing a T-SQL script, and then noticing, with horror, that they’ve just executed the script against the wrong server.

You know the feeling. It even happens at Christmas time, just when you were hoping to get away from work for a few days, or when you are the unlucky one who’s doing on call work.

2025-08-01

SQL Interview: 67 Backing up transaction log after DB failure

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

SSMS Tips and Tricks 4-1: Adding additional parameters to connections

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

General: Removing Hugo footer credits in Azure Static Websites

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

SSMS Tips and Tricks 3-16: Using surround-with snippets

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

SQL Interview: 66 Using FORCE ORDER

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

SSMS Tips and Tricks 3-15: Using snippets to improve the DROP DATABASE statement

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