A whole slew of editing tips
- 00:15 - Indenting and un-indenting code blocks
- 00:33 - Commenting and uncommenting code blocks
- 01:56 - Configuring index scripting settings (compression, partition schemes)
- 04:53 - Dragging table names and column lists from Object Explorer
- 06:53 - Getting column names for a table with Quick Info
Update: when dragging column names over, the column names are [now],[bracketed], that fix was released.
Transcript
There are lots of cool secrets in Management Studio when it comes to writing and editing TSQL.
One very simple thing is indenting code
I can highlight a whole block of code and then use Tab to indent across the screen, and use Shift + Tab to edge my way back and un-Tab to the left.
Similarly, I can comment and uncomment blocks of text easily, and I love this one
It’s another one of our chords.
This one is control + k, that’s the first part of our chord, and we’ll see this little message at the bottom of the screen saying “hey, I’m waiting for more.”
The second part of the chord, to comment it out, is control + c. I had a hard time remembering this one, and I can do it repeatedly. If I want to add a lot of comments I can just control + k, control + c like crazy.
Merril Aldrich on Twitter gave me the tip that he remembers this with the pneumonic, “Control Kansas City,” and for whatever reason, that has stuck with me for years, so thank you, Meryl.
To uncomment…
I can do control + k followed by control U, which I remember as, “Un-control Kansas City.”
Thank you, Kansas City, I think of you a lot when I’m editing code in Management Studio because this comes in handy a whole lot.
You know, a lot of times I have a big script block and I just don’t want to run part of it, but I want to keep it. So I temporarily control + k, control + c, and I feel like a magician. It’s really fun.
Scripting out advanced index options, like compression and partition schemes
One thing that didn’t feel like a magician, though, for a long time was scripting out indexes in SQL Server. I actually thought this was a bug, but there are settings that we can control in Object Explorer that control things a bit like whether or not, SQL Server will script out things like data compression on an index, or whether or not it uses a partition scheme.
I’m going to go ahead and make sure that this index has data compression on it in the WideWorldImporters sample database, and then I’m going to go ahead and check my setting for this under Tools and Options.
I need to scroll all the way down, and maybe this is why I never noticed this, it’s tucked away at the bottom. Maybe that’s my excuse, I don’t know about that, but expand SQL Server Object Explorer and go to Scripting.
There are so many cool things in here. You may want to tweak more than the two [settings] I do.
I like to make sure that ‘Script Data Compression Options’ is set to True. This defaults to False, so I’m going to double-click here to toggle this one to True, and then another one that I like to do is to make sure, and it’s always fun to find this one, to make sure ‘Script Partition Schemes’, I just toggled it, is set to True. That one also defaults to False.
This index isn’t partitioned, but it is compressed, so with those set, when I click OK, if I go into Object Explorer, and navigate to Sales.Customers in the WideWorldImporters sample database, here is Sales.Customers.
This is the index that is FK_Sales_Customers_AlternateContactPersonID, it’s this first index in the list. I’m going to right-click this and [select] Script Index As Create. Because of mis-clicks, I always try to go as slow as I can in Management Studio. I say [To] New Query, Editor Window.
Now, when I look in my With options, DATA_COMPRESSION = PAGE was scripted out, but that’s only because I went into my Tools and tweaked my Object Explorer scripting properties saying I would like you to script that out.
This is something I’ve had multiple folks ask me about. I thought this was a bug in Management Studio, I tweeted about it because I was looking to see if someone had filed the bug, and Adam Machanic said, oh, no, you can just set that in the Scripting Options. And whoa, amazing, I love it, I love it! It makes life much easier to be able to script those out.
Dragging over table names and column lists from Object Explorer
Another thing that it’s really useful to save time with in Management Studio, is typing out table and object names. Let’s say that I want to query the Website.Suppliers table, but I’m feeling a little bit lazy. Oh, it’s a view, it’s not actually a table. This works with views too.
We’ve got a view named Website.Suppliers. You may be familiar with the fact that you can drag the name of the table over. Let’s do that first. I’m going to grab the name of the table and drag it into the FROM [clause]. Very cool, right?
But it gets cooler.
If I want all the columns in the table, I can drag the whole folder, I am grabbing the whole Columns folder, and dragging that over and saying, I want all of these columns typed out. You may notice a difference. It did put the quoted safety brackets around our view name, but it didn’t safety quote bracket out, or quote name with brackets any of my column names.
Well… A community member made a suggestion and said, hey, could you quote name those too? And the suggestion has been currently just recently closed as fixed. It hasn’t shown up in the product [as of this recording], but soon in a release of Management Studio, when you drag that Columns folder over, you’ll see some safety brackets here. (Update: this fix was released in the product and now column names are bracketed when the columns folder is dragged into a session.)
Now, of course, we can edit it however we want. Really, really, that can be super useful.
It was Jimmy May, another community member who first showed me that trick with dragging the brackets over, or the whole Column folder over at a SQL Saturday once. Thank you so much, Jimmy.
Getting a list of columns with Quick Info (keyboard shortcut)
Another thing that I sometimes use is– especially if I don’t want all the columns– maybe I just want one or two columns, but I don’t really want the whole sp_help for the table, I just quickly want to know what columns are in a table.
Well, I can do SELECT *, and when I do SELECT *, if I am using the mouse and I hover the mouse in the exact right place, I can get a tool tip that has the column names as well as the data types. But what if I don’t want to use the mouse?
Well, if I type out SELECT * from the table name, I can do another chord. I can do control + k followed by control + i, that is the Quick Info chord, and let’s see if we can actually zoom at the magical moment where we get it not grayed out. There we go.
But as soon as a click it’s grayed out, but essentially, control + k and control + i will give me that same list of Quick Info for the column names, and this can– let’s say I want BuyingGroupName. I can start typing, and I want BuyingGroupName, and now, oh, I forgot what the list is. I can do it again and say, oh, actually, now OK, I want ValidTo and I’m going to autocomplete that, and oh, maybe one more.
Right, so… It can be a really useful way to see those names and it can work really nicely with IntelliSense’s other features.