This hands-on tutorial should help you in understanding the interface available for querying the SQL Server 2005 databases. Some of the major features will be discussed as related to their use rather than going into a lot of details. Querying the database is one of the most basic activities that is routinely and frequently performed.
In SQL Server 2000, the Query Analyzer provided excellent support for all database-related programming. However, the Query Analyzer was separate from the Enterprise Manager. Further, the Enterprise Manager had its own querying interface common to many other Microsoft products like Visual Interdev, VS 2003, and so on. Also, SQL queries could be run from the command line in the DOS screen using ISQL and OSQL utilities, although ISQL was on its way out. SQL Server 2005 shows excellent integration of all these different features into one master dashboard, the Microsoft SQL Server Management Studio as seen in Fig.1. Also, while Query Analyzer always demanded a login by the machine owner, with Windows Login, the Enterprise Manager generally did not.
Fig.1
SQL Server 2005
In SQL server 2005, the Query Analyzer and Enterprise Manager are integrated in the Microsoft SQL Server Management Studio (which will be called the Studio in this tutorial). When you connect to the Server, you will be connecting to both utilities. The OSQL is still retained in the Studio, but will not be supported in future versions; it is included in this version for backward compatibility only. The recommended utility to use is SQLCMD, which can be run in the Studio. You can run SQLCMD without accessing the DOS screen; in this case it uses a Microsoft .NET SQLClient. SQLCMD can also be run side by side with OSQL in the command line when it uses an OleDb driver.
After you start the Studio, you need to hit the New Query button, which changes the center region, which is now showing SQL Server AGENT as in Fig.2, in a new tab. This action also changes the main menu by adding the Query menu item.
Fig.2
Fig.3 shows how you may open up the querying tool. By clicking on the Query menu item in the Studio, you get access to all of its functionality. From this drop-down list it can be seen that all the functionalities are retained with a couple of additional ones such as SQLCMD mode, include/reset client statistics, etc.
The vertical drop-down sub-menu items are also repeated horizontally at the top of the query editor, as can be seen in Fig.4. You may also set the database you need to query using the drop-down (partially hidden in this figure by the Query drop-down, showing only part of Northwind). The Connection menu item lets you make a connection; Disconnect disconnects all queries, or changes the connection. The Design Query in Editor... will wake up the query editor with all needed items, as we will see later. The Results To lets you show the result of a query in a text, Grid, or file, functionality that is similar to what was in SQL 2000. The Query Option... submenu lets you configure the parameters needed by both the executed query as well as the result set returned.
Fig. 3
Fig.4 shows a simple query in the Query design plane. This pane is tabbed and clicking on the Summary tab; you would see summary information. This also shows the Northwind database being queried as well as two queries that can be tabbed for editing, or access. After you finish coding your query, you may test the syntax by going to Query ->Parse (or use the keyboard shortcut: CTRL+F5). Again it is assumed that developers/users will be transitioning from SQL Server 2000 to Studio and therefore they could customize their keyboard to emulate SQL Server 2000. The keyboard can be customized from the Tools menu as seen in Fig.5 by going to Tools ->Options and choosing Keyboard in the Environment node. The Standard Keyboard refers to the Studio default.
Again from the query menu, you can see the query Plan as in Fig.6. This plan is available for standard SQL statements and also for statements with the for XML clause. You might have observed that the tabs Messages and Results changed to Message, Execution Plan.
Fig. 6
The Query Designer
One way to call up the query designer was discussed earlier (from Query ->Design Query Editor...). The other option is calling from the query pane. The areas in the query pane (refer to Fig.6) where the query is typed-in and the results plane have context sensitive drop-down menus. By right clicking in an empty area in the query pane and choosing appropriate item, you can make all changes to the query as well as wake up the Query Designer, as shown in Fig.7. This was a feature in Enterprise Manager in SQL 2000, if you recall; in Visual Interdev (Visual Studio 6.0); VS IDE 2003; and other Microsoft data related products. For instructions on using this designer please look up the Book on Line (the BOL), or here.
In SQL Server 2000, Microsoft added T-SQL enhancements so that it can support getting XML into the database and getting XML out of the database. However, the support was not sufficient. Fig. 8 shows the result of running a for XML query in SQL Server 2000. Depending on the number of maximum characters allowed in the results, this could get even uglier.
Fig. 8
The same query in Studio is shown for comparison in Fig. 9. Now the result is shown with a hyperlink. Clicking on the hyperlink takes you to the hyperlinked XML document, albeit a fragment as shown in Fig.10. The <root> is still missing, but it's better than before. A more comprehensive discussion of XML support in Studio is not within the scope of this article.
Fig. 9
Fig.10
The Tools menu is also useful for setting other Query related items like ANSI_Defaults, and query execution related items as seen in the next two pictures. You may also configure to open new queries in SQLCMD mode if you so desire.
Fig. 11
Fig. 12
Summary
SQL Server 2005 shows the excellent manner in which several scattered tools in the previous version are integrated. Besides conferring total support for XML which is not discussed in this tutorial, the for XML clause a la SQL Server 2000 has been improved enormously by the hyperlink. By providing SQLCMD support, Microsoft will successfully transition developers from Osql to SQLCMD, as it is so much more convenient. In SQL Server 2000 you needed to click on object browser; this is now redundant because the objects are visible. Only some major improvements were covered in this tutorial; the reader will probably discover many more.
MS SQL Server 
/ 66 
ASP.NET Export to Excel and Word using VB.NET and C#
