Takeaway: SQL Server 2005 Management Studio is a phenomenal advance over the tools provided with SQL Server 2000. Check out the improvements in SQL Server Management Studio, which makes it easier to locate errors in your code and allows you to keep tabs on your reports.
By Arthur Fuller and Stephen Giles
Microsoft SQL Server 2005 contains a number of major new features, but the feature that we like the most is the SQL Server Management Studio. This tool is leaps and bounds beyond what was available in earlier versions of SQL Server. Here is a list of the 10 things that we find most useful about SQL Server Management Studio.
1. Combines the best features of many tools
In earlier versions of SQL Server, you had two main tools: a graphical administration tool (Enterprise Manager) and a Transact SQL Editor (Query Analyzer). The problem with this split is that we do development and administration on SQL Server (sometimes at the same time) and often have to flip back and forth between the two. In SQL Server Management Studio, the Enterprise Manager and Query Analyzer are combined into one common interface, allowing you to manage your servers graphically and to write Transact SQL.
SQL Server Management Studio also allows you to access an Object Browser for all registered servers, which combines the features of the Object Browser from Query Analyzer with the Server tree view from Enterprise Manager. In addition, it provides a workspace similar to Query Analyzer, with the expected tools like the Language Parser and the Graphical Show plan. Now you can write queries and scripts and manipulate objects with Wizards and Property sheets in the same tool at the same time.
SQL Server Management Studio's interface has a separate Registered Servers view that allows you to work with multiple servers at the same time. You can do this in Enterprise Manager; however, SQL Server Management Studio allows you to register server instances as well as all Analysis Services, Reporting Services, SQL Server Integration Services, and Mobile SQL instances. Thus, you can obtain an enterprise view or concentrate on the particular instances and objects of interest.
2. Work with projects and solutions
If you have worked with Visual Studio, then you are familiar with the concept of projects and solutions. In a nutshell, projects allow you to group files together and access them as a unit. A solution is a series of projects, enabling you to drill down to projects just as OLAP users can drill down to the data dimension of interest.
A project can contain .sql, .mdx, .xmla, and .dmx scripts. You can also add other files (such as XML or CSV files) to a project. Therefore, the project itself is a drill-down object.
To create a new project, follow these steps:
1. Click File | New | Project.
2. Choose the type of project you want to create (SQL Server Scripts, Analysis Services Scripts, or SQL Mobile Scripts).
3. Give your project and solution a name.
4. Select the path where you want to store the files.
5. Click OK.
Now you can define various data sources (if your project touches more than one database) and add files effortlessly (simply right-click the Scripts folder in the Solution Explorer and select the items to add). You can also import scripts into a project if you have done some work already.
(If you don't see the Solution Explorer in your SQL Server Management Studio, select View | Solution Explorer or hit [Ctrl][Alt]L.)
3. The tool is a data analyst's best friend
Thanks to the integration of the OLAP tools, SQL Server Management Studio is a great tool for working with your cubes. The object browser allows you to access Analysis Services objects to graphically manage your cubes. It also lets you write and execute MDX and DMX and XMLA statements from within the editor window, allowing you to run both OLTP and OLAP queries from the