Ssms-Tips-Tricks

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

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

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

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

When I am writing my own code using a .NET (or other) language, I have a great deal of control of how the connection string that my application uses to connect to SQL Server is configured.

In particular, I might need to add another parameter or two.

As a simple example, I might be working with a multi-subnet Availability Group, spread across a production site and a disaster recovery site. It’s common to then have an Availability Group Listener in both subnets.

2025-07-30

In previous posts, I’ve been talking about how to use snippets in SSMS and how to create your own. There are several types of snippets and one of the special types of snippets that I want to mention are the “surround with” snippets.

If you look at the following block of code:

Imagine that you want to execute the four highlighted lines only when a condition is true. If I hit Ctrl-K and Ctrl-S while they are highlighted, I’m prompted with this:

2025-07-28

In an earlier post, I showed how to create a DROP DATABASE template in SSMS. I mentioned that a template wasn’t the best option because a command like this is normally inserted into a script; it’s not the whole script.

That’s where snippets shine. Let’s create a snippet for it.

First let’s open Code Snippets Manager (Tools > Code Snippets Manager):

You’ll see the existing snippet folders. I’ve clicked Add, then created a new folder called GL_Snippets. I created a new folder and pointed to it. For this temporary one, I’ve created it in C:\Temp but you would normally use an appropriate folder.

2025-07-26

Have you ever started to create an object using T-SQL in SQL Server, and thought what’s the right syntax for this?

I’ve worked with SQL Server since 1992 (version 4.2) and yet almost every time I go to create a function, I have to spend a few moments thinking about what the correct syntax is, because there are different types of functions (scalar vs table-valued, inline vs multi-statement).

SSMS has had templates for a long time, and they are useful. In fact, a few days ago, I wrote about how you can create your own.

2025-07-24

In another post, I mentioned how useful templates can be. I said that I’d discuss how to create them later. Well, that’s today.

I thought I’d take dropping a database as an example. The supplied template doesn’t work for me, so let’s create a new one.

Note: SQL Server 2016 SP1 introduced DROP DATABASE IF EXISTS but I find that option quite useless. It fails if anyone is connected to the database. And to disconnect people beforehand, you need to first check if it exists, so the statement is pointless.

2025-07-22

Sometimes you might need help to remember the T-SQL syntax required to create different types of objects. Templates can do this. Because they don’t appear on the screen by default, many people don’t even realize they are there.

From the View menu, you can choose to view Template Explorer:

When Template Explorer opens, you see a list of predefined templates, plus any that you have created yourself. (One advantage of templates is that they are quite easy to create). In this case I’ve expanded the Credential folder, right-clicked the Create Credential template, then clicked Open.

2025-07-20