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 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...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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.
[IMAGE]
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.
[IMAGE]
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:
[IMAGE]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. He regularly contributes SQL Server articles to Visual Studio Magazine, SQL Server Magazine and other technical publications and presents at user groups and conferences in the U.S. and Canada. He is an active member and volunteer for the Professional Association for SQL Server. Roman also serves as Tech Chair for the SQL Server track at the annual DevTeach conferences in Canada and is president of the Vermont SQL Server User Group.
MEMBER FEEDBACK TO THIS TIP
Do you have a comment on this tip? Let us know.
