Database.Guide – Page 313

How SHOWPLAN_XML Works in SQL Server

December 6, 2025October 5, 2020 by Ian

In SQL Server, you can use the SET SHOWPLAN_XML statement to return the query plan for a T-SQL statement in the form of a well-defined XML document. The query plan contains detailed information about how the statement will be executed.

It’s similar to SHOWPLAN_ALL, except that SHOWPLAN_ALL returns its data set of rows that form a hierarchical tree.

You can set SHOWPLAN_XML to either ON or OFF.

When SHOWPLAN_XML is ON, all subsequent T-SQL statements are not executed. Instead SQL Server returns execution information for the statement (without executing it).

SQL Server Error 4104: The multi-part identifier could not be bound.

February 12, 2022October 4, 2020 by Ian

If you get an error telling you that the “The multi-part identifier could not be bound.”, it usually means that you’re prefixing one or more columns with either a table that isn’t included in your query, or an alias that you haven’t actually assigned to a table.

Fortunately, the error message shows you which multi-part identifier is causing the problem.

How to View the Query Execution Plan in Azure Data Studio (SQL Server)

December 6, 2025October 3, 2020 by Ian

If you use Azure Data Studio for your database admin tasks, you might be wondering how you can view the execution plan for your queries?

Fortunately, this is easy!

You have the option of viewing:

An estimated query execution plan
The actual query execution plan

List the Accounts Associated with a Database Mail Profile in SQL Server

October 2, 2020 by Ian

In SQL Server, you can use the sysmail_help_profileaccount_sp stored procedure on the msdb database to retrieve a list of all associations between accounts and Database Mail profiles.

You can also return account info based on the account name/ID or the profile name/ID.

Return a List of Database Mail Profiles in SQL Server (T-SQL)

October 1, 2020 by Ian

In SQL Server, you can use the sysmail_help_profile_sp stored procedure on the msdb database to retrieve a list of all Database Mail profiles.

You can also return profile info based on the profile name or ID.

Find the Associations Between Database Mail Accounts and Database Principals in SQL Server (T-SQL)

September 30, 2020 by Ian

In SQL Server, you can use the sysmail_help_principalprofile_sp stored procedure on the msdb database to retrieve a list of all associations between Database Mail accounts and database principals.

You can also return account info based on the principal name/ID or the profile name/ID.

Get a List of Database Mail Accounts in SQL Server (T-SQL)

September 29, 2020 by Ian

In SQL Server, you can use the sysmail_help_account_sp stored procedure on the msdb database to retrieve a list of all Database Mail accounts.

You can also return account info based on its account name or ID.

3 Ways to use ALL in SQL Server

December 8, 2020September 28, 2020 by Ian

You might be familiar with the ALL option in SQL Server. Perhaps you’ve used it along with the UNION operator to include any duplicates that might be returned in the result set.

But did you know that ALL can also be used in two other contexts?

ALL can be used in the following three contexts:

As an argument to the SELECT clause.
As an argument to the UNION clause.
As a logical operator when comparing a scalar value with a single-column set of values.

Examples of each of these contexts follows.

SQL Server ALL Operator Explained

September 28, 2020September 27, 2020 by Ian

In SQL Server, the ALL operator can be used with a subquery to compare a scalar value with a single-column set of values returned by the subquery.

It’s also true that the SELECT clause and UNION operator both accept an ALL argument, although this usage has a different purpose (allows duplicates in the result set).

Below are examples of using the ALL operator with a subquery.

What is a Comparison Operator?

February 14, 2023September 26, 2020 by Ian

Comparison operators are an important part of most programming languages.

Comparison operators are used to compare two expressions. The result is either true or false. It could also be unknown. This could also be represented by either 1, 0, or NULL, depending on the language. These are typically known as “Boolean expressions”.

When used with databases, comparison operators can be used inside your SQL queries to filter data to a certain criteria.