Friday, January 9, 2009

The Lesser Known Features of SQL Server Management Studio – Part 2

In my last article, I went over some of the commonly expressed “annoyances” in SQL Server Management Studio, as well as infrequently known methods for alleviating each of these annoyances. In this article, we will voyage deeper into the bountiful reaches of SSMS, and explore a handful of other features which you may or may not know about.

Incremental Search

If you’ve never run into this before, you’re in for a treat. Traditional search (Edit > Find and Replace > Quick Find, or Ctrl + F) is very feature-rich in SQL Server. It allows you to specify case options, search direction, and even to use regular expressions and wildcard characters. For quick searches though, it’s rather clunky. It pops up a window overtop of the query text that does a really fabulous job of completely obscuring the text that you’re looking for. It’s also somewhat unforgiving with respect to exact matches - you’ll often find yourself searching for something like “Employees” and coming up with zero results, when really you’re looking for “Employee”.

image
Traditional Search (Ctrl + F): A better door than a window

Enter Incremental Search. Go ahead and open up SSMS, and script out one of your larger procs. Click on the query pane, and press Ctrl + I. Your cursor will turn into a little downward arrow with a set of binoculars beside it. Now start typing a word to search for. After each keypress, SSMS will jump to matching words. Hitting F3 will then cycle through the matches onscreen, regardless of their direction with respect to your cursor. Ahhhhh…..that’s much better.

Find in Files and Replace in Files

Ever scripted out a bunch of objects from a dev database, and gone to deploy them in a production db, just to find out that you scripted out the “USE DevDB” statements as well? You’re then stuck opening each .sql file and editing out the USE statements, or using a third-party utility like TextPad to search through the files. Not so when you use “Replace in Files” (Edit > Find and Replace > Replace In Files). Simply specify the string to find, the string to replace it with, and the area to search. You can search SSMS solutions, all open documents, the current document only, or even select a set of folders to search within.

Block Selection in the Query Window

Many a time have I meticulously written a query, complete with proper casing and perfect indentation, just to get to the last line (usually a GROUP BY) and find out that I didn’t leave enough space between the first and second columns – something like this:

SELECT sod.ProductID, SUM(sod.LineTotal) AS TotalSales

FROM Sales.SalesOrderDetail sod

WHERE SpecialOfferID = 13

GROUP BY sod.ProductID -- Curse you GROUP BY!!!!


It’s not so bad in this case, because there are only three lines to “fix”. A real pain in the neck when you have ten or fifteen lines in your SELECT and JOIN statements, if you have to individually adjust the spacing for each line. Block comments save you the trouble of adjusting each line individually. Simply press and hold “alt”, and then using the mouse, select the first few characters from each of the lines that you want to bump. It’ll look something like this:

image

Hit tab, and your rows will be back in alignment.


Fetching Column Lists Using Object Explorer

Best practices dictate that you use “SELECT *” as infrequently as possible (with a few exceptions). It’s a real pain in the butt to type out all of those column names though, isn’t it? You have two options here. One, which most of you are probably already aware of, is to script out a SELECT to your clipboard. To do this, right-click on the table, and select “Script Table As > Select To > Clipboard. You’ll get something like this:

SELECT [SalesOrderID]

,[SalesOrderDetailID]

,[CarrierTrackingNumber]

,[OrderQty]

,[ProductID]

,[SpecialOfferID]

,[UnitPrice]

,[UnitPriceDiscount]

,[LineTotal]

,[rowguid]

,[ModifiedDate]

FROM [AdventureWorks].[Sales].[SalesOrderDetail]

GO

Many people don’t use the “one column per row” format, and standards are important, so for those of you who don’t, you can get a listing of all of the columns in the table in a single row by expanding the table in Object Explorer, clicking on “Columns”, and dragging the folder into the query pane. You’ll then need to break that “single row” into an appropriate number of reasonably sized rows. JFYI – this also works for a number of other Object Explorer nodes, such as triggers, indexes, and the like.



That’s all for now – I’ll cover more SSMS tips and tricks in my next article. Before I “go”, let me draw your attention to the Community menu at the top of SSMS. Without even leaving SSMS, you can Ask a Question on the MDSN Forums, check on the status of any threads that you’ve opened up, or even submit SQL Server Feedback on Connect. There are a number of other options on the Community menu that I haven’t gone into, so be sure to check each one out!

8 comments:

Mike Walsh said...

I am hanging my head in shame right about now!

I have been using SQL Server 2005 since the CTPs and I never used CTRL-I or the Block Move tricks.

These are great! Exciting, even (kind of geeky to be excited by features of a tool but hey..)

Sankar said...

Aaron,

Good one. You can add about these too.

1) while testing queries that return lot of data, you have to scroll down but you have an option to display the results in separate tab.
Options-> Queru Results-> Results to Grid -> Display results in separate tab.

2) Making the splash screen go away

3) opening a default file everytime you open the SSMS.

Aaron Alton said...

Thanks Sankar. I'll add 1 and 2 to the next article. #3 was in my last post ;)

Aaron Alton said...

@Mike - Neither of those really jump out at you, do they? I found the incremental search just the other day.

Mike Walsh said...

@aaron - no not at all. They are great features though. I like these much better than Part 1's ;)

Jim McLeod said...

Incremental search is great! I've gotten so used to Control-F, type, hit escape, F3, F3, F3, F3.

Thanks for pointing it out, Aaron!

schroe said...

Incremental search - yum! Thanks! Thanks also for some other good SQL resources!

Anthony said...

Nice tips.
Thank you, Aaron!