ssms | SQLServer.info

5 Quick Tips For Using SQL Server Management Studio

October 8, 2014 by robert

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:

Select Tools -> Options
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

Show Query Execution Time

November 3, 2014May 1, 2014 by robert

One of the limitations of SQL Server Management Studio is that it shows query execution time in seconds. In most daily activities, this is accurate enough. But when it comes to query permanence tuning, it is helpful to have a more accurate measurement.

Here are a 3 different ways to show you an accurate query execution time:

Statistics On
To have your exact query time show in the separate messages tab, all you need to do is run the following queries.

SET STATISTICS TIME ON

GO

-- Insert query here

GO

SET STATISTICS TIME OFF

GO

Then you can view the time by clicking on the Messages tab.

Include Client Statistics
To get a lot of in-depth information about your query, you can turn on Client Statistics before running your query. The details of all the results will be shown in the Client Statistics tab after execution was completed.

Turn on client statistics by choosing the Include Client Statistics option from query menu.

You can then view the query time by looking under the Client Statistics tab by your query results

Inline Query Time Results
If you would like to just show the execution time in the standard results window, this query allows you to do this. It will also allow you to show exact time of execution of a specific code segment in a query.

To use this, just place the code that you would like to get the execution time for in the center of the below script. The exact time will be shown in the results.

DECLARE @Time1 DATETIME

DECLARE @Time2 DATETIME

SET     @Time1 = GETDATE()

-- Insert query here

SET     @Time2 = GETDATE()

SELECT  DATEDIFF(MILLISECOND,@Time1,@Time2) AS Elapsed_MS

Reference: http://technet.microsoft.com/en-us/library/ms190287.aspx

How To Enable Line Numbering

March 23, 2014 by robert

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.

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