Azure-Sql-Db

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

SSMS Tips and Tricks 4-6: Using a count with the GO separator

In T-SQL, a script is a set of one or more batches.

For example, if we have the following script and click Execute, it looks like all the commands were sent to the server and executed all at once:

But that isn’t what happened.

What did happen is that SSMS found the word GO and broke the script into a series of batches. In this case, there were three batches. First, it sent the commands shown here as Batch 1 to the server, waited for them to execute, then sent Batch 2, waited for it to execute, then sent Batch 3, and waited for it to execute.

2025-08-09

SQL Interview: 69 When tempdb gets used

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:

You are monitoring a SQL Server and you notice that tempdb is being heavily accessed. You know that your applications make very little use of temporary tables.

Can you give examples of other things that might be using tempdb.

2025-08-08

SSMS Tips and Tricks 4-5: Executing multi-server queries

In an earlier post, I mentioned that you can create a registered list of servers, either in Local Server Groups or stored in a Central Management Server.

What I didn’t really talk about though, is what you can do with these groups of servers, rather than just executing queries on an individual server.

I’ve created three local server groups, for my development, UAT, and production servers.

The Development Servers group has two database servers in it. If I right-click the group, rather than any individual server, we get these options:

2025-08-07

SSMS Tips and Tricks 4-4: Configuring registered servers

When working with SQL Server systems, it can be hard to remember the names of all the servers, to remember connection details for the ones that need SQL logins (instead of Windows authentication), and to remember other details of those servers, such as which environments they are part of (eg: production, UAT, test)

SSMS has a facility to help you to do this. It allows you to register server details in a single place.

2025-08-05

SQL Interview: 68 Converting to local time zones

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 are working with an Azure SQL Database. When you display dates and times, you notice that they do not match your local time zone of CEST.

Can you change the server settings to Central European Standard Time (CEST)?

2025-08-04

SSMS Tips and Tricks 4-3: Changing connections in a query window

I commonly run into a few connection-related scenarios:

  • I’m working on a large query and need to run it against several servers, not concurrently, but one after the other.
  • I’ve just made a database connection, and got my query ready, only to discover that I’ve connected to the wrong server.

Either way, what I’ve seen people do in these scenarios is to:

  • Select all the text in the current query
  • Copy it
  • Open a new query window
  • Paste the code

That’s all good but SSMS has had a simpler way of doing this for quite a while.

2025-08-03

SQL: There is no default output order for SQL queries

There is a really common misconception about output order for T-SQL queries. I can’t tell you how often I’ve heard people say that if you don’t specify an ORDER BY clause, then you’ll see the rows returned in the order of the primary key.

The assumption they’re also making is that the primary key is the clustering key. And that’s not necessarily true either. You can have a non-clustered primary key on a table.

2025-08-02

SSMS Tips and Tricks 4-2: Using colors to avoid running scripts against the wrong server

Everyone who’s worked with SQL Server for any length of time, has had the experience of executing a T-SQL script, and then noticing, with horror, that they’ve just executed the script against the wrong server.

You know the feeling. It even happens at Christmas time, just when you were hoping to get away from work for a few days, or when you are the unlucky one who’s doing on call work.

2025-08-01

SQL Interview: 67 Backing up transaction log after DB failure

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:

A storage failure occurs and during recovery, a data file from your database was lost.

Transaction log backups are taken every 5 minutes.

Periodically, when data is being loaded into the database, Bulk Logged mode is used. At the time of the failure, the database was still in bulk logged mode.

2025-07-31