We are very excited to announce the release of Public Preview 5 of SQL Server Management Studio (SSMS) 18.0. This release has a number of new features and capabilities and several bug fixes across SQL Server Management Objects (SMO), UI, etc.
You can download SSMS 18.0 Public Preview 5 here.
The most interesting thing in it for me is probably the menu item for
CREATE OR ALTER with scripts.
Note the :CONNECT command is used to connect to another server.
Because everything else works pretty much the same, and you get a whole lot of additional options, you might choose to open all your new queries in SQLCMD mode. That’s easy to do.
SQLCMD mode is one of those things where I thought I’d use it a lot, but aside from deploying database projects, I don’t. Granted, this could be a failure of imagination on my part.
If you work with SQL Server on a daily basis, it is very likely you have a lot of custom scripts you have to execute frequently, maybe you have stored them on a folder and you open them manually as you need them, or have saved them on a solution or project file, maybe you execute a custom .bat or PowerShell file to load them when you open SSMS…
Every method has its pros and cons, and on this post, I will show you a new method to load your custom scripts on any open query window on SSMS via Snippets.
Click through for more details, including an example. Snippets are a good tool implemented adequately in SSMS. A few third-party extensions make working with snippets better and really valuable (until you’re stuck on a machine without your snippets).
Last week Microsoft released SQL Server Management Studio 18.0 into public preview, here’s a link so you can read about the new and improved functionality it offers.
One significant change is the addition of actual vs estimated row counts onto the showplan operators in execution plans (only actual, not estimated…which kinda makes sense).
Here I’m running a very simple bit of code on some DMV’s (namely exec requests and sessions) to demonstrate this addition.
Read on for the example.
SSMS 17.9 on the left has Database Diagrams at the top.
SSMS 18.0 does not. Database Diagrams are simply gone. Hallelujah! For over a decade, people have repeatedly cursed SSMS as they’ve accidentally clicked on the very top item and tried to expand it. One of the least-used SSMS features had one of the top billings, and generated more swear words than database diagrams.
The good news continues when you right-click on a server, click Properties, and click Processors.
The comments show that not everyone is happy about this, but I do think it’s for the best—the database diagram tool hadn’t been updated in a long time and is missing many features that an ER tool needs. I’d rather use Visio (or a better tool).
You can see the estimated and actual number of rows right there on the query plan just like live query plans! You no longer have to waste hours of your life hovering over different parts of the query plan in order to see where the estimated row counts veer off from the actual row counts.
This doesn’t require SQL Server 2019, either.
Read on for Brent’s thoughts on the matter.
SSMS is based on the new VS 2017 Isolated Shell. This means a modern shell that unlocks all the accessibility features from both SSMS and VS 2017.
Smaller download size (~400 MB). This is less than half of what SSMS 17.x is.
SSMS can be installed in a custom folder. Currently, this is only available on the command line setup. Pass the extra argument to SSMS-Setup-ENU.exe, SSMSInstallRoot = C:\MyFolder
High DPI enabled by default.
Better support for multiple monitors to ensure dialogs and windows pop up on the expected monitor.
Isolation from SQL Engine. SSMS does not share components with SQL engine anymore. More isolation from SQL engine allows for more frequent updates.
Package Ids no longer needed to develop SSMS Extensions.
18.0 will install alongside 17, so you can have both at the same time.
There has been many a blog post out there that shows you some of the great ways you can customize the look and feel of your management studio windows. One of my favorite recently published ones is from the great people at Brent Ozar Unlimited (Brent himself in this case) here.
There is even many a blog post out there about how you can export your settings from one and import it to another here.
All of the blog posts I’ve read however have one problem for me. They seem very “clicky”, as in a lot of clicking of the mouse (or keyboard shortcuts) has to happen every time you want to import your settings and I thought there has to be a more programatic way.
After all, many of us espouse to the DRY (Don’t Repeat Yourself) technique when coding. Why wouldn’t we want to trim time and energy off of something as basic as a settings import.
And Josh has that solution.
SSMS 17.9 provides support for almost all feature areas on SQL Server 2008 through the latest SQL Server 2017, which is now generally available.
In addition to enhancements and bug fixes, SSMS 17.9 comes with several new features:
- ShowPlan improvements
- Azure SQL support for vCore SKUs
- Bug Fixes
View the Release Notes for more information.
It looks like the big push for this release was bug fixes, and there are quite a few of them.
Now, you do need to be aware that the settings Tools -> Options are just for new query windows. If you want to change the settings for an already existing window then either go to Query -> Query Options or right click in the window and go to Query Options from there.
Either way, once you are done and re-run your script you get this when you copy and paste:
Click through for the tip.