Azure-Sql-Db

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 use a tool that does static code analysis of your T-SQL code.

It identifies the following predicate as an issue:

WHERE EXISTS 
(
    SELECT * 
    FROM dbo.Products AS p 
    WHERE p.ProductID = c.ProductID
)

Is this an issue?

2025-09-13

SQL Server execution plans can become quite large. That makes them hard to navigate because you are endlessly scrolling around the results pane in SSMS.

The pane does have some zoom features. Note that if I right-click in the whitespace, I get these options:

So I can zoom in and out, set a custom zoom level, or zoom until the entire plan fits. Generally though, that would make the plan too small to read, as soon as you have a complicated plan.

2025-09-12

In an earlier post, I described how query plans could be saved as .sqlplan file, shared, and loaded again in SSMS. It’s also possible to extract them out of SQL Server Profiler or Extended Events Profiler.

This is useful, but the same applies to deadlock graphs. SQL Server 2005 added Deadlock graph as a type of event in SQL Server Profiler. (It’s also part of Extended Events Profiler).

If I open a new trace in Profiler, I can add Deadlock graph to the list of events:

2025-09-10

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:

A DBA at your site tells you that you need to make sure your predicates are sargable.

What is SARGability?

Assume a table has a column called OrderDate and it is of date data type. There is a single column index on the column. Can you give an example of a non-sargable predicate that uses it?

2025-09-09

SQL Server query plans are stored as XML. You can see what they look like by right-clicking in any query plan in SSMS, and clicking Show Execution Plan XML:

That will return a whole bunch of XML like this:

It’s important to understand that when SSMS is showing a graphical execution plan, it’s just graphically rendering some XML like the plan above.

The Properties window in SSMS is also showing details extracted from that same XML.

2025-09-08

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

Anyone who has worked with SQL Server for any length of time knows the common errors that happen. Before a new user even completes their question, you often know how it’s going to end.

One of those questions is always about database log files that keep growing and growing until they fill all available disk space. Sound familiar? We’ve all heard about that one and most of us would have a straightforward checklist for what to look for.

2025-09-07

I’ve mentioned before that SSMS is a good tool for analyzing queries, as much as for executing them.

Way back in SQL Server 2005, query plans had missing index details added. When a query plan was created, SQL Server recorded that it thought it could have executed the query better, if only you’d provided it with appropriate indexes. But at that point, the suggestions weren’t very good, and the tools didn’t show them.

2025-09-06

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:

Are there situations where temporary tables will perform better than table variables?

What could cause that?

Answer:

Temporary tables have two properties that are not available to table variables:

• Indexes - with table variables, you can have primary key constraints and unique key constraints, but not other indexes
• Statistics - temporary tables support rich statistics, similar to tables where table variables have very limited statistics, mostly just cardinality.

2025-09-05

One of the advantages of SSMS is that it can be used to analyze queries, not just to execute them.

There are two basic types of query plan: estimated execution plans, and actual execution plans.

For a typical query, I can obtain the estimated execution plan, by hitting Ctrl-L, choosing the option in the Query menu, or clicking on the toolbar icon:

Let’s do this for the following query:

2025-09-04

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

I was recently at a customer site where the developers were very concerned about the impact of adding columns to a table. They told me that when they added a new column that their deployment code was timing out and the database was massively increasing in size.

The table had increased from around a small size to well over 50GB during the single operation. The deployment operation involved adding the column and writing one row to a deployment history table. Because they were only writing a single new row, they were blaming SQL Server for bloating the database size when a column was added.

2025-09-03