SQL Management Studio, part 1
When you work with MS SQL Server, the most important tool is the Management Studio.
With it, you can create and maintain databases, do back-ups and take care of security.
And of course it is the tool to write your queries with.
So when you spend a lot of time in Management Studio, be sure to know these tips and tricks.
Controlling your workspace
Full Screen for Coding
When writing queries, especially with stored procedures or complex views, I like to have some space.
Using the key combination Shift+Alt+Enter you can switch just the Coding section on Management Studio in Full Screen Mode.
Additional benefit, no distraction as well.
But what if you want to have a look at the Estimated Execution Plan?
Then you can use Ctrl+L to bring it up.
Rather see the Actual Execution Plan?
Just press Ctrl+M before running the query and you will see the Tab appear in the results window.
And if you want to skip out of the Full Screen Mode?
Just pres Shift+Alt+Enter again and you are back in the usual view.
Hide the Results
Especially when you have to scroll up and down a lot, the Result Pane doesn't make it easier.
So while writing your query, you might not want it for a moment.
Then Press Ctrl+R to make it disappear. And when you want it back, just hit Ctrl+R again.
Or just run the query and it will automatically be back.
Speaking of Scrolling
When going through a large query, I prefer to use the Scroll Bar in Map Mode.
With this you will get a mini version of the full code in the Scroll Bar.
When you hover it, it will show you a preview of that section.
By clicking on it, you directly jump to that section without scrolling:
To enable this, go to Tools, Options. And then go for Text Editor, All Languages, Scroll Bars.
Now simply enable "Use Map Mode for vertical Scroll Bar" and save the settings.
Use Multiple Tab Rows
When working all day in Management Studio, you might get a lot of Tabs with queries open.
This might get confusing.
Luckily there is a Setting to put your pinned tabs on a different row than the non-pinned tabs.
Usually I pin the Views, Procedures, Triggers or Functions I am working on so they are in the top row of the tabs.
Ad hoc queries I do in a new tab and I don't pin them.
That way, what you are working on throughout the day is always visible.
Temporary things are just that, so it is not a problem if they disappear in the background and are a little bit harder to find.
Save your time typing
Programmers are usually lazy when it comes to typing.
That's what intellisense is for, right?
You can use that for table or column names and a lot of build in functions as well.
But sometimes it's not enough.
Let's say you need to select everything from one table into another table.
This could do the trick:
select *
from SourceTable
But wait, this will not work because there is an Identity Column in there.
Now you need to type out all the column names except that Identity Column.
But there is a nice trick for this that will save you a lot of typing.
Just browse the Object Explorer and go the the Table or View from which you need the Column Names.
Expand it so you can see the Columns Folder.
Now drag that folder to where you want the Column Names to appear.
And yes, there you have all the Column Names, nicely comma separated.
Some usefull Shortcuts
Ctrl(+Shift)+Enter
I like to keep blocks of code separated for better readability.
So between one block of code and the next one, there should be a blank line.
That's where this Key Combination comes in.
Ctrl+Enter adds a blank line before the line where the cursor is.
By adding the Shift as well, so that's Ctrl+Shift+Enter, it adds a blank line after the line where the cursor is.
Ctrl+K+C & Ctrl+K+U
To comment out a line so it is not executed, you can add 2 dashes at the beginning of the line.
Well, good luck if you want to comment out a whole chunk of code.
Actually, it is very easy.
Just select the lines of code you want to comment out and hit Ctrl+K+C (that's C for Comment) and you are done.
Do you want it back?
Just hit Ctrl+K+U (U for Uncomment) and you are back in business!
Ctrl+Shift+V
Everyone knows Ctrl+V to paste something from your clipboard.
Better said, it pastes the last thing you put on your clipboard.
But you can also use Ctrl+Shift+V.
The first time it is identical to Ctrl+V.
But when you hit the V once more, it changes to the previous thing you put on the clipboard.
Just keep hitting V to go back in time until you have what you are looking for.
The Universal Backup
Imagine, you have the latest version of SQL Server running and make a back-up for someone else.
But unfortunately, that person does not have the latest version and cannot upgrade to the latest version.
Well, end of story, it seems.
You cannot restore a back-up from a newer version into an older version
since there might be functions used that did not exist in the previous version.
Even if you are sure that you did not use something that is exclusive to the latest version, SQL Server will not allow it.
But there is a Workaround for this!
Right click the Database that you want to back-up and select Tasks, Generate Scripts.
In the first step of the wizard it is selected by default to script the entire database.
Leave it like that because that is what we want to do.
The trick is in the next step. Click the button Advanced to see a lot more options.
Scroll down to the last option in the General section: Type of Data to script.
By default only the Schema is selected. Now change this one to Schema and Data:
Now specify if you want to save the script to a new window or file and generate the script.
You can also save it to clipboard but I do not recommend that, especially not with larger databases.
Now give the script to the person that needs a copy of your database and let him/her run the script.
Do note that the File Path for the Database Files is in the script, so you might need to change that.
But yes, this is an easy way to create a back-up that might very well work on older versions.
Unless you might have used something that did not exist in the targetted Server Version.
But that will give an error and in many cases you can simply change something back to the older version.
And it isn't really the best way to do it with really large databases.
So yeah, it's not bullet proof but this one has saved us many times when moving Test Databases from one developer to another.
That's it for today!
Some useful Tips and Tricks for working with Management Studio.
If you like to work with Shortcuts, be sure to check my previous posts on that topic
(part 1, part 2 in Dutch) here as well.
As usual, let me know which one you liked best.
And if you have one that I did not mention here, let me know so I can include it in a future post!
You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!