The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
Welcome to day 2. This isn’t a lesson about TSQL. I’m assuming you’re good there. Using your query editor like a pro is all about saving key strokes and knowing a little bit about what is going on behind the scenes.
Setting Default Options:
Head over to Tools->Options->Query Execution->SQL Server->Advanced and make sure your defaults are what you are expecting. For example, concat_null_yields_null or Isolation Level. I also like that you can set deadlock priority. These are all things you could write into your query – but if you have something you ALWAYS do, make it the default – stop typing it all the time.
Hint: you can bring up this same window under the Query menu item also.
Making the most of Intellisense:
If you’ve used Visual Studio to write in C# or VB you are probably already very spoiled with intellisense. The built-in intellisense for SSMS isn’t nearly as nice, but there are a couple habits you can get into for making the most of it.
First: Tab is the keystroke that will complete your object with either the top-most item in the list or the item that you currently have selected. Hint: Alt+Right Arrow will bring up that list if it got closed or didn’t pop up for some reason.
Second: Take note that SQL can only provide an intellisense list based on how much of the query has been written. So for example, if you are writing a select from statement it knows your looking for a table or view from your current connection:
What it can’t do however is provide a list of columns until you complete the FROM clause. So, I recommend getting in the habit of stubbing out your select statements like this:
SELECT FROM dbo.myTable mt
Then coming back and filling in the columns you need. Now intellisense can suggest columns as you type out the table alias mt. (or whaterver you choose). Don’t forget… TAB to accept suggestion.
There are some third party tools that do intellisense better in my opinion but we’ll cover 3rd party tools more later.
Making the most of Alt, Ctrl, and Hotkeys:
FYI: This rest of this post uses animated GIF demonstrations. IE may have GIFs disabled. You can re-enable by going to Internet Options->Advanced->Multi Media.
It’s not just for this post. I mean, come on, without GIFs how could you watch DBA reactions !?!?
We covered setting up Query Shortcuts yesterday. Huge time saver there. In addition, there are a ton of hotkeys and ALT or CTRL combinations in SSMS that can save you significant keystrokes. The column based copy/paste for example is one I use all the time. Hold down ALT+CTRL while selecting with mouse or arrows and you can copy paste text in grids/columns as opposed to rows (the default). Works great when swapping out shema names, or copying lists of columns.
We will cover a lot of useful hotkey’s throughout the month. Much of the cool stuff you can do with SSMS starts with a hotkey. But, if you just can’t wait that long the full list of hotkeys supported by SSMS are listed on the MSDN site. Bookmark this list – learn hotkeys to your favorite tasks – all the cool kids are doing it.
Making the most of Drag / Drop:
Drag / drop from within the studio:
Pretty much anything from the object explorer can be dropped onto a query window. Try it. Databases, tables, columns, views, procs, functions… they all work. In almost all cases they will also show up with schema designator as well. (Obviously dragging a database over doesn’t).
When I first discovered drag and drop of programmable objects I remember wishing it also stubbed out parameters etc. but in a future post I’ll show you how to achieve this as well.
The best one? Jimmy May (@aspiringgeek) recently showed me at a SqlSaturday that you can drag the full column node under the table node onto the query editor and get a comma delimited list of all your columns in one click!! Check it out:
Drag / drop from outside the studio:
You can drag any SQL script from the file system into the query editor window and it will instantly open the text. Note that by doing this you are in fact opening the script file. This isn’t a copy/paste operation. So keep in mind if you make changes and save – you will overwrite the original file.
Any text file works, it doesn’t have to be a .sql file. In fact any file that the OS knows how to open will work, it just may not open in SSMS. Try dragging a word doc or an excel document for example and see what happens.
BUT WAIT!! Drag and drop does not work work for me!!
An interesting gotcha with drag and drop from the file system is that if you are running SSMS ‘as administrator’ or another user with the ‘run as’ option, drag and drop is disabled. You can still drag objects from the object explorer but not the file system. If you think about it – the security implications of that make sense.
Frankly one of the biggest key stroke savers in SSMS is the tools related to generating scripts. But we’ll cover that entire topic on day 5.
Today we queried like a pro, tomorrow we’ll take it up a notch and query like a BOSS!!