The Bit Bucket

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

Data Factory Course covering both Azure and Fabric released

Learn to design, automate, and optimize data pipelines across Azure and Microsoft Fabric environments !

We’re pleased to announce that our new course Azure Data Factory and Fabric Data Factory: Data Integration for Data Professionals is now available. This is another course that we’ve had so many requests for. You can find details of it here:

Azure Data Factory and Fabric Data Factory: Data Integration for Data Professionals

Are you ready to master modern data integration with Azure and Fabric Data Factory?

2025-08-11

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

SQL: Forgetting to backup the TDE certificate is a CLM

Years ago, I was a trainer for the Microsoft SQL Server Master’s program at their headquarters in Redmond. In one of the labs, I asked the students to set up database mirroring on a TDE-encrypted database. It was fascinating to watch them try to set it up.

It’s important to realize that the students in the room were all Premier Field Engineers and senior consultants from Microsoft and from some large partner companies. The students were all very experienced with the product. Yet it often took many of them quite a while to work out how to set it up.

2025-08-10

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

SQL: Restoring a SQL Server 2000 database on SQL Server 2022

You’d think SQL Server 2000 would be long gone by now, but recently I had to restore a SQL Server 2000 database, and get it working on SQL Server 2022.

Now extended support for SQL Server 2000 ended on 9th April 2013.

SQL Server 2000 came in several editions: Enterprise, Standard, Developer, Personal, and the Desktop Engine or MSDE. I’m guessing most people would forget that there was ever a Personal edition. Regardless, this database was from an application that used an embedded database attached to the Desktop Engine or MSDE.

2025-08-06

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