Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Management Studio

By Amit Lohia, (first published: 2005/07/07)

Let's look into SQL Server Management Studio. SQL Server 2005 provides us many new features. I will focus this article only on SQL Server Management Studio and the few features which will make life simpler for Database Developers and DBAs.

  1. Tab and Tab Groups

Multiple windows were possible with Query Analyzer, but it was not as friendly. With SQL Server Management Studio, your query windows are organized in Tabs and can be navigated easily. See below diagram:

Not only can we organize them in tabs but we can also create a Tab Group. Right Click on any tab (window) New Horizontal Tab Group or New Vertical Tab Group

You will notice a new Tab Group has been created. It is kind of similar to when we Tile horizontally or vertically, but we can move the query from the 1 tab group to another very easily. Just right click on the tab and select " Move to another tab group. Once you get into the practice of using tabs and tab groups, you will wonder how you managed without it.

  1. Changing Connection (This is my favorite)

In Query Analyzer once we open a query there is no way to change the connection to some other server. Instead you have to open a new query window (connection to different server) and copy the query to that window. For example, you received a query which needs to be implemented in Production. You want to test the query and its impact, so you run this on a QA or/and staging server, and then in production. With Management Studio you can change the connection for the query window. Right Click on the window and select " Connection", then "Change Connection…"


This will open the connection window and you can select a new connection.

  1. Filtering Objects

There are many times when we need to see the logic of a stored procedure, or update a table or view, but only vaguely remember the names of the objects. So we scan the objects in the enterprise manager, or query the system table (sysobjects) with “like” keyword. In Management Studio our life is simpler: we can filter the list of objects.

Expand the Database tree Right Click on Table or Views folder, select Filter, then "Filter Settings", which will open the Object Explorer Filter Settings.

You can filter the result based on the Name, Schema (owner) or Creation Date. Click OK, and you will notice the lists of objects are now filtered based on the conditions you specified.

Note: Stored procedures are under the “Programmability” folder.

  1. Result Sets

As we are aware in Query Analyzer or Management Studio we can output the result set as

  1. Text
  2. Grid
  3. File

To change the output format for a particular window in Management Studio, right click on window | Result to Result to Text , Result to Grid or Result to File. If you want to change the default: In Menu bar Tools Option Query Result. This will set the default for all other new windows (this will not have any affects on the windows which are already open).

There are many other options in the Query Result window, but the following 2 interest me the most:

  • There are many times when we have to send the result set to management, and after a few days, they will ask you to send the latest result set based on the same criteria. I would forget to save the query, or even if saved, did not recollect which one it was. So I started adding the query as a part of the result set. This used to take an extra step of copying the query, but thanks to Management Studio I can include the query in my result set. Check the box “Include the query in the result set” in “Results to Grid.”

  • In Query Analyzer, there was no way to copy the heading of the columns from a Result Set, if the output was in grid. For example, if you needed to copy the result set to an Excel file along with the column heading, you had to change the result to text with TAB delimiter, so the file would be formatted properly. In Management Studio, you can copy the column heading even when the result is in grid format. Check the box “Include column headers when copying or saving the results” in “Results to Grid.”

There are many other great features in Management Studio. Next time, we will explore the Logs Views, Activity Monitoring, Jobs and Jobs Monitoring.

I would like to thank Thomas Keller (http://www.tomkellerconsulting.com) and Steve Jones (http://www.dkranch.net) for proofreading my articles.

Total article views: 31499 | Views in the last 30 days: 10
 
Related Articles
FORUM

Spatial results windows in query editor

How to remove this windows from query result in grid.

FORUM

Cannot execute queries from Management Studio

Cannot execute queries from SQL Server 2008 Management Studio

BLOG

SQL Server – Show/Hide Results Pane in Management Studio 2012

In earlier version of SQL Server Management Studio (2005, 2008 and 2008 R2) you can show/hide result...

FORUM

sp_helplogins 'domain\account' Results Doesn't Match Management Studio Results

sp_helplogins 'domain\account' Results Doesn't Match Management Studio Results and sp_change_users_l...

BLOG

How to Copy Query Results From SQL Server 2005/2008 into Excel

I often work with DBAs who need to copy the results of a T-SQL query from SQL Server Management Stud...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones