Azure-Sql-Db

SQL Interview: 72 Dynamic data masking and encryption

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

SSMS Tips and Tricks 5-1: Changing the number of rows selected or edited in Object Explorer

When you right-click a table in SQL Server Management Studio, you get options for selecting or editing but the number of rows is limited:

Those values can be changed. By default, these numbers are both 200, but I’ve decided to change the default number of rows selected to 1000.

In Tools, Options, SQL Server Object Explorer, then Commands, you can set the values to whatever suits you:

I don’t tend to ever use the Edit option but I’d suggest not making it too large.

2025-08-19

SQL: The need for enumerations in T-SQL

I recently wrote about the need for T-SQL to have constants. I received a lot of feedback on that.

Everyone seemed to agree, although one or two asked Why bother as Microsoft isn’t really listening now about enhancing T-SQL?. I understand that sentiment but I don’t think things are quite as bleak as some are suggesting. I currently sense a stirring within the product group, where there is a new interest in developers.

2025-08-18

SSMS Tips and Tricks 4-10: Using Activity Monitor

This is a quick tip but an important one. I see many people using SSMS and they aren’t aware of Activity Monitor.

While there are many clever things that we can do with queries, to interrogate the health of the system, don’t forget that there is quite a bit of useful information in Activity Monitor, and it’s easy to get to.

There are two basic ways to launch Activity Monitor. The first is to right-click the server in Object Explorer:

2025-08-17

SQL Interview: 71 Potential issues with NOLOCK

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:

You have applications that make extensive use of NOLOCK hints. You have heard that this can cause issues.

Which of the following issues could occur as a result of using NOLOCK hints?

  • Duplicate rows
  • Phantom rows
  • Missing rows

Answer:

2025-08-16

SSMS Tips and Tricks 4-9: Setting SQLCMD mode for all new query windows

SQLCMD mode changes how queries are executed in SSMS. When using this mode, you can work with options that aren’t normally part of SQL Server T-SQL scripts.

Some installation scripts also require SQLCMD mode and will fail if it’s not enabled.

Let’s look at an example executing a query against 2 servers within the same script.

First, we open a new query window, then on the Query menu, we choose SQLCMD Mode.

2025-08-15

SQL: The need for constants in T-SQL

If you look at the code in the image above, I have one immediate reaction.

What are those numbers that are sprinkled throughout the code?

I see this all the time in T-SQL code. Yet in any programming language, it’s a really poor idea to do this. It leads to very fragile code that’s so hard to maintain longer-term.

So why do people do it?

So, you might wonder why people do this in T-SQL. Couldn’t they just do this at the start of the code?

2025-08-14

SSMS Tips and Tricks 4-8: Setting shortcuts for your favorite stored procedures

In an earlier entry, I mentioned how useful the F1 key is. On its own, it provides syntax help, but when you highlight an object and hit Alt-F1, you get to see metadata about the object.

Under the covers, this just runs the sp_help system stored procedure. Alt-F1 has been mapped to that.

You can see where this is configured, change it if required, and/or configure other procedures as well.

2025-08-13

SQL Interview: 70 Updating statistics during index rebuilds

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:

When you rebuild indexes, are statistics on the table automatically rebuilt?

If not, why?

Answer:

Rebuilding indexes updates the statistics for the index, but column statistics are not updated unless they are tied to the index.

2025-08-12

SSMS Tips and Tricks 4-7: Viewing client statistics

While SQL Server is quite fast at executing queries, when you are connecting from a client application like SSMS, you might wonder how much time SQL Server spent executing the query, as opposed to how long the communication with the server took.

This type of information is available in the Client Statistics.

Let’s see an example. If I connect to a server in an Azure data center, I’ll have higher latency than for one in my own site. That will affect the wait time for a server response. I’ll connect to a server that I have aliased as SDUAzure. The server is in the Australia South East data center.

2025-08-11