Azure-Sql-Db

SSMS Tips and Tricks 7-3: Configure autorecover time, and recover unsaved queries

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

SQL Interview: 79 Extensive use of varchar(max)

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 Tips and Tricks 7-2: Pinning and clearing the connection entries

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

Data Tales 4: The case of the phantom duplicates

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

SSMS Tips and Tricks 7-1: Changing the default text in new query windows

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

SQL Interview: 78 SELECT *

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

SSMS Tips and Tricks 6-5: Zooming and navigating query plans

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

SSMS Tips and Tricks 6-4: Saving and sharing deadlock graphs

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

SQL Interview: 77 SARGability

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:

A DBA at your site tells you that you need to make sure your predicates are sargable.

What is SARGability?

Assume a table has a column called OrderDate and it is of date data type. There is a single column index on the column. Can you give an example of a non-sargable predicate that uses it?

2025-09-09

SSMS Tips and Tricks 6-3: Saving and sharing query plans

SQL Server query plans are stored as XML. You can see what they look like by right-clicking in any query plan in SSMS, and clicking Show Execution Plan XML:

That will return a whole bunch of XML like this:

It’s important to understand that when SSMS is showing a graphical execution plan, it’s just graphically rendering some XML like the plan above.

The Properties window in SSMS is also showing details extracted from that same XML.

2025-09-08