Select Favorite SQL Server 2012 Articles

The following articles are a collection of my favorite SQL Server related posts over the last 6 months.

3 Steps to Work More Efficiently in SSMS. Posted 2/17/2013

Recently the 3 Steps to Work More Efficiently in SSMS article was one of my favorites. The three steps were:

  1. Display results in a separate tab.
  2. Hotkey for SELECT TOP 100 * FROM …
  3. sp_help by using Alt+F1

The Display results in a separate tab is one of the first things I do when working in SSMS on a new computer.

VARCHAR diff function Posted 2/13/2013

What made the VARCHAR diff function posting one of my favorite was how it built on a number of previous articles to finally get to something that was much more useful, all based on common table expressions. The VARCHAR diff function used code from a previous posting called Using a CTE to Split a String Into Rows with Line Numbers. A couple weeks after writing the VARCHAR diff function with CTE’s I ended up using this on a project to create an automated testing script to compare the output from an outdated code project to the results from the new T-SQL implementation.  The use of the VARCHAR diff function allowed for a single tester to test the work of 3 developers in a time far less than what was anticipated on this project.

The VARCHAR diff function probably isn’t something that anyone would use on a daily basis, but it is a massive time saver when needed.

TSQL 2012 – Generating letters with a SEQUENCE object Posted 11/9/2012

This article was a lot of fun. Having learned about how to use a SQL Server 2012 sequence object to generate letters at a SQL Saturday presentation, I just had to blog about this one. It was a fun post.

Adventureworks2012 data to Google GeoChart Visualization Posted 10/19/2012

On this article I pulled address data from the AdventureWorks2012 database, and covered how to extract it and reformat it correctly to display on the Google GeoChart Visualizations.

Visualization1

Again another fun posting.

I hope everyone enjoys these as much as I did.

3 Steps to Work More Efficiently in SSMS.

Throughout my career I have worked with different programming and database tools.  Thinking back the tools that I enjoyed using were the tools that were easy to get the job done, and the tools that I hated using were the tools that were the most difficult to use. Being good at what I do means mastering the tools so that I can be more productive with my time.

Yesterday at SQL Saturday 198 in Vancouver I learned lots of great things with SQL Server, but there were a couple small things that I picked up on to improve my overall performance when working in SQL Server Management Studio (SSMS).

I thought it might be fun to create a list of the thing that I normally do, along with a few new things that I learned just yesterday at SQL Saturday.

1.  Display results in a separate tab.

This has been my favorite for a couple of years now.  Here is how you set it up.

Open SSMS, and from the top “Tools” menu choose “Options…”.

From the options dialog navigate through the heirarchy to the Query Results, SQL Server, Results to Grid page as shown here.

ResultsToANew

You will need to check the “Display results in a spererate tab” checkbox, and I suggest that you also check the “Switch to results tab after the query executes”.

You will need to open a new query windows to see this change.  Once it runs you will end up with the results grid taking the entire query window area, and a new tab up top to switch back to your query.  This saves time when viewing results because you need to do less scrolling to see your results.   This is also impacts the execution plan window.  Give it a try it will look something like this.

tabs_instead_of_split_screen

Once you have this configured, there will be no need to battle with that split screen results again.

2.  Hotkey for SELECT TOP 100 * FROM …

Just learned this one yesterday at SQL Saturday.  Quite often when working in a database, I need to take a look at what some of the data looks like in a table, so I find myself opening a new query window and typing SELECT TOP 100 * FROM Table name. I find myself doing this all the time.  Yesterday I learned a new trick, and that is to set up a keyboard shortcut that allows you to highlighta table name anywhere in a query window, then just hit a shortcut key, in my case CTRL+3, and it runs the SELECT TOP 100 * FROM … query for the table name that is highlighted.

Here is how you do it.   From the options dialog navigate through the heirarchy to the Environment, Keyboard, Query Shortcuts page.

SSMS_Hotkeys

Here, choose the shortcut key you want to map, and enter “SELECT TOP 100 * FROM ” into the Stored Procedure column.  Yes, I know its not a stored procedure, but just give it a try.

Then hit OK on the dialog.  You may need to restart SSMS for this to work.

After restarting, then just highlight a table name in any query editing window and hit the short combination, CTRL + 3 in this example.  This will save me lots of time.

3. sp_help by using Alt+F1

Another big time saver, just highlight the name of a table, then hit Alt+F1, and sp_help will be run against that table name.

Thats my time saving for today.  3 steps to make your SSMS experience more productive.

Enjoy!

Whats your favorite SSMS time saving hotkey?

Recently I have been on the LEAN improvement quest to make a 2 second improvement in my process every day.

Part of that improvement is to avoid switching to the mouse when you don’t have to.  Here are some shortcut keys that I find very useful in SQL Server Management Studio, some of which I just discovered recently.

Window + L – Lock my Computer

F5 – My all time favorite – in a query window execute the query.

CTRL+M  - Turn on/off Actual Execution Plan

CTRL-F6 switch between top tabs

F6 switch between tabs (ie Editor, Results, Messages…)

CTRL-TAB window manager

Alt+F1 for sp_help and a list of its fields

CTRL+SHIFT+U  - shift word to uppercase

CTRL+SHIFT+L  - shift word to lowercase

Ctrl+K+C  – Comment block of text
Ctrl+K+U  – UnCommnet block of text

What are your favorite keyboard shortcuts in SSMS?

Please post your favorites.