Sql-Server

This one is a very simple and quick tip.

When working in SSMS, I often need to open Windows File Explorer in the folder where the script file is stored. Turns out there is an easy way to do that.

There are two interesting options when you right-click the tab at the top of a query window. (Note: not on the File menu)

Note that you can open the containing folder for the script. You can also copy the path to the script into the clipboard.

2025-09-20

This is the fifth tale in a series of stories about data. I hope you enjoy the series.

3rd party applications and indexes

Most of my end-user clients run applications that are written by other companies ie: 3rd party applications. Very few write most of their own applications. One of the endless challenges with this is that the application vendors never want their clients to make any changes to the databases that are used by their applications. As a customer, it can be very painful to see performance problems caused by poor or inappropriate indexing choices, and yet to be blocked out from fixing those issues by supportability concerns.

2025-09-19

Every now and again, I come back to my laptop and find that it has rebooted for some reason, while I wasn’t expecting it. A prime cause of that is Windows Updates. I really, really wish that wasn’t so, but someone at Microsoft has decided that I must apply these updates. I have very little control over the time when that occurs. For example, if I’m on the road delivering presentations, there’s no “wait till I get home” option for Windows Updates.

2025-09-18

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:

You are reviewing a database design from one of your developers.

He normally works with PostgreSQL and in that language, he uses the text data type for all strings.

When creating the database in SQL Server, he has used the varchar(max) data type for all strings.

2025-09-17

SSMS keeps a list of the server names that you have connected to, and prompts you with those when you drop-down the list while making a connection:

Eventually, that list can either become messy, it can include servers that don’t exist anymore, and so on. You might want to clear up the list.

To do this in early versions of SSMS, you needed to locate the SqlStudio.bin file from the Documents and Settings area in your user profile. Fortunately, that’s no longer required. In more recent versions, all you needed to do was to open this dialog, arrow down to the ones that you want to remove, and hit the Delete key.

2025-09-16

This is the fourth tale in a series of stories about data. I hope you enjoy the series.

As very few people seem to have worked with SQLCMD mode, I thought I’d start this article with a little background.

Some background

Way back in SQL Server 2005, Microsoft added SQLCMD as a new command line tool to replace OSQL. It was an OLE-DB based tool rather than an ODBC based tool and had a richer set of options. For example, you could execute operating system commands within your scripts.

2025-09-15

In SSMS, when you click New Query, a new query window is opened, but because it’s blank, what might not be immediately obvious is that it’s based on a template.

The location of the template depends upon the version, but for SSMS 17.6, you’ll find it in this folder:

C:\Program Files\Microsoft SQL Server Management Studio 21\Release\Common7\IDE\NewFileItems\SQLFile.sql

The file is called SQLFile.sql. If it’s not in the location above, just search for SQLFile.sql.

2025-09-14

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 use a tool that does static code analysis of your T-SQL code.

It identifies the following predicate as an issue:

WHERE EXISTS 
(
    SELECT * 
    FROM dbo.Products AS p 
    WHERE p.ProductID = c.ProductID
)

Is this an issue?

2025-09-13

SQL Server execution plans can become quite large. That makes them hard to navigate because you are endlessly scrolling around the results pane in SSMS.

The pane does have some zoom features. Note that if I right-click in the whitespace, I get these options:

So I can zoom in and out, set a custom zoom level, or zoom until the entire plan fits. Generally though, that would make the plan too small to read, as soon as you have a complicated plan.

2025-09-12

In an earlier post, I described how query plans could be saved as .sqlplan file, shared, and loaded again in SSMS. It’s also possible to extract them out of SQL Server Profiler or Extended Events Profiler.

This is useful, but the same applies to deadlock graphs. SQL Server 2005 added Deadlock graph as a type of event in SQL Server Profiler. (It’s also part of Extended Events Profiler).

If I open a new trace in Profiler, I can add Deadlock graph to the list of events:

2025-09-10