If you work with SQL Server for a long time you’ve probably learn some Keyboard combinations to speed up your administration or development process.
The full list of SSMS Shortcut keys you can find in MSDN
I will try to re-categorize the most interesting ones
If you spend a lot of time in Management Studio, learning keyboard shortcuts will make your life easier.
Sometimes when using SSMS you will see a redline under a table or object name in your T-SQL. This means SSMS thinks the object doesn’t exist in the current database. Usually it’s right, but if you have just created the object, the query editor wont know as it’s local cache is not regularly refreshed. To force a refresh you can hit Ctrl + Shift + R but I always forget keyboard shortcuts. For this I like to add a button to the toolbar.
This is a good intro-level article on SSMS basics and some configuration options.
Tip 4: Results Grid aggregates – Some people do a lot of calculations with their data, whether it’s sales data or whatever. You end up saving the query results to Excel and about five minutes later you have the totals, etc. With SSMSBoost, you can have your totals in seconds. Below I’m selecting 10 records from a sales related table. Say I want to get the SUM, MIN, MAX and Count of the SALEPRICE. All I do is slide my mouse down the column highlighting the cells and a pop-up will appear with that information:
This is probably my second-favorite feature of SSMSBoost; my favorite is automated crash recovery, and my third-favorite its snippet support. SSMSBoost has a free version and I use it myself. I try not to push many tools here on Curated SQL, but this is one worth checking out.
That is most difficult operation. At first, SSMS can’t show more than 5000 separate objects at the same time. In order to show more we have to construct “MULTIPOLYGON” or “GEOMETRYCOLLECTION”. That only the way to fit more objects into SSMS screen. However it is still limited.
In order to combine triangles in a single object we divide them in buckets (Line 106).
In this example I just making number of buckets approximately equal to a number of objects within each bucket. Making lower number of buckets will increase processing speed, but produce less colors. All objects in one collection will have the same color.
Also, I wrapped the last query in extra CTE to have more flexibility on results formation.
This is a fun post showing some of the power and limitations of geometry types in SQL Server and their display in SSMS.
One of the tips that I was super surprised that many people didn’t know is the Object Explorer Details. It allows you to delete multiple objects at once, script out multiple objects at once and just do some really cool stuff. How do I access this magic you are asking? When in management studio, click on View>>Object Explorer Details.
For those one-off jobs where you need to script out a dozen objects, this is very helpful.
Everyone knows you can use SHIFT + [Left/Right Arrow] to highlight text. But you can also use ALT + SHIFT + [Up/Down/Left/Right Arrow] to select a block of text or even make a vertical selection to insert a block of text on multiple lines. Or you can use ALT + [Mouse Drag] to make a block selection with your cursor.
Notepad++ works the same way. Every once in a while, I’ll run into a scenario in some tool which doesn’t implement Alt key functionality—especially certain non-Microsoft platform database products—and it will hurt a little bit inside.
Once I click Properties, I get a dialog with a lot of items on the left. The bottom one is for Extended Properties, with a simple add/edit/delete grid. Here I can see the property(ies) I’ve added.
However this is cumbersome for me. I’d much rather find a way to query the information, which is what I need to do with an application of some sort. I’d think sp_help would work, but it doesn’t.
Extended properties, like Service Broker, was a great idea that floundered because there was never a good UI. Given how much fighting Steve has to do to see one object’s properties, it’s no wonder they’re so relatively unused. And that’s a shame, because with the right tooling, they can be a great way of documenting data structures.
Every time I install a new version of SSMS I make a handful of changes to the default setup. For my own notes, and in case anyone is interested in some of the things you can do with SSMS I thought I’d post a list of those changes.
I also use a darker theme, very similar to Fisher’s; mine is designed to look like vim blue. Of course, personal SSMS settings are personal.
After you provision a Microsoft Azure VM with SQL Server there are a few more steps that you need to take to make remote connections. The procedure below starts with a fresh Azure VM provisioned and walks through the process of establishing a connection via SQL Server Management Studio, installed on an on-premises work station.
Note that this is Azure IaaS, not Azure SQL Database.