Sql-Server

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

This is the eighth 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-01

Just a heads-up that v26 of SDU Tools went out to SDU Insiders last week. If you haven’t used SDU Tools, they are just a large library of functions implemented in T-SQL. You can use them as a full library or use them as examples of code in work you are trying to do.

Fabric SQL Database Release

The main reason for this release (apart from the standard list updates for SQL Server versions, etc.) is to introduce a new target for SDU Tools. When you download v26, you’ll notice there are now three installation scripts, and three removal scripts. So, there are now versions for:

2025-09-30

SSMS is a great tool for working with SQL Server relational databases but it can do much more than that.

In Object Explorer, note that you can easily connect to other types of services:

For a long time, it has been able to connect to Analysis Services to manage SSAS databases, both tabular and multi-dimensional. It can connect to Integration Services but that’s to the older style interface for SSIS. Nowadays, you should use the SSIS Catalog instead. There are a few items that you can configure via the Reporting Services connection as well.

2025-09-30

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

SELECT ProductID,
       UnitPrice / OuterPrice AS PriceRatio
FROM dbo.Products;

When OuterPrice is zero, an error is returned. What is the simplest way to change the query to return NULL for PriceRatio if OuterPrice is zero, without wrapping the expression in a CASE statement?

2025-09-29