Sql-Server

Execution plans are one of the most valuable tools in SQL Server for understanding how a query is executed. But even experienced developers and DBAs regularly misinterpret certain operators. Some of these operators look deceptively simple, others have subtle side effects, and a few behave differently depending on cardinality estimates or memory availability.

This post walks through the execution plan operators that are most frequently misunderstood, what they actually mean, and how to interpret them correctly.

2025-12-24

Upcoming processors are likely to have even more cores than now. Have you ever tried to write multiprocessor-style code? A friend of mine recently said that he learned some of this style of coding but later when he came back to it, he realised how much he thought he knew but didn’t.

For languages like T-SQL, we don’t have inherent support for multi-threading. In fact, the only trace I can see of this in T-SQL today is the ability to have multiple readers on a service broker queue.

2025-12-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 writing a query that involves inner joins across five tables. Does the order of the tables within the query affect the query plan?

Answer:

No. SQL Server determines the best order for you to join the tables in.

2025-12-21

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:

Most information about each database is stored within the database.

Can you list three types of database information that live in the master database?

Answer:

Pick three of the following:

• Database IDs, names, creation date
• Compatibility level
• Collation
• Recovery model
• Status (ONLINE, RESTORING, SUSPECT)
• File locations and size metadata (from sys.master_files)
• Ownership (database owner)
• Availability Group membership
• Replication information
• Database GUIDs, resource IDs

2025-12-20

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:

If you are SELECTing from a table and the ORDER BY clause in your query is in the same order as a non-clustered index, will the non-clustered index be used?

Answer:

It might or it might not.

2025-12-19

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

Question:

A developer in your team has suggested that better formatted and laid-out SQL code will perform better?

Is this true?

Answer:

Because the optimiser reduces queries down to a format that it can work with the format of your code doesn’t make a difference. Better formatted code is more readable and maintainable though, so it’s always a good idea.

2025-12-17

SQL Server has a number of built-in database roles. The ones I see used the most are db_datareader and db_datawriter. They let you either read from all tables or read and write from all of them. Curiously, what has never been supplied is a db_procexecutor.

Developers and administrators who set up the use of these permissions think they are at least not using dbo or sa and while that’s true,

2025-12-16

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 have created a table named dbo.Customers. The column CustomerID has been declared as

INT NOT NULL IDENTITY(1, 1)

No contraints are defined on the table.

Can the CustomerID contain a duplicate value? If so, how?

Answer:

2025-12-15

When you create a new query file in SSMS, it automatically becomes part of a solution. You can view it in Solution Explorer. In this example, I have created two query files, and renamed the tabs without saving them, as Query 1 and Query 2:

They are in the Miscellaneous Files section as they weren’t part of a script project.

SSMS can now compare multiple files. If I highlight both the files, and right-click, I get this option:

2025-12-11

One surprise in the release plans for SQL Server 2025 is that SQL Server Reporting Services (SSRS) is being discontinued as a brand. If you have a paid license for SQL Server, you are now able to install Power BI Report Server. Previously, that option required you to have an Enterprise Edition license for SQL Server or a premium level license for Power BI.

But Reporting Services was also previously available for SQL Server Express. That will no longer be supplied, and there’s no option to use Power BI Report Server.

2025-12-10