Here are a couple tips for optimizing your use of the SQL Server Management Studio editor. I was prompted for this editorial by reading an article in SQL Server Magazine called, “Using the Alt Key for Editing” by Michael K. Campbell.
In his article he demonstrated how you can select a column inside the SQL editor, and that any typing you perform will be entered in all the rows selected. His description is a lot better than mine.
I found a similar capability years ago using Microsoft Word where you could not only modify the edited contents with text you were typing, but you could also copy, past, and replace data from columnar clipboard contents. It’s easier to demonstrate than to explain. I’ll use the following select statement as an example.
This could be a statement with a lot more columns, and in that case the demonstration here saves even more time. The goal is to select just the column definition data without the preceeding spaces. Hold the Alt key down and select the data you want. It will look like the following.
Go to the bottom of your editor and create a new line and paste the contents. It should look like the following, which probably doesn’t surprise you much. However, the leading spaces are gone.
Now press paste again and you will probably be surprised to see what follows.
Because the data was selected and copied in a columnar format, instead of pasting two new rows after your previous paste, it appends the data to the end of the current position of the cursor.
If you were converting this to an update statement you could have variables defined with the same names as the columns. So, in order to change the value being updated from the alias, all you would need to do is replace the a. with an = @, and remove the commas before the value you set to @. Easier to demonstrate.
First select the a.
and replace that value with @
Make sure the dialog says that it will replace the selection or it will replace a. in a larger scope than you want. Your text should now look as follows.
Now you just have to remove those pesky commas and your ready to finish your update statement. You could simply select the whole thing and replace ",=" with "=". Or, you can use the alt key to select just the commas. I’ll use the latter to again demonstrate the columnar selection technique. Holding down the Alt key, select the commas you wish to remove as demonstrated below.
Use a Find and Replace to replace only the selected commas with a space.
.jpg)
Your final text modifications should look as follows.
Now you can convert the whole thing into an update statement. You’re probably beginning to see a couple aspects about this technique. First, the text needs to be farily closely aligned in the editor from a columar perspective in order to work. I align my code by default to make it more readable. As a result, this technique works well.
The second thing to note is that the value for this technique grows as the number of columns impacted by your SQL increases. If you only have a couple columns it is probably easier to just edit things manually. When you have 10 ore more columns, this technique tends to save a lot of time.
Share your editing tips here on SSWUG with your comments. Or, if you prefer, drop me an Email at btaylor@sswug.org.
Cheers,
Ben