SQL Server Management Studio Keyboard Shortcuts

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Although some of these keyboard shortcuts may seem a little odd at first… once you start using them I think that you’ll love them and use them all the time.

Ctrl + R – Show/Hide the results pane

Ctrl + N – New query window

Ctrl + Shift + U – Switch current selection to uppercase

Ctrl + Shift + L – Switch current selection to lowercase

F5 – Execute the currently selected query (or entire query if nothing is selected)

Ctrl + Tab – Switch to the next open tab

Ctrl + Shift + Tab – Switch to the previous open tab

Ctrl + Space – Pops up IntelliSense complete if it is not already open

Ctrl + Shift + R – Refresh local IntelliSense cache

F8 – Open the Object Explorer if not already open

 

 What other SSMS keyboard shortcuts do you use?

SQL Server Pagination

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Paging through data is something that developers need to do frequently.  Until SQL Server 2012, the best way to achieve this is with the ROW_NUMBER function… and let’s face it… that wasn’t the easiest/most elegant thing to use.  Our prayers have been answered in SQL Server 2012.  The SQL Server team has come out with a better way of doing pagination using the OFFSET FETCH clause.

 
The OFFSET FETCH Clause

The OFFSET FETCH clause allows the client application to pull only a specified range of records.  To implement pagination using the OFFSET FETCH clause, it takes two parts… the OFFSET and the FETCH. 🙂

NOTE: To use the OFFSET FETCH clause for pagination, you must use an ORDER BY clause as well.

 
OFFSET

This part tells SQL Server to skip N number of rows in the result set.

The above statement tells SQL Server to return all of the AnimalNames in the Animal table, but only return the names after the 50th.  So 51 and beyond.

 
FETCH

This part tells SQL Server how many records to return in the result set.  If you use FETCH, you always need to use OFFSET.

The above statement tells SQL Server to return the AnimalNames in the Animal table with row numbers 51-60.  The OFFSET clause tells it to skip 50 rows and the FETCH clause tells it to return 10 records.

 
Variable Row Counts

You are also able to use a variable for the record counts in the query.  If you wanted to do this, it would look like this:

 
 
Reference: http://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx

 

5 Quick Tips For Using SQL Server Management Studio

Facebooktwittergoogle_plusredditpinterestlinkedinmail

1) Script multiple objects by using the Object Explorer Details.

Scripting objects like tables and stored procedures is one great feature of SQL Server Management Studio.  But the way to script multiple tables at one time is not very straight forward.

To script multiple objects, first thing you need to do is open the Object Explorer Details window.  This is located under the VIEW menu at the top of the screen.

Object Explorer Details

When you click on an object’s folder (Tables, Stored Procedures, Views, etc) in the Object Explorer (located on the left of the screen), the Object Explorer Details window will show all of the available tables.  You can now highlight multiple objects… then right click and select the Script As option.

SQL Server Object Explorer-details-window-script

 
 
2) Add Line Numbers To The Query Window

Line numbers can be very useful when doing development.  By default, SQL Server Management Studio has this turned off.  To turn this on:

  1. Select Tools -> Options
  2. In the tree on the left, select Text Editor -> All Languages. Under the Display heading, check the box for Line numbers.

Enable Line Numbering

 
 
3) How To Refresh The IntelliSense Cache

If you’ve ever added a table or stored procedure, you know that the IntelliSense does not know about it.  You can refresh the cache pretty easily.  To refresh the IntelliSense cache, just selecting Edit -> IntelliSense -> Refresh Local Cache.

SQL Server IntelliSense Refresh Cache

 
 
4) Highlight Columns Of Text In Query Window

This trick actually works in Visual Studio also.  You can highlight and manipulate text in different rows without having to highlight the entire row.  This is a little hard to explain, so I will show you pictures of what I mean.

Query Highlight Columns

To do this fancy trick, all you need to do is hold down the ALT key while you click and drag.

So, how is this useful you ask… good question.  I use it for 2 different things.

  • First is for aligning the sql statement. If you highlight text you can just hit tab and it will tab the highlighted text in more.
  • The second thing I use it for is copying data. If you only want to pull out a specific set of data, this is super easy.

 
 
5) Drag And Drop Objects On To Query Window

If you ever want a fast way to place a table name, column name, or other object in to a query… you can just drag and drop.  Just drag the object (table, column, store procedure, etc…) from the object browser and place it where you want it on the query window.  It will insert the fully qualified name in to the query with no typing needed.

Drag-Drop Objects

How To Enable Line Numbering

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Line numbers can be very helpful when writing/debugging code in SQL Server Management Studio. Below are the simple steps to turn on line numbering in the query window.

Before and After

Step 1

From the menu at the top, select Tools –> Options

Select Tools --> Options

Step 2

From the list on the left, choose Text Editor and then All Languages.  Then select the Line numbers check box on the right.  Choose OK to save your settings.

Line Number Options