Tip SSMS: How to BLOCK Select

SQL Server Management Studio has so much capability, yet most people I meet don’t seem to have read the manual to discover it.

So I thought I might post a few of features I find invaluable. This works for SQL 2005, SQL2008, SQL2008 R2 & Visual Studio.

Summary:

Tip 1: It is possible to “ALT + Select” to highlight a block of text to work on.

Tip 2: If you press ALT & stay in the same column (vertical line), it doesn’t select a block but instead selects an entire line, not just the partial top & partial bottom lines.

The following is a quick example of what I mean plus a few other uses you may not have thought of.

 

Example:

Tip 1: It is possible to “ALT + Select” to highlight a block of text to work on. eg: Hold the ALT key, Left Click & move your Mouse produces the Blue “Selected” region in picture below. You must press ALT prior to moving the mouse.

 

If I hit DELETE, then I remove just those characters so they all line up next to the NVARCHAR.

Just for comparison. If I just Click & Drag down without the ALT. I’d see all the lines selected. Starting from the Character I clicked on at the start line & finishing on a partial line I released the mouse.

Tip 2: If you press ALT & stay in the same column (vertical line), it doesn’t select a block but instead selects an entire line, not just the partial top & partial bottom lines. eg:

 

Other Uses:

1. Block Select an area of code to limit the effects of other commands.

eg: using CTRL+ALT+U to convert all selected text to Upper case.

2. Same can be done for :-

i) Search & Replace within a small area,
ii) Copying a column of Text next to itself &
iii) many other uses.

The following is somewhat contrived example of building really large WHERE clauses. The sort of TSQL you might create if you wanted to compare every row in the table with every row in the same table in a different database. Some people may do this when they are rewriting stored procedures & need to check that the new code gives the same result as the old code. Of course they’d use a FULL OUTER JOIN to ensure they noticed if they Deleted or Inserted any extra rows. (Clearly the data comparison feature in Visual Studio for Database Professionals is way faster & less error prone but not everyone has installed it.)   eg:

Step A: Create a large table, using SSMS “Script AS” or “SELECT Top N Rows” is handy for this.

 

Step B: ALT+Select the Column of  info you want & Paste it in the WHERE / ON Clause area.

Step C: Repeat but this time grab some Whitespace to push the rows to the right, which ensures the code still lines up so you have a vertical area you can conveniently select.

 Step D: Cut the Column names again & Paste to the left of themselves.

Step E: Highlight one of the columns & use Find & Replace, just to change values in the Highlighted area. eg: Remove the comma “,” & replace with an Equal & Table Alias “= R.”

 

Step F: Repeat above but this time Swap the comma for a Join Clause & Table Alias “ AND F.”  (Yes I also swapped the “WHERE” to an “ON” & added the 3 part table names)

 

Step G: Use some of the Advanced Editing Commands to tidy it up. eg: Delete Horizontal White Space ( Hit Ctrl K, then Ctrl \ ) & “Increase Indent” button.

 

Kind of a long post for such a simple feature. I hope the pictures stimulate your thinking in the wide variety of ways this can change the way you work.

enjoy

Dave.

 

Lame thought for the post. When we were out shopping my girlfriend suggested that it was about time we took our relationship to a whole new level. So I parked on the roof.

Technorati Tags: SQL Server,SQL Server Management Studio,SSMS