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

Question:

You are managing a SQL Server 2022 server and have enabled Instant File Initialization (IFI).

Which files will this apply to?

• Data (MDF and NDF) files only
• Transaction log files only
• Both data and transaction log files

Answer:

2025-10-11

Like Visual Studio that it’s based upon, SSMS is very flexible when working with query windows and tabs.

Most people realize that you can undock and move tabs and windows around. Usually they discover that by accident and then realize that the Reset Window Layout option in the Window menu is helpful.

But one option I’ve found that many people don’t seem to realize is that you can undock just a single query window and move it outside the bounds of SSMS. You can even place it across on another screen if you have multiple screens.

2025-10-10

This is the tenth tale in a series of stories about data. I hope you enjoy the series.

I regularly see code that calculates a count of a set of rows then makes a decision that’s based on the count, but only on whether or not the count is zero. Consider this (slightly contrived) example based on AdventureWorks:

DECLARE @PersonCount int;

SELECT @PersonCount = COUNT(*)
FROM Person.Password
WHERE BusinessEntityID = 12
AND PasswordHash = 0x208394209302;

IF @PersonCount > 0
BEGIN
    PRINT N'Password matches';
END;

Now I’ll start by saying that the last thing I’d ever want to see in SQL Server code is this sort of playing around with users and passwords, but I’m ignoring that for now.

2025-10-09

In a previous post, I showed how you might use split windows to allow you to work on different parts of a single query at the same time.

But what if you need to work on two queries and see parts of both of them?

That’s where document groups can help you. You can create both vertical and horizontal groups. For me, the most useful is typically side-by-side vertically, for when I’m comparing two sections of code.

2025-10-08

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 your development standards and notice that there is a warning about making sure you don’t nest views.

Why would this be an issue?

Answer:

Nested views hide complexity, and worse, the SQL Server optimizer might not be able to eliminate all the tables from the query that it could otherwise have done. This can lead to serious performance issues that could have been avoided.

2025-10-07

I recently had to install SQL Server Management Studio (SSMS) offline on a virtual machine. The machine was running Windows Server 2022.

I struggled for many hours, and came to the conclusion that this should not be as difficult as it was. I hope this information helps someone else.

I followed the instructions provided for offline installation from here: Create an Offline Installation of SQL Server Management Studio . Erin Stellato did a good job in that article and that almost could have worked but there were a few issues.

2025-10-06

This is the ninth tale in a series of stories about data. I hope you enjoy the series.

In the first article we saw why size really does matter, both for the DB itself and all the other copies of it that exist in most organizations. We then saw how to estimate the improvement in size with ROW compression. Our customer’s database that started at 3.8TB was reduced to 2.6TB by applying ROW compression without any code changes. Better still the performance of the I/O bound application improved significantly by compressing the tables and indexes.

2025-10-05

One of the problems with applications that have highly-configurable user interfaces (UI) is that users can end up configuring them in ways they hadn’t intended, and then don’t know how to get back to where they were.

I remember the first time that I was at a session with a presenter from Microsoft showing the (at the time) new personalization options in ASP.NET. You could build a website and let the user determine how the site should be laid out, to suit themselves.

2025-10-04

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

Question:

Why is it advisable to avoid storing user objects in the PRIMARY filegroup, and keeping it small?

Answer:

You want the PRIMARY filegroup as small as possible and well-backed up. If you cannot restore the PRIMARY filegroup, you cannot restore any other filegroups.

2025-10-03

When you get to a large number of query windows or other documents open as tabs in SSMS, it can start to be difficult to keep track of them, and to find them when needed.

It’s not too bad when you can immediately find the tab that you want in the drop-down list:

But if you have more tabs than are shown in this drop-down list or if, like me, you often end up with many of them without names (as they are temporary), it can get very hard to find the few that you are mainly referring to.

2025-10-02