Editing Transact-SQL Code in SQL Server 2005 Beta 2
Solid Quality Learning
Microsoft SQL Server
SQL Server 2005 Beta 2
SQL Server 2005 Management Studio
Transact-SQL (T-SQL) code
Summary: Get an overview of the T-SQL development tools available in Microsoft SQL Server 2005 Management Studio. (11 printed pages)
General Features of Query Editing in Management Studio
Managing Transact-SQL Queries
Using the Transact-SQL Code Editor
Configuring Management Studio for Transact-SQL Editing
Management Studio is the primary tool for developing Transact-SQL queries in Microsoft SQL Server 2005 Beta 2. The following discussion introduces you to the Management Studio Transact-SQL editing facilities, highlighting some of its more interesting and compelling new features for Transact-SQL development. You'll also learn how Management Studio differs from, and improves on, the SQL Server 2000 Query Analyzer. You should already be familiar with how to start and operate SQL Server 2005 Management Studio, how to connect to a SQL Server 2005 instance, and how to manipulate the various windows in Management Studio.
The SQL Server 2005 Beta 2 Management Studio is a work in progress; new features will emerge, and some features will change, by the time SQL Server 2005 reaches final release. In the following discussion, all references to Management Studio are to the version installed with SQL Server 2005 Beta 2, and all references to Query Analyzer are to the version that comes with SQL Server 2000.
Management Studio integrates query editing across all SQL Server database products. When you start a new query from the New Query toolbar button or from the File/New Query menu, you will immediately see that you can create and edit queries for SQL Server, Analysis Services, and SQL Mobile. When you work with SQL Server queries, Management Studio invokes the Transact-SQL code editor. When you work with Analysis Server queries, Management Studio invokes the MDX, DMX, and XMLA editors, and when you work with SQL Mobile queries, it invokes a special code editor that covers an appropriate subset of the Transact-SQL language. The focus of this paper will be on using Management Studio to edit Transact-SQL queries.
Before focusing in on Transact-SQL queries, it's useful to draw attention to some general features of Management Studio that stand out in contrast to Query Analyzer. One of the more striking features of Management Studio is that you can now edit in either a connected or disconnected mode: editing a query does not require that you be connected a server. When you start a query, you are prompted for a connection, but if you decline you may still continue editing. You can even drop or change a connection during your editing session without having to save and reopen the query. This allows you to develop code offline, or successively run code on multiple SQL Servers.
When you work with queries in a connected mode, Management Studio uses the SQL Native Client to connect to SQL Server, Analysis Server, or SQL Mobile. While Management Studio resembles Microsoft Visual Studio in many ways, it is a distinct tool that has been developed using Visual Studio managed code. Management Studio supports creating and editing SQL Server, Analysis Services, and SQL Mobile queries. You must use Visual Studio to develop SQL CLR code.
You can also edit plain text files in Management Studio. For example, if you invoke the Open dialog from the File menu to open a text file, Management Studio will not prompt you for a connection, and will open a plain text editor. You can also indirectly invoke an XML editor, which will be discussed below under Analyzing Queries.
There are two fundamental ways that you can edit Transact-SQL code in Management Studio. You can directly edit database Transact-SQL code objects (such as stored procedures, triggers, views, and functions) using the Assisted Editors from the Management Studio Object Explorer, or you can edit Transact-SQL scripts saved as disk files.
Directly Editing Database Code
Management Studio's Object Explorer is a natural companion for interactively editing Transact-SQL code, and you will often want to keep it available, whether docked or hidden, while you are editing Transact-SQL queries. You can use it to start a new query from the current connection without having to fill in connection information in the Connection Dialog by drilling down to a user database, right-clicking over the database name, and choosing New Query.
Object Explorer also lets you directly and interactively edit Transact-SQL code using a new tool called the Assisted Editor. When you drill down into the Programmability node in Object Explorer and right click over one of the Programmability nodes, the New and Modify options on the resulting popup menu invoke an appropriate Assisted Editor. Assisted Editors are available for stored procedures, functions, triggers, and views. Figure 1, for example, shows the Assisted Editor in use for editing a stored procedure in the AdventureWorks sample database.
Figure 1. The Assisted Editor can be used for editing a Transact-SQL code object directly.
When you use an Assisted Editor to change a database code object, you are editing without using a disk file, and therefore without a project or solution, and without source code control. The Assisted Editor takes control of the header portion of a Transact-SQL code object, and creates and revises the header based on your input. You can then freely edit the body of the code object using the Management Studio Transact-SQL code editor.
Note If you have comments or special formatting placed in the header of a Transact-SQL code object, the Assisted Editor will remove them if your actions cause it to rewrite the line. Management Studio will warn you and give you the option of editing the code object in a query window so that you can preserve the comments.
When you use Object Explorer to create a new Transact-SQL code object or to modify an existing Transact-SQL code object in the Assisted Editor, you'll see a new Assisted Editor menu appear in the Management Studio menu bar. From the Assisted Editor menu you can set the permissions for the object, create or modify extended properties for the object, script the Assisted Editor action, and schedule the resulting creation or change of the object.
The new Extended Properties page in the Assisted Editor allows you to enter long text descriptions. When the Extended Properties page is active, click on the browse button to the right of the Value text box, and a new data entry dialog will appear for long descriptions.
Remember that editing database code objects using the Assisted Editor is direct and interactive. If you save the changes you made in it, they will be immediately applied to the database. Management Studio also supplies file-based editing facilities so that you can develop Transact-SQL code objects that are subject to change-control practices.
Script File Editing
In addition to interactive editing, Management Studio lets you edit Transact-SQL script files individually from disk, or through SQL Server solutions and projects. Just as with Query Analyzer, you can create and modify individual script files, and save a new query as an isolated script file. Also as with Query Analyzer, you can check these individual files out of a source code control system before editing, and check them back in after finishing. What is new with Management Studio is that you can organize your Transact-SQL script files into solutions and projects, and integrate those solutions and projects with source code control.
A Management Studio solution organizes multiple projects as a single unit of work. Each project you create is associated with only one query type: SQL Server, Analysis Services, or SQL Mobile; and every solution must have at least one project. Within a given project, you can store items concerning connection information, query script files, and miscellaneous files.
Note Management Studio solutions and projects are similar to, but not compatible with, Visual Studio solutions and projects.
Even when you begin a new SQL Server Query outside a project, Management Studio will start a new default Solution called "Solution1" with no projects. If you do not add a project, Management Studio will still ask you whether to save the solution Management Studio. However, the best way to create a new solution is to start by creating a new project. Just go to the File menu and choose New and then Project. The resulting dialog allows you to choose a new project type, name, and solution name. If you want to include multiple projects in a solution, make sure you give the solution a different name from your project, as shown in Figure 2.
Figure 2. You can name a new solution when creating a new project, and also specify a custom directory.
Note that you can have only one solution open at a time, but any given solution can contain multiple projects of any project type. For more information about solutions and projects, see "Introduction to Solutions, Projects, and Items" in SQL Server 2005 Beta 2 Books Online.
Management Studio integrates solutions and projects with source code control systems provided your source code control system provides you with a plug-in. You can then configure the source code control system from the Options dialog in the Tools menu. The Source Control/Plug-in Selection node in the selection tree presents the dialog where you can specify the current source code control plug-in. Once you have set up integration with source code control, you can add the SQL Server solution to the source code system, and check it back out. When you create new SQL Server queries from the Solution Explorer, a Pending Checkins dialog will assist you in determining what files will be checked in.
For example, if you have installed the Microsoft Visual SourceSafe 6.0 client components on your machine, SQL Server 2005 Management Studio will automatically detect that the VSS plug-in is available. As noted above, you can find the VSS plug-in listed in the Tools/Option dialog, by selecting the Source Control and then Plug-in Selection node. Also, the Source Control option in the File menu drop down becomes active, and from there you add an existing Management Studio solution to source control, or open a Management Studio directly from source control. You can enable multiple source control systems, and change from one to another.
In addition to these editing strategies, there are a number of Management Studio tools that support editing Transact-SQL and other code.
For example, you can jump-start SQL Server queries using Query Templates, which you can access through the Management Studio Template Explorer window. The Template Explorer allows you to use, modify, and add your own templates for all types of SQL Server queries, not just Transact-SQL queries. The Template Explorer SQL Server templates are compatible with SQL Server 2000 templates, using the same type of parameter substitution tags.
You can also run and debug SQLCMD queries using the Management Studio Transact-SQL editor, if you click on the SQLCMD button when you run the query. Management Studio will then recognize and execute SQLCMD-specific commands along with Transact-SQL commands.
Note SQL Server 2005 Beta 2 does not support debugging Transact-SQL code through Management Studio. You must use Visual Studio 2005 to step through Transact-SQL code.
For the most part, editing Transact-SQL code in SQL Server 2005 Management Studio is very similar to SQL Server 2000 Query Analyzer, but you will notice a number of enhancements to the authoring experience.
When you are editing Transact-SQL code in Management Studio, you will see mostly the same color syntax highlighting and editing options that you are familiar with from SQL Server 2000 Query Analyzer. In addition, you can now redo an edit, in addition to undoing it. You also have enhanced find and replace dialogs, which allow you to search based on regular expressions or wild cards, as well as search and replace across files.
Note Management Studio in Beta 2 does not have a tool corresponding to Query Analyzer's Object Search dialog.
Additional editing options in the Management Studio Edit menu include tabify and untabify options, which convert tabs to spaces and back, as well as the ability to delete horizontal white space. Also new is the ability to toggle word wrap on or off, and toggle whether to use a word wrap glyph. You can configure word wrap in the Tools/Options/Text Editor/All languages dialog.
You can comment and uncomment, and indent and un-indent text, using buttons on the default Standard toolbar. However, the Management Studio standard keyboard shortcuts for commenting and un-commenting Transact-SQL code have changed: rather than using CTRL+SHIFT+C to comment, and CTRL+SHIFT+R to un-comment, Management Studio by default uses CTRL+K followed by CTRL+C for commenting, and CTRL+K followed by CTRL+U for un-commenting. Just hold down the control key press K followed immediately by C, without releasing the control key, to comment code. (See "SQL Server Management Studio Keyboard Shortcuts" in SQL Server 2005 Beta 2 Books Online.)
Note You can change the Management Studio keyboard shortcut scheme from the Standard to SQL Server 2000 in the Tools/Options/Environment/Keyboard dialog. Beta 2 Management Studio does not allow you to define custom keyboard shortcuts.
In Management Studio, you can work with two styles of query editing windows: tabbed or MDI Document. The default setting is for tabbed windows, and you can select any open query window by selecting its tab. You can change the window layout to MDI windows in the Tools/Options dialog, in the Environment dialog. When you do this, Management Studio will automatically restart, but preserve all your connections and open windows.
Note Because Management Studio has many windows available, you may find it convenient to make the query editing window full screen by using the SHIFT+ALT+ENTER keyboard shortcut, which you can also access from the View menu. If you want to reset your windows back to the default setting, you can click the Reset Window Layout option found in the Window menu.
You can view query windows tiled vertically or horizontally by right clicking over a query window's tab and choosing either New Horizontal Tab Group or New Vertical Tab Group. Once you have done this, you can use the ALT+SHIFT+ENTER keyboard shortcut to maximize the query windows to get the tiled effect. Figure 3 shows two query windows tiled side by side using the Vertical Tab Group maximized with the SHIFT+ALT+ENTER command.
Figure 3. You can align two or more query windows by right clicking on the query tab and choosing New Horizontal/Vertical Tab Group, and maximize the windows with SHIFT+ALT+ENTER.
Working with Query Results
You can still use F5, ALT+X, or CTRL+E to execute queries, in addition to the Execute button on the Query toolbar. Similarly, you can direct results to text, grid, or a file. When you direct results to a grid, you can cause query results to be displayed in a separate tab window, and you can make this change apply to the next opened query window using the Options dialog under the Tools Menu. In the tree view of that dialog, drill down to Query Results, SQL Server, and Results to Grid. Then check the "Display results in a separate tab."
There are some limitations here, reflecting a work in progress. In Management Studio Beta 2 you cannot display text-based results to a separate tabbed window. When you do display grid-based results to a separate tabbed window, the tabs appear at the bottom of the window, and cannot currently be positioned at the top.
Although there is no toolbar icon associated with toggling the results window on or off in Management Studio, as in Query Analyzer, the CTRL+R command will still have the same effect. You can also use the Hide/Show Results Pane option from the Window menu, and add a button with the same options onto your Query toolbar using the Customize option from the Tools menu.
Management Studio adds new features in support of query analysis. In addition to the familiar graphical and textual showplan output, you can also direct showplan output to XML. The Database Tuning Advisor replaces the Index Tuning Wizard, and Client Statistics have a couple new features.
Graphical Query Plans
Just as in Query Analyzer, you can visually inspect the estimated and actual query plans, using buttons on the Standard toolbar, as well as clicking options on the Query menu. You will notice that Management Studio shows a revised set of icons for analyzing a query plan. In particular, operators are shown in blue, cursor physical operations in yellow, and language elements in green. The fly-over display on top of each node in the graphical query plan shows the operation's information more plainly, though to find the object (table or index) in question, you need to access the Properties page for the given node. (For a complete list of graphical query plan icons, see "Graphically Displaying the Execution Plan Using SQL Server Management Studio" in Beta 2 Books Online.)
Note You can zoom in on a large query plan using the + sign in the lower right hand corner of the graphical query plan window.
You can also direct showplan output to XML. If you invoke the command,
SET SHOWPLAN_XML ON
and execute a query with results to grid, the results set will contain the XML showplan as an XML document returned in a one-row, one-column table. You can then click on the internal link in the content of the cell to review the XML showplan within the Management Studio XML editor. The output is the same as when sending the results to text, but opening from the grid results cell invokes the XML editor.
Note Management Studio Beta 2 does not support exporting graphical query plans as XML documents, or importing XML documents into the graphical query plan display.
The Database Tuning Advisor
The SQL Server 2005 Database Tuning Advisor replaces the legacy Index Tuning Wizard, and you can invoke it for selected queries from the Query menu, or using the CTRL+I keyboard shortcut. (You can invoke the Database Tuning Advisor directly from the Tools menu.)
Client statistics are useful in determining how much time the network and client components contribute to the total response time of a query. Management Studio supports collecting Client Statistics from the SQL Editor Toolbar as well as from the Query menu. Client Statistics in SQL Server 2005 Beta 2 Management Studio gives you much more control over client-side statistics, by giving you the option to reset them, as well as displaying trial runs resulting in the average values. You can use Shift+Alt+S to set client statistics on, or choose the same option from the Query menu, from which you can reset client statistics. Each trial result is listed in the output, along with the average results over all trials. A new client statistic, total execution time, is added to Time Statistics.
Management Studio has three major configuration options that affect Transact-SQL query editing.
The Tools/Options dialog
You will find most Management Studio configuration options in the Options dialog from the Tools menu. This dialog serves as a central location for the Management Studio default options for all query editing. When you change an option related to SQL Server queries, then just as in Query Analyzer, your changes will affect the default settings for all subsequent queries. However, configuration changes vary as to when they take effect. Some of the choices vary depending upon the type of queries (Transact-SQL, Analysis Services, SQL Mobile, and so on) that you want to configure. Sometimes changes are applied immediately to all your query windows, sometimes only to subsequent windows, and sometimes Management Studio must restart itself to apply the change.
Note Management Studio does not provide the ability to manage table indexes or table statistics from the Tools menu. These operations have been moved to the Object Explorer: just drill down in the Object Explorer to the Indexes or Statistics node of the table in question.
Not all options available in the Tool/Options dialog are active in Beta 2. For example, in the Text Editor/All Languages/Tabs dialog, you can choose between no indenting, block indenting, and smart indenting. In Beta 2, block indenting is implemented for SQL Server queries but smart indenting is not.
In the Tools/All Languages/General dialog, notice the options for Statement Completion. These options affect IntelliSense behavior. Here also is where you can specify word wrap options and permit single-click URL navigation within SQL Queries.
Note IntelliSense is not active for SQL Server queries in SQL Server 2005 Beta 2. It is active, however, for the XML and MDX editors. (You can further configure XML editing in the Text Editor/XML node of the Options dialog tree.)
The Customize option from the Tools menu in Management Studio invokes the Customize dialog, which allows you to customize your toolbar appearance. For example, if you would like to have a toolbar button that will toggle the results pane, in the Commands tab of the Customize window, select the Window category, and scroll down to the Show Results Pane command. Select it, and drag it to the location on the desired toolbar.
Note There is no mechanism in Beta 2 Management Studio to customize keyboard shortcuts. Instead, you can choose between the Standard Management Studio keyboard shortcut scheme, and a SQL Server 2000 shortcut scheme.
You can set certain options for the current query window by invoking the Query Options dialog, either from the Query menu, using the CTRL+SHIFT+O keyboard shortcut, or clicking the Query Options button on the Query toolbar. The Query Options dialog lets you set options for both the execution of queries and the display of results, but on a query window basis. The options available are a subset of the options in the Tools/Options dialog: the Execution options are identical to those found in the Tools/Options/Query Execution/SQL Server, and the Results options are the same as those found in the Query Results/SQL Server dialog. The difference is that when you set options in the Query Options dialog, you are only affecting the current query window.
SQL Server 2005 Beta 2 Management Studio offers you some compelling enhancements for developing Transact-SQL queries. In particular, you no longer need to edit while connected to a physical SQL Server, and you can connect, disconnect, or change connections all within the same editing session. Perhaps the most significant enhancement is the ability to organize your Transact-SQL script files into solutions and projects, and integrate those solutions with source code control systems. Management Studio is similar enough to Query Analyzer that you will find it easy to get started with, while it also offers you significant new assistance in editing Transact-SQL code.
About the author
Ron Talmage is a founder and principal mentor with Solid Quality Learning, and lives in Seattle. He is a SQL Server MVP, current president of the Pacific Northwest SQL Server Users Group (pnwsql.org), and a contributing editor for PASS InfoLink. He writes for SQL Server Professional, SQL Server Magazine, and CoDe Magazine. His email address is Ron@SolidQualityLearning.com.