A guide to basic new features in SQL Server Management Studio 2008, part 1
This is the first part of two articles that cover the new features in SQL Server Management
Studio released in August 2008. The second part covers advanced
features in SSMS 2008.
When SQL Server Management Studio (SSMS) was first introduced with SQL Server 2005, Microsoft's
main goal was to combine some of the existing tools and to integrate functionality provided by
Query Analyzer and Enterprise Manager.
As expected, SSMS 2008 includes several new features and improvements – some of which have been
requested for years. This article will introduce several new useful SQL Server Management Studio
features, and later part 2 will
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
-
This was first published in December 2008
show more advanced new features.
IntelliSense support. IntelliSense is a feature that monitors the context of what you are
typing in the code editor and prompts you with syntax help or some likely choice to finish what you
are typing. When implemented properly, it can be a huge time saver. It's one of those features that
once you are used to it, you can never go back. If I recall correctly, this feature goes all the
way back to Visual Basic 5.0 and has been included in every version of Visual Studio. But for some
reason, the SQL Server team has been struggling for a long time to implement it.
IntelliSense appeared in the first beta version of SQL Server 2005 back in 2003, but it was
removed by the RTM version. Again, IntelliSense was present in the CTPs of SQL Server 2005, but
before the product was finished, Microsoft announced that the feature would
only be supported when connected to a SQL Server 2008. So
if you work a lot with SQL Server 2000 or 2005, you still have to rely on third-party tools such as
Red Gate Software Ltd.'s SQL Prompt.
IntelliSense has syntax help, word completion, a list of available database objects and a list
of system functions among other things. One feature I find very helpful is the listing of
parameters for a stored procedure call. Once you type a stored procedure name and hit the space
key, you should see the list of procedure parameters, including their data types. IntelliSense also
identifies misspelled keywords, similar to the way a spell checker does.
Region outlining. This feature implements collapsible regions by grouping together a set
of related statements and allowing you to quickly hide or expose entire sections of code. Just like
IntelliSense, it has been in Visual Studio for a while. Now, finally, database developers and DBAs
can improve their productivity by being able to better navigate within large code files. A region
is automatically created for a batch, BEGIN-END, BEGIN TRY-END TRY and BEGIN CATCH-END CATCH
blocks. The screenshot in Figure 1 shows region outlining. Both batches are the same, but the first
batch is almost hidden in a collapsed region.
Figure 1: Region outlining in SQL Server Management Studio 2008 features collapsible
regions. (Click on image for enlarged view).
Multi-server queries. With SSMS 2008, you can now execute the same script against
multiple servers simultaneously. This can be very handy for multi-server administration or for
easily comparing results from different servers. The feature works with the Registered Servers
window.
First, you need to create a group of registered servers. Next, you right-click on the group and
select New Query. The code in the query window executes against each server in the group
that's currently running and is accessible from SSMS. The status bar in the lower left corner shows
how many servers the query window was able to connect to, against the total number of servers in
the group. You should see something like 5/5 or 4/5, if one server in the group is stopped at the
moment. You can define whether you want to receive results in a combined result set or whether you
want a separate result set for each server. However, results can only be merged into a single
result set if all result sets have the same schema – that behavior is sort of similar to using the
UNION clause. You can also configure whether the result set should include the server name. The
screenshot in Figure 2 shows the configuration dialog for multi-server queries.
Figure 2: SQL Server Management Studio 2008 features a configuration dialog allowing
multi-server queries. (Click on image for enlarged view).
It takes some time to get used to the results returned by a multi-server query. In my opinion,
it is a good idea to include the server name in the results to easily identify where the data came
from. I personally prefer to not merge results into a single result; if the query returns a lot of
columns, you have to keep scrolling to the left to see where the data came from. You also have to
examine any error messages very closely to identify which server they came from and to determine
whether you need to take corrective action on some servers before you continue executing further
multi-server queries.
Custom colors in connection properties. SSMS allows you to define a custom color when
creating a connection to a SQL Server instance. Then when you connect to the instance, the status
bar for the query window shows in the color you selected. This gives you a quick visual hint to
help you figure out what server you are connected to without reading the details on the status bar
– and perhaps prevent you from executing your script on the wrong server.
One idea floating in the SQL Server blogs is to configure all development servers with one
color, QA servers in another color and then production servers with a color that really stands out
to help you stay extra cautious when working directly with production data. But, you have to be
really careful because this only works when you create a new connection but not when you change the
existing connection and point to another server.
If you are connected to Server A and the status bar is green, the bar will stay green when you
change the connection for the query window to point to Server B, even if Server B is registered to
show in another color. This could be potentially dangerous since you actually could be connected to
a different server than the custom color is indicating. Then you run the danger of accidentally
executing scripts on the wrong server. If you are going to use this feature, I recommend that you
train yourself to close the query window and start a new connection rather than reconnecting to
another server.
ABOUT THE AUTHOR
Roman Rehak is a senior database architect at MyWebLink.com in Colchester, Vt. He
specializes in SQL Server development, database performance tuning, ADO.NET and writing database
tools. Roman also serves as the technical chair for the SQL Server track at the annual DevTeach
conferences in Canada and is the president of the Vermont SQL Server User Group.
MEMBER FEEDBACK TO THIS TIP
Do you have a comment on this tip? Let us know.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation