Sql-Server

If you have ever started SSMS in an isolated environment (ie: one with no external Internet connectivity), you’ll find that it’s slower to start.

That’s because SQL Server uses signed assemblies, and whenever an application with signed assemblies starts, it needs to check whether or not the certificate that they were signed with has been revoked. It’s not good enough to just check if it’s a valid certificate.

Certificates include a CRL (Certificate Revocation List) and this tells an application that’s trusting the certificate where to check for a list of revoked certificates.

2025-09-28

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

In the previous article I started discussing a large customer database that needed to go on a diet. We needed to drastically reduce the size of the database. I described why ROW compression was important, and showed how to estimate the savings from using it.

This month, we’ll start by looking at how ROW compression actually works, then look at the greater savings from PAGE compression. We’ll also look at how it works internally.

2025-09-27

I’m puzzled that so few people use script projects and solutions when working with SSMS.

They are easy to use. Let’s see an example:

Instead of just starting to create scripts, from the File menu, click New, then Project. You are greeted with the new Project dialog which also allows you to create a solution.

I’ve selected SQL Server Scripts as the project template and Next:

To get to the point faster, here’s one that I created earlier:

2025-09-26

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 execute the following code:

DROP TABLE IF EXISTS dbo.Table1;
GO

CREATE TABLE dbo.Table1
(
    Column1 int NULL
);
GO

INSERT dbo.Table1 (Column1)
VALUES (1), (NULL), (NULL), (2);
GO

-- Query 1
SELECT COUNT(*), COUNT(Column1)
FROM dbo.Table1;

-- Query 2
SELECT COUNT(*), COUNT(Column1)
FROM dbo.Table1
WHERE Column1 = NULL;

What values will be returned for each query?

2025-09-25

You don’t always want to run SSMS as your current login for Windows.

Now if all you want to do is to use a SQL Server login, then that’s easy. When you connect to a server in Object Explorer, or when you start a new Database Engine query, you can just choose SQL authentication instead.

But three other scenarios commonly occur.

If you need to run SSMS as an administrator on a machine with UAC, you can do this:

2025-09-24

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

Time for a diet

One of the busiest places to be in the new year is in any gym. So many people make new year resolutions and in so many cases, it involves losing weight. So I thought I’d start the new year with a tale of a database that needed to go on a diet. The client’s primary database started at 3.8 TB. This case will be described over a short series of articles. This is part 1 and describes phase 1 of the diet.

2025-09-23

Back when SSMS for SQL Server 2016 was released, a search tool called Quick Launch was added. It was this bar up the top of previous versions:

Note there was also another bar underneath it to the left. That was the Find bar. While the Find bar was useful for searching for text within queries, etc., the Quick Launch bar was useful for searching within SSMS itself. This was great because it means you don’t have to remember where all the options for various things are set. Here was an example:

2025-09-22

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 transactional code that does not appear to be working as expected. The following code is being executed:

BEGIN TRAN;

UPDATE Table1 SET Column1 = 12 WHERE Column2 = 14;
UPDATE Table2 SET Column3 = 15 WHERE Column4 = 99;

COMMIT TRAN;

The second update (for Table2) is failing with a foreign key violation, but the update to Table1 is not being rolled back.

2025-09-21

This one is a very simple and quick tip.

When working in SSMS, I often need to open Windows File Explorer in the folder where the script file is stored. Turns out there is an easy way to do that.

There are two interesting options when you right-click the tab at the top of a query window. (Note: not on the File menu)

Note that you can open the containing folder for the script. You can also copy the path to the script into the clipboard.

2025-09-20

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

3rd party applications and indexes

Most of my end-user clients run applications that are written by other companies ie: 3rd party applications. Very few write most of their own applications. One of the endless challenges with this is that the application vendors never want their clients to make any changes to the databases that are used by their applications. As a customer, it can be very painful to see performance problems caused by poor or inappropriate indexing choices, and yet to be blocked out from fixing those issues by supportability concerns.

2025-09-19