Azure-Sql-Db

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

As very few people seem to have worked with SQLCMD mode, I thought I’d start this article with a little background.

Some background

Way back in SQL Server 2005, Microsoft added SQLCMD as a new command line tool to replace OSQL. It was an OLE-DB based tool rather than an ODBC based tool and had a richer set of options. For example, you could execute operating system commands within your scripts.

2025-09-15

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

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

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

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

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:

In the following WHERE clause, both AColumn and AnotherColumn are NULL.

WHERE AColumn = AnotherColumn

What is the effect of the WHERE clause?

Answer:

In SQL boolean logic, the expression NULL = NULL returns NULL and not True. So the WHERE clause would exclude the rows.

2025-09-01

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

I have a number of clients that I spend a day or two with each month. I like this style of engagement as I get to know the staff and their systems over a period of time, can see the improvements that we make over time. The staff members also know that if they have issues that aren’t desperate, they can save them up for the days that I am onsite. When I arrived at one of these customer sites recently, several of the staff members had grins on their faces, and one told me that Terry (well let’s call him Terry anyway) had really broken something.

2025-08-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: Security Level: Medium

Question:

Is it possible that revoking a permission can cause a user to be able to access a resource, when they previously could not?

If so, how?

Answer:

REVOKE can remove either a GRANT or a DENY. Revoking a DENY can cause a user to be able to access a resource that they previously could not.

2025-08-28

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:

In SQL Server, what is an orphaned user?

How do users typically become orphaned?

Answer:

An orphaned user is one that appears in a database, but the server principal that they are linked to, does not exist.

2025-08-24

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:

Is dynamic data masking a form of encryption?

Answer:

No. Dynamic data masking just obfuscates the data when it is presented to end users. It is unrelated to encryption.

Obfuscation is not a form of encryption.

2025-08-20