Sql-Server

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

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 reviewing T-SQL code and in one query, you find the following:

OPTION (FORCE ORDER)

What is the point of this code, and is it likely to be helpful?

Answer:

FORCE ORDER tells SQL Server to join the tables that are involved in a query, in the order that they are written in the query.

2025-07-27

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

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: Security Level: Medium

Question:

You have code in stored procedures that records which users have called the procedures. The stored procedures are defined as:

WITH EXECUTE AS OWNER

You find that when you log the caller of the procedure, you get the owner of the procedure, rather than the caller.

2025-07-23

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

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 have a table called Sales.Transactions that has an associated foreign key constraint. The constraint checks that CustomerID values correctly reference the CustomerID primary key of the Sales.Customers table.

You need to bulk load some transaction data and decide to disable the constraint during the load, hoping it will increase performance of the load.

2025-07-19

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: