Sql-Server

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

Every now and again, I come back to my laptop and find that it has rebooted for some reason, while I wasn’t expecting it. A prime cause of that is Windows Updates. I really, really wish that wasn’t so, but someone at Microsoft has decided that I must apply these updates. I have very little control over the time when that occurs. For example, if I’m on the road delivering presentations, there’s no “wait till I get home” option for Windows Updates.

2025-09-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 are reviewing a database design from one of your developers.

He normally works with PostgreSQL and in that language, he uses the text data type for all strings.

When creating the database in SQL Server, he has used the varchar(max) data type for all strings.

2025-09-17