Extended Properties

Phil Factor has a detailed article on extended properties:

Extended properties are easy to read, thanks to a useful system view. Sys.extended_properties. However, they are a pain to create, update and delete; they rely on special stored procedures that have a syntax that isn’t at all intuitive for those of us without mutant mental powers. They have a limit of 7,500 characters but are actually stored in a SQL_variant so that DateTime, Approximate numeric, exact numeric, character, Unicode and binary information can be stored in it. Most of us use some sort of tool such as SSMS to maintain this documentation rather than to do it via SQL. The SQL is cumbersome.

Extended properties was an interesting idea but there was so little tooling available to make them really useful.  I don’t see that changing.


Kenneth Fisher has a demonstration of snippets in Management Studio:

A while ago I talked about Templates. This is an easy way to get a, well, template of a piece of code. But a much faster way to get a simple template is to use asnippet. With a simple key command (ctrl-K, ctrl-X) you can open up the snippet picker and quickly navigate to the snippet you need.

Note: You can also open the snippet picker by right clicking and selectingInsert Snippet… or from the menu Edit -> Intellisense -> Insert Snippet…

Further note: snippet picker sounds really odd but it was the name in BOL so we’ll go with it.

Click through for a demo.

SSMS Grids

Riley Major airs grievances with SQL Server Management Studio’s old-timey grids:

After years of using SQL Server Management Studio (and its predecessor Query Analyzer), I’m struck by how incapable the results grids still are. Unlike Excel, you can’t sort them, you can’t filter them, you can’t search within them, and you can’t easily change their font size. In any commercial software product, grid tools are table stakes. For some reason vendors still like to run through them, but they’re never a differentiator. That’s because you can just buy a grid component and use it in your application. Even the basic grid control which came with .NET 2.0could sort.

Click through to read more, and also check out the Trello board that Riley mentions.

SSMS And Memory

Daniel Janik looks into those out-of-memory errors Management Studio blesses us with:

Has SSMS (SQL Server Management Studio) been crashing on you? Have you been getting Out of Memory messages when attempting to run queries?

You may have noticed that this tends to occur after you’ve opened and closed 40 to 50 query windows. I’ve noticed this when I have had as little as 5 query windows open after having already opened and closed 30 or so other query windows.

It’s crazy that Management Studio is still a 32-bit application after all of these years.

SQL Server Connectivity In VS Code

Max Trinidad looks at the VS Code plugin for SQL Server connectivity:

After installing, we need to customized their setting by creating connection(s) to our SQL Server. We do this by opening VS Code “User Preferences” and under “Default Settings.json” we search for the “vscode-mssql” settings to be copied over to our working folder “settings.json” file.

I played with this very early on and would like to see it continue to be developed, but it’s no replacement for Management Studio.

Bug Scripting Sequences

Daniel Janik notices a small bug in how Management Studio scripts out sequences:

Have you ever scripted an object from SQL Server Management Studio (SSMS)? It does a really good job. You get nice cleanly formatted scripts that start with USE statements to select the database. They even have some simple comments?

Have you ever written a Sequence? Turns out if you script one you’ll notice that Microsoft left you an extra surprise. Double USE statements. Does it matter much? No. Should they fix it? Yes. I noticed this behavior when sequences were first released and it still exists in the latest version of SSMS for SQL 2016 (13.0.1500.23) as of this posting.

Yeah, that’s a tiny bug, but I can see it being annoying.

SSMS Query Shortcuts

Andrew Pruski shows where to set Management Studio query shortcuts:

Following on from my last post Changing connection colours in SSMS I thought I’d write another quick about this cool but also often unused feature in SSMS.

These shortcuts allow you to run pre-determined queries by assigning a hot key within SSMS. To do this in SSMS go to Tools > Options > Environment > Keyboard

I love query shortcuts.  I have three dedicated to different sp_whoisactive commands (one to get everything going on; one to get everything related to my username; and one to get everything going on plus query plans, which I don’t always use because of the additional overhead).

Understanding Query Durations

Kendra Little explains some of the intricacies behind query durations:

I typically look at the ‘CPU time’ metric when tuning instead of ‘elapsed time’ (duration). This can work well for tuning because you’re measuring how much more efficient you made the  query in terms of CPU cycles.

But ‘CPU time’ isn’t perfect, and it can get a little weird for reporting results to users, because:

  • If the query uses parallelism, CPU time can be higher than the duration — which may make the query seem “slower” than it actually is to anyone reading a report

  • ‘elapsed time’ includes all the time that it takes to display the results in Management Studio, which is probably a different duration than it would take to return the results to an application server. If you’re just returning a few rows, this may be negligible– but once it gets into the thousands of rows, it can be very noticeable.

Moral of the story:  also use SQL Sentry Plan Explorer…

Creating Snippets With Powershell

Allison Tharp has a script to create SSMS snippets:

One issue with SSMS’s Snippet Manager is that the code has to be stored in a .Snippet file in a specific location on your local machine.  The .Snippet files are in XML format, which is a little inconvenient since we don’t write SQL in XML format.  This means that in order to add a new snippet, you need to create a new .Snippet file in the correct format, add the SQL to it, and save it in the right location.  After that, you must import the file through SSMS.

I have had ideas for snippets, but it is enough of an inconvenience to sometimes prevent me from going through the motions of making an actual snippet file.  I wanted to write some kind of code that would allow me to save a SQL file into a specific folder and let an automated program convert it into the Snippet format.  I’ve also been wanting to learn PowerShell, so I thought this would be a good opportunity!

The next step would be turning that into a cmdlet.

SSMS Connection Colors

Andrew Pruski shows how to change window bar colors within SQL Server Management Studio:

A simple but effective setting in SQL Server Management Studio is using custom colours to identify which server you are about to execute a query on. It’s simple to setup but not everyone who uses SSMS is aware of it so I thought I’d quickly run through the steps here.

This is a nice visual way of figuring out you’re in production before you run that truncate table script.


August 2017
« Jul