New SQL Server Management Studio

  • Article

SQL Server Management Studio is a new authoring and management tool for database administrators and developers. It combines the functionality of Enterprise Manager, Query Analyzer, and Analysis Manager, and also allows management and authoring for SQL Server 2005 Reporting Services (SSRS), SQL Server 2005 Integration Services (SSIS), SQL Server 2005 Notification Services, SQL Server Compact Edition, replication, and previous versions of Microsoft SQL Server, all through the same interface.

The new interface for SQL Server Management Studio provides the flexibility to host editors, management dialogs, wizards, designers, and browser in the same shell. Database administrators can author and execute a query, view thousands of objects on the server, manage an object or monitor system activity, and view online help, all at the same time. SQL Server Management Studio hosts a number of editors for authoring Transact-SQL, MDX, DMX, XMLA, SQL Server 2005 Compact Edition, and XML data. Components such as execution plan viewer, client statistics analyzer, and integration with Database Engine Tuning Advisor provide a powerful tool for performance tuning and optimization. It also hosts tools for managing SQL Server Agent and maintenance plans to assist the database administrator with daily maintenance and operation tasks. The integration of management and authoring in a single tool coupled with the ability to manage all types of servers provides enhanced productivity, flexibility, and manageability for database administrators. The principal SQL Server Management Studio features are discussed in greater detail below.

Integrated Management

SQL Server Management Studio integrates all management components of SQL Server, including all of the functionality previously available in SQL Server Enterprise Manager and Analysis Manager. The environment uses resizable, nonmodal dialogs, with many new capabilities. These dialogs allow you to perform a variety of tasks, such as backing up a database, and executing queries simultaneously.

SQL Server Management Studio includes:

  • Object Explorer. Object Explorer allows you to discover and connect to all SQL Server products in a tree hierarchy. Object Explorer can expand multiple nodes of the tree simultaneously, or stop enumeration of an expanding node. Object Explorer can filter display of a node's contents based on one or more filtering criteria.
  • Nonmodal dialog boxes. Users can now access multiple resources simultaneously in SQL Server Management Studio. For example, if they are viewing properties of a database, they can use Object Explorer to browse objects on the server or open another dialog box without closing the initial dialog box.
  • Scripting or scheduling from any dialog box. script your action to a query window, the clipboard, or to a file, or schedule it to be executed at a later time.
  • Enhanced message information. When actions fail, error messages include a "Show technical details" button, which presents detailed information from all application and programming levels.
  • New maintenance plan designer. A new maintenance plan designer provides an intuitive and powerful user interface for creating and managing maintenance plans.
  • Summary Window. As you navigate in the Object Explorer hierarchy, the Summary Window presents convenient information about the folder or object selected.

To see a complete description of this new tool use the links below or take the SQL Server Management Studio Tutorial.

Enhanced Query Authoring

SQL Server 2005 introduces Query Editor, a component of SQL Server Management Studio and the primary tool for authoring Transact-SQL scripts and executing statements against SQL Server and SQL Server Compact Edition. Query Editor also allows you to write and execute Analysis Server scripts such as multidimensional expressions (MDX), data mining expressions (DMX) and XMLA. Query Editor replaces and has many enhancements over SQL Server 2000 Query Analyzer, including:

  • The ability to write scripts with or without a current database connection.
  • Dynamic Help, which automatically displays context-relevant Books Online topics within SQL Server Management Studio. The new Help search engine also lets you search SQL Server Books Online and community partners for additional information.
  • SQLCMD Mode, where you can execute scripts containing operating system actions.
  • Improved display for XML results.
  • Integrated execution plan graphing tool for SQL Server and SQL Server Compact Edition.

Enhanced Project Management

SQL Server Management Studio provides a tool to organize related scripts, connections, and other files into projects to form a solution. The solution and projects can be managed in Solution Explorer, a tool for database developers who are creating and reusing scripts that are related to the same project. If you have created applications with Microsoft Visual Studio you will find Solution Explorer very familiar.

The project functionality is available for SQL Server, Analysis Server, and SQL Server 2005 Compact Edition. When a script is saved in a script project, users are able to:

  • Save connection information with each script.
  • Maintain version control on scripts.
  • Store results options with scripts.
  • Organize related scripts into a single script project.

Enhanced Templates

SQL Server Management Studio includes many additional Query Editor templates. The new Template Explorer provides an interface for creating custom folders and templates. It also provides the functionality to define templates for all supported script languages in SQL Server Management Studio.

See Also

Other Resources

Introducing SQL Server Management Studio
Features in SQL Server Management Studio
Writing, Analyzing, and Editing Scripts with SQL Server Management Studio
Developing Solutions and Projects in SQL Server Management Studio
Using SQL Server Management Studio Templates

Help and Information

Getting SQL Server 2005 Assistance