Ssms-Tips-Tricks

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

In a previous post, I was discussing how outlining can be helpful with navigating around within a large T-SQL script file.

If you were trying to do that within a Microsoft Word document, the most common thing to use is bookmarks, and SQL Server Management Studio (SSMS) has them as well.

Bookmarks are simply placeholders within a script. (They can also apply to other types of document within SSMS). Where I find them very useful is when I’m working in two or three places within a long script at the same time. Perhaps I’m working on a function, and also on the code that calls the function. By using bookmarks, I’m not flipping endlessly around the script file, and can jump directly from placeholder to placeholder.

2025-07-18

For some years now, SQL Server Management Studio (SSMS) has had the ability to use code outlining, the same way that other Visual Studio languages can.

This can be very useful when you are trying to navigate around a large script file.

The simplest usage is to collapse or expand a region of code. Note that in the following script, code regions have been automatically added by SSMS:

This allows us to click on the outline handles, and collapse the code:

2025-07-16

Two key combinations used by SQL Server T-SQL developers every day are Ctrl-C and Ctrl-V for copy and paste.

But many users of SQL Server Management Studio (SSMS) don’t realize that it has a clipboard ring and can deal with several objects in the clipboard at the same time.

Let’s see an example.

In this screen shot, I’ve opened a query window with the source code of the AnalyzeTableColumns procedure from SDU Tools.

2025-07-14

I often see people doing very repetitive editing tasks that could easily be carried out by using selecting and changing rectangular regions of code.

The simplest example of doing this is to insert some text on a number of rows. Take the following code as an example:

I’ve got the skeleton of a list of columns in a CREATE TABLE statement but let’s assume that I’m a comma in front person and want to put a few spaces and a comma, etc. in front of each column after the second.

2025-07-12

A request that I hear all the time, is “I don’t like tabs but insert name of annoying colleague here decided he likes to use them. How do I remove them?” Similar thing happens when installing SSMS and leaving the default options.

Tabs vs Spaces

Whether to use tabs or spaces leads to near religious level arguments amongst SQL developers. I see strong arguments on both sides. In the past, I’ve always ended up using spaces because I’ve run into issues with tabs in some of the tooling that I needed to use.

2025-07-10

Intellisense is one of the best things that’s ever been added to Visual Studio or to SQL Server Management Studio (SSMS). It’s hard to remember back to before it was added, or how we worked then.

I had a young friend from the United Kingdom who had just completed a Computer Science degree and one of the things that he was most proud of, is that he knew so many HTML tags and which attributes went with which tags. When I showed him HTML Intellisense in Visual Studio, I think he was about to cry.

2025-07-08

I mentioned in an earlier post that I think the online documentation is now superior to any version that you can install locally.

I particularly like the way that the online version is cross-version ie: each page covers all supported versions, instead of having a separate page for each version.

But one of the really big bonuses is that you also have the opportunity to change the documentation if you think it’s incorrect or you think it could be improved. Microsoft have placed all the documentation in a GitHub repository and you can change it. Doing so is easier than you might expect.

2025-07-06