Steps To Setup Query Shortcuts in SQL Server

SQL Server Management studio provides a very useful functionality called query shortcuts. This makes the task of querying the sql server easier. Just by pressing two keys on the keyboard a complex query can be executed.

Lets take a scenario where a query is very frequently used.

Lets suppose the query to be
[sql]
SELECT top 10 * FROM PRODUCTS
[/sql]

Now instead of writing the query every time and executing it a query short cut can be setup for this query.

Steps to setup query shortcut in SSMS in detail:

1) Select tools menu and then select options

2) In the options expand environment and then click on Keyboard.

3) In the query shortcuts plane give the query in the Stored Procedures section for one of the shortcuts.

query shortcuts
query shortcuts

4) Click on OK.

Now in order to execute the query using shortcut click any where inside the query window and press ctrl+[shortcutnumber] and the query gets executed. Simple!!!

As shown in the screen shot above the shortcut is ctrl+3.

Lets see a more advanced scenario where the query would be
[sql]
SELECT top 10 * FROM PRODUCTS where productid = 100
[/sql]

In order to execute this query using shortcut just type
[sql]
where productid = 100
[/sql]

and select the text and press ctrl+3. Do not forget to add space before the where clause.

Done!.

In order to simplify this we can add the below query as shortcut.
[sql]
SELECT top 10 * FROM PRODUCTS where productid =
[/sql]

Now to find top 10 products that have productid as 100 just type 100 select it and press ctrl+3.

Note:
When a new query shortcut is created you have to open a new query in order to use it.
All the open query windows will not have the updated query shortcuts.

Custom Coloring Query Window in SSMS

Sql Server Management Studio Provides a feature to custom color SSMS for each registered server.
This feature will be really useful when the user needs to identify the kind of server he is actually connected to so that he can be careful while executing the queries.

In a generic situation a developer can select the following Custom colors:

  • Development Server – Green Color
  • UAT Server – Orange Color
  • Production Server – Red color

Following are the steps to set up custom color for a Server:

1) Open registered servers dialog in ssms.
  Click on view > Registered Servers
  Alternate way to open registered servers is by using the shortcut Ctrl+Alt+G

2) In Registered Servers window expand Database Engine then Right Click on Local Server Groups

3) Now select New Server Registration

4) In the New Server Registration window give the server name and authentication details

New Server Registration
New Server Registration

5) Click on Connection properties tab.

New Server Registration Connection Properties
New Server Registration Connection Properties

6) You can find Use Custom Color option where the necessary custom color can be selected for the registered server. Select a color and click on test and then on save.

Now the process of registering a server and giving it a custom color is complete.

In order to test the custom color expand Local Server Groups in registered servers.
You will find the newly registered server here.
Right Click on the server and click on New Query.

A new query will open and you will be able to see the custom color for this server at the bottom of the query window.

Custom Color in query window
Custom Color in query window

Points to note:
1) The first point is that the custom colors can be opted only for those servers that are registered in SSMS. Only registered servers in SSMS can have custom colors
2) Custom color will be shown only when the query is opened from registered servers.
Shortcut to open registered servers in ssms is ctrl+alt+g

Cannot open object explorer in SSMS

Problem:
When you try to open object explorer in SQL Server management studio object explorer does not come up.

Fix:
Reset the window layout.
To reset the lay out in SQL Server Management Studio, Select Window Menu and Select Reset Window Layout Option

  • Window > Reset Window Layout

    Reset Window Layout

This should fix the issue.

Additional Problem details:
All the options of opening object explorer will be unsuccessful and object explorer does not show up.
using menu view > object explorer
using F8 Shortcut key

Even if SSMS is reopened object explorer does not show up, also after restarting the system object explorer is not visible.

Using Grouping Sets with Group By Clause Example

Grouping Sets is newly introduced in SQL Server 2008.

The use of Grouping Sets is that it simplifies the process of using group by on multiple columns one at a time.

Following are the steps that can be performed using a single Grouping Sets statement:
1) Group by on one column and calculate the result
2) Group by another column and calculate the result
3) Union the the result sets in steps 1 and 2

Following is the generic example for using Grouping Sets:

Let us consider a table @Invoice which contains the sales data with details of the product subproduct and amount. This table has some test data.
[sql]
DECLARE @Invoice TABLE(
Product varchar(50),
Subproduct varchar(50),
Amount int)

INSERT INTO @Invoice
SELECT ‘PEN’,’NIB PEN’,100
UNION
SELECT ‘PEN’,’NIB PEN’,200
UNION
SELECT ‘PEN’,’INK BRUSH’,200
UNION
SELECT ‘PEN’,’QUILL’,250
UNION
SELECT ‘PEN’,’QUILL’,500
UNION
SELECT ‘PEN’,’QUILL’,250
UNION
SELECT ‘PEN’,’REED PEN’,300
UNION
SELECT ‘PENCIL’,’Coloured pencil’,50
UNION
SELECT ‘PENCIL’,’Coloured pencil’,60
UNION
SELECT ‘PENCIL’,’Carbon pencil’,10
UNION
SELECT ‘ERASER’,’gum eraser’,20
UNION
SELECT ‘ERASER’,’gum eraser’,50

[/sql]

With the above table following will be the query to calculate the total sales for each product individually and the total sales for each subproduct individually:

[sql]
SELECT NULL AS Product,Subproduct, COUNT(*) FROM @Invoice
GROUP BY Subproduct
union all
SELECT Product,NULL AS Subproduct, COUNT(*) FROM @Invoice
GROUP BY Product
[/sql]

Output of the above query will be:

Grouping Sets
Grouping Sets

The same result set can be gained by using Grouping Sets as shown in the below query:
[sql]
SELECT Product,Subproduct, COUNT(*) FROM @Invoice
GROUP BY GROUPING SETS(Product,Subproduct)
[/sql]

If the result set has to be grouped by Product and Subproduct, Product, Subproduct
Following would be the union all statement:
[sql]
SELECT Product,Subproduct, COUNT(*) FROM @Invoice
GROUP BY Product,Subproduct
union all
SELECT NULL AS Product,Subproduct, COUNT(*) FROM @Invoice
GROUP BY Subproduct
union all
SELECT Product,NULL AS Subproduct, COUNT(*) FROM @Invoice
GROUP BY Product
[/sql]

This can be achieved simply by using grouping sets as shown below:
[sql]
SELECT Product,Subproduct, COUNT(*) FROM @Invoice
GROUP BY GROUPING SETS((Product,Subproduct),Product,Subproduct)
[/sql]