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.
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.
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.
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.
As we are aware in Query Analyzer or Management Studio we can output the result set as
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
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.