Press "Enter" to skip to content

Category: SQL Server Management Studio

SSMS Regular Expressions

Published 2019-12-19 by Kevin Feasel

Tim Mitchell looks at regular expressions in SQL Server Management Studio:

Regular expressions (or simply regex for short) have long been used by system administrators and data professionals for searching and manipulating text. Regular expressions allow the user to find, replace, and manipulate text based on the pattern they define in the expression. While every text editor allows simple search-and-replace capabilities, regex allows for searching for partial matches, using wildcards, and even integrating special characters (such as newlines and tabs) into the search or replacement text.

Regular expressions have been a part of SSMS for as long as I can remember, and make the process of pattern-based SQL code search much easier. In this tip, I’ll show you a couple of brief examples of the use of regular expressions for working with SQL code in Management Studio.

Regular expressions have been in the product for a long time, but the set of available regular expressions changed when SSMS moved over to the Visual Studio shell. And in some ways (particularly around capture groups), that was a change for the worse.

Comments closed

Editing Rows in SSMS–Behind the Scenes

Published 2019-12-02 by Kevin Feasel

Randolph West explains what happens when you use the editor built into SQL Server Management Studio to modify rows directly:

A customer recently brought up an interesting thesis, that if you edit a table’s values using SQL Server Management Studio (SSMS) using the edit feature, that the table is dropped and recreated in the background when you commit the changes.

This is false, but there had to be a good reason why they were under this misapprehension.

Read on to understand what does happen and where the customer might have gotten this idea.

Comments closed

What’s New with Query Store in SSMS 18.4

Published 2019-11-13 by Kevin Feasel

Erin Stellato takes us through changes to Query Store components in SQL Server Management Studio 18.4:

Last week the SQL Server Tools team released a new version of Management Studio, 18.4, and it dropped in the middle of my Query Store full day pre-con on Monday. Those two are related. There were some changes in SSMS 18.4 specific to Query Store, and for a few seconds over lunch I thought about downloading the new version and running it for my afternoon demos. But…I didn’t want to anger the demo Gods, so I stuck with 18.3.

Read on for info on the changes.

Comments closed

SQL Server Management Studio 18.3

Published 2019-09-24 by Kevin Feasel

Dinakar Nethi announces a new version of SQL Server Management Studio:

Fixed the hard to see dialog for Query Options when the user right-clicks on the T-SQL editor window by making it resizable.

The Completion time message that’s visible in the result grid/file  that was introduced in SQL Server Management Studio 18.2 is now configurable under Tools > Options > Query Execution > SQL Server > Advanced > Show completion time.

In the connection dialog, we replaced Active Directory – Password and Active Directory – Integrated with Azure Active Directory – Password and Azure Active Directory – Integrated, respectively.

There are a pretty good number of bugfixes in this release.

Comments closed

Hidden Modals and SSIS

Published 2019-09-17 by Kevin Feasel

Jana Sattainathan knows how to make hidden modals rise to the surface in SSMS:

I am not sure if run into this but I do often enough on my SSMS 17.9.1 version – suddenly, the SSMS window will not register any mouse-clicks although it will seemingly look fine. The reason is because there is a Modal Window somewhere hidden. Sometimes, ALT-TAB followed by ESC will do the trick but not always.  In the beginning, when I did not know about the modal window, I used to kill SSMS and restart it but you don’t have to.

Click through to see how to do this and save yourself a task killing.

Comments closed

Execution Plan Properties

Published 2019-09-11 by Kevin Feasel

Erik Darling reminds you to check out the properties of execution plan elements:

I read a lot of posts about query plans, and I rarely see people bring up the properties tab.

And I get it. The F4 button is right next to the F5 button. If you hit the wrong one, you might ruin everything.

But hear me out, dear reader. I care about you. I want your query plan reading experience to be better.

Erik provides sound advice here.

Comments closed

Intellisense and the DAC

Published 2019-09-09 by Kevin Feasel

Slava Murygin doesn’t like severity 20 errors just popping up for no good reason:

Yesterday I’ve needed to use Dedicated Administrator Connection (DAC) once in a while, and because I have all kinds of notifications in my system, I immediately got an “Severity 20” alert.

As you probably know, Severity 20 Errors “Indicate system problems and are fatal errors” (See books online: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities?view=sql-server-2017)

Even though “Severity 20” does not indicate any problems with data and belong only to a user process it is still worth to investigate the problem.

Read on to see the cause of Slava’s problem and how there’s no way to fix it in SSMS.

Comments closed

Estimated Execution Plans in Azure Data Studio

Published 2019-08-26 by Kevin Feasel

Dave Bland walks us through the “Explain” button in Azure Data Studio:

At first I was thinking maybe it is a short cut to help.  Nope, it is actually an easy way to get the estimated execution plan. The key is estimated execution plan. While in SQL Server Management Studio we have been able to easily get both the estimated or actual execution plan.

Just as a reminder, the actual execution plan requires the query to actually run. While the estimated plan will use statistics to generate the plan.  Generally, if the actual and estimated are capture very close to each other, you are more than likely not going to see a difference between the tow.

Now let’s talk about the Explain button.  It will return two things, the XML of the execution plan and a graphical representation of the execution plan.

There are a few differences between Azure Data Studio’s implementation of execution plans and SQL Server Management Studio’s.

Comments closed

Management Studio’s Staying Power

Published 2019-08-15 by Kevin Feasel

Kendra Little explains why SQL Server Management Studio isn’t going away anytime soon:

After all, SSMS is no longer the cool new kid on the block: Microsoft has shown consistent effort to develop their new tool, Azure Data Studio (the artist formerly known as SQL Operations Studio), since November 2017. Azure Data Studio is built on the modern foundation of Microsoft’s VS Code, whereas SQL Server Managed Studio is related to the legacy Visual Studio Shell.

Based on this overview, it might seem like a new SQL Server DBA or developer should primarily learn Azure Data Studio, not SSMS. And it might similarly seem like vendors should focus on developing new tooling only for Azure Data Studio.

But when you look into the details of how Azure Data Studio is being developed, it becomes clear that SSMS is still just as relevant than ever:

User base inertia is another reason, one that Kendra doesn’t mention directly. I like where Azure Data Studio is going and try to use it at least half-time. But there are a lot of people with a specific workflow they’ve developed and don’t want to change. As long as that’s a large percentage of the SQL Server population, SSMS isn’t going anywhere.

Comments closed