Azure-Sql-Db

SSMS Tips and Tricks: Overview

Now that I’ve finished posting my T-SQL 101 series, it’s time to start another blog post series.

A few years back, I created a shortcuts series that covered how to use SQL Server Management Studio (SSMS) proficiently. I eventually added that all into a free eBook. It was updated in March 2019.

You can get a copy of it here:

SQL Server Management Studio Tips and Tricks

SSMS version 21 is a huge shift from previous versions, so I thought it was time to revisit all the tips and tricks, to work out what still does or doesn’t work, and to add a bunch of new ones. V21 is based on the 64 bit version of Visual Studio, not the older 32 bit VS partner edition shell.

2025-05-05

SQL Interview: 43 No return value from calling procedure

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

Question:

You want to call a procedure that counts the number of customers in a particular city.

You use the following code:

DECLARE @NumberOfCustomers int;

EXEC dbo.GetNumberOfCustomers 
    @City = N'Brisbane', 
    @NumberOfCustomers = @NumberOfCustomers;

SELECT @NumberOfCustomers;

No matter which city name you pass in the code, the selected count is NULL.

2025-05-04

SQL Interview: 42 OPTION LABEL

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: Developer Level: Medium

Question:

You have a series of very similar SQL queries in an application.

You notice that one of them includes an OPTION LABEL clause.

What would this be used for?

Answer:

OPTION LABEL allows you to apply a label to an instance of a query. That then makes it easy to trace executions of the query using tools like Extended Events or SQL Server Profiler.

2025-05-02

SDU Tools v25 is now available for download

Just a heads-up that v25 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.

Backwards Compatibility

We’ve also tried to keep all the code working for all versions of SQL Server that we see clients using. That means from SQL Server 2008 to SQL Server 2022. We also have an Azure SQL DB version.

2025-04-30

SQL Interview: 41 Sparse columns vs Nullable columns

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: Administrator Level: Medium

Question:

Your team is considering a design where a small number of tables would each have a very large number of columns.

One developer has suggested making the columns SPARSE where another has suggested just making them NULLable.

2025-04-28

SQL Interview: 40 Replacing text data type with varchar

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

Question:

You have a table in your database that contains six columns that are defined with the text data type.

When you run checks on your database, these columns are often flagged with deprecation warnings, and so you change each column as follows:

2025-04-25

SQL Interview: 39 Char data types and row compression

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 a column in your database that is defined as char(20). Whenever you query it, 20 characters are returned, with space padding any unused characters.

An administrator has suggested applying ROW compression to the table, to save space and improve performance.

2025-04-23

SDU Tools: Script Analytics View

Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. One tool that was added a while back is ScriptAnalyticsView.

When we build BI systems, we create a data warehouse with tables that are structured as cleansed versions of the incoming data, and with strong referential integritty and consistency.

When we expose those tables to external BI tools like Power BI, Analysis Services, etc. we limit those tools to a series of views that we create. Generally we put them in a schema called Analytics, but it could have any name.

2025-04-20

SQL Interview: 38 Table Compression Candidates

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:

A developer in your team has suggested applying PAGE compression to all tables and indexes in your application database.

You are concerned that doing so might impact performance, and that ROW compression might be better.

2025-04-19