- Whats New in SSMS
- The Integrated Environment
- Administration Tools
- Development Tools
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Development Tools
SSMS delivers an equally impressive number of enhancements for database developers. These new tools are based on tools such as Query Analyzer that were available in prior versions of SQL Server. They deliver the same functional value available in prior releases and offer enhancements that address some of the shortcomings.
The Query Editor
The Query Editor sits at the top of the list for new development tools in SSMS. The Query Editor, as its name indicates, is the editing tool for writing queries in SSMS. It contains much of the functionality that was contained in SQL Server 2000’s Query Analyzer. The ability to write Transact-SQL (T-SQL) queries, execute them, return results, generate execution plans, and many of the other features you may be familiar with in Query Analyzer are also available with the Query Editor.
One main difference with the Query Editor is that is has been integrated into the SSMS environment. In SQL Server 2000, the Query Analyzer was a separate application with its own independent interface. In SQL Server 2005, SSMS houses the query-editing capabilities along with all the administrative capabilities.
Clicking the New Query button, opening a file, and selecting the Script to File option from a list of database objects in the Object Explorer are just a few of the ways to launch the Query Editor. Figure 3.12 shows the Query Editor window with a sample SELECT statement from the AdventureWorks database. The figure shows the Query Editor window displayed on the right side of the screen and the Object Explorer on the left side.
)
Figure 3.12 The Query Editor window in SSMS.
The basic editing environment within the Query Editor is similar to Query Analyzer. The top portion of the Query Editor window contains the query. The bottom portion contains the results of an executed query. The results can be displayed as text, displayed in a grid format, or output as XML. However, in the Query Editor, windows are by default managed differently than with Query Analyzer. Multiple Query Editor windows are displayed in a tabbed format; in comparison, Query Analyzer displayed a separate window for each query.
Query Editor Types
The Query Editor in SQL Server 2005 enables you to develop different types of queries. You are no longer limited to database queries based on SQL. You can use the Query Editor to develop all types of SQL Server Scripts, including those for SQL Server Analysis Services (SSAS) and SQL Server Mobile Edition. The SSAS queries come in three different flavors: multidimensional expressions (MDX), data mining expressions (DMX), and XML for analysis (XMLA). Only one selection exists for creating SQL Server Mobile Edition scripts.
You see these new query options when you create a new query. When you select New from the SSMS menu, you can choose what type of query to create. You use the Database Engine Query choice to create a T-SQL query against the database engine. The other new query options correspond to SSAS and SQL Server Mobile Edition. The toolbar on SSMS also has icons that correspond to each type of query that can be created.
Each query type has a code pane that works much the same way across all the different types of queries. The code pane, which is the topmost window, color-codes the syntax that is entered, and it has sophisticated search capabilities and other advanced editing features that make it easy to use. The features that are new to SQL Server 2005 and apply to all the editor types include line numbering, bookmarks, hyperlinks in the comments, and a color-coded indicator that is shown in front of each line that has changed since the script was opened.
Other code pane features are available only for certain types of queries. IntelliSense, which automatically completes syntax and arguments, is available for all queries except database engine queries. Squiggles, which are wavy lines that appear below a word in the editor to indicate possible syntax errors, are available with MDX, DMX, and XML queries. The MDX, DMX, and XML editors also offer code outlining, which enables you to expand and collapse code segments to make it easier to review code.
Disconnected Editing
New to SQL Server 2005 is the ability to use the code editor without a database connection. When creating a new query, you can choose to connect to a database or select Cancel to leave the code pane disconnected. To connect to the database at a later time, you can right-click in the code pane window and select the Connect option. You can also disconnect the Query Editor at any time or choose the Change Connection option to disconnect and connect to another database all at once.
Along with disconnected editing are some changes to the Windows behavior that are worth noting. The biggest changes relate to the behavior of query windows that are currently open at the time that a file is opened for editing. With SQL Server 2000 Query Analyzer, the currently selected window would be populated with the contents of the file that you were opening. Prior to this replacement, a prompt would be displayed that asked whether you wanted to save your results. If the query window was empty, the contents would be replaced without the prompt for saving.
With SQL Server 2005, a new query window is opened every time a new file is opened. The new window approach is faster but can lead to many more open windows in the document window. You need to be careful about the number of windows/connections you have open. Also, you need to be aware that the tabbed display shows only a limited number of windows. Additional connections can exist even if their tabs are not in the active portion of the document window.
Editing SQLCMD Scripts in SSMS
SQLCMD is a command-line utility that is new to SQL Server 2005. You can use it for ad hoc interactive execution of T-SQL statements and scripts. It is basically a replacement for the ISQL and OSQL commands that were used in prior versions of SQL Server. (OSQL still works with SQL Server 2005, but ISQL has been discontinued.)
What’s new to SSMS is the ability to write, edit, and execute SQLCMD scripts within the Query Editor environment. The Query Editor in SSMS treats SQLCMD scripts in much the same way as other scripts. The script is color-coded and can be parsed or executed. This is possible only if you place the Query Editor in SQLCMD mode, which you do by selecting Query, SQLCMD Mode or selecting the SQLCMD mode icon from the SSMS toolbar.
Figure 3.13 shows a sample SQLCMD script in SSMS that can be used to back up a database. This example illustrates the power and diversity of a SQLCMD script that utilizes both T-SQL and SQLCMD statements. It uses environment variables that are set within the script. The script variables DBNAME and BACKUPPATH are defined at the top of the script with the SETVAR command. The BACKUP statement at the bottom of the script references these variables, using the convention $(variablename), which substitutes the value in the command.
)
Figure 3.13 Editing a SQLCMD script in SSMS.
SQLCMD scripts that are edited in SSMS can also be executed within SSMS. The results are displayed in the results window of the Query Editor window, just like any other script. After you test a script, you can execute it by using the SQLCMD command-line utility. The SQLCMD command-line utility is a very powerful tool that can help automate script execution. For more information on using SQLCMD in SSMS, refer to the Books Online topic “Editing SQLCMD Scripts with Query Editor.” The SQLCMD command-line utility is discussed in more detail in Chapter 4, “SQL Server Command-Line Utilities.”
Regular Expressions and Wildcards in SSMS
SSMS has a robust search facility that includes the use of regular expressions. Regular expressions provide a flexible notation for finding and replacing text, based on patterns within the text. Regular expressions are found in other programming languages and applications, including the Microsoft .NET Framework. The regular expressions in SSMS work in much the same way as these other languages, but there are some differences in the notation.
The option to use regular expressions is available whenever you are doing a find or replace within an SSMS script. You can use the find and replace option in the code pane or the results window. You can use the Find and Replace option from the Edit menu or use press either the Ctrl+F or Ctrl+H shortcut keys to launch the Find and Replace dialog box. Figure 3.14 shows an example of the Find and Replace dialog that utilizes a regular expression. This example is searching for the text Customer, preceded by the @ character and not followed by the Id characters. This kind of search could be useful for searching a large stored procedure where you want to find the customer references but don’t want to see the variables that contain customer in the first part of the variable name.
)
Figure 3.14 A find and replace with regular expressions.
You use regular expressions only when the Use check box in the Find and Replace dialog is selected. When this option is selected, you can choose either Regular Expressions or Wildcards. Wildcard searches work much the same way in SSMS as they do in file searches. For example, if you wanted to find any references to the word zip, you could enter *zip* in the Find What text box. The wildcard options are limited but very effective for simple searches.
Regular expressions have a much more extensive number of available search options. When you choose the option to use regular expressions, the arrow button is enabled to the right of the text box where you enter your search text. If you click this button, you are given an abbreviated list of regular expression characters that you can use in your searches. A brief description of what each character represents in the search is listed next to the character. For a complete list of characters, you can choose the Complete Character List option at the bottom of the list. This option brings you to the Books Online topic “How to: Search with Regular Expressions,” which gives a comprehensive review of all the characters.
Enhanced Performance Output
The Query Editor in SSMS has an extended set of options available for capturing and distributing performance-related data. It contains many of the familiar performance features that you may have grown accustomed to in SQL Server 2000 Query Analyzer—plus more.
Changes in the collection of performance data include a new Execution Plan tab that is displayed in the results window, along with the Results and Messages tab. The Execution Plan tab can be populated with two different types of plans: estimated plans and actual plans. The actual execution plan is a new display for SQL Server 2005; it shows the plan that was used in generating the actual query results. The actual plan is generated along with the results when the Include Actual Execution Plan option is selected. This option can be selected from the SSMS toolbar or from the Query menu. Figure 3.15 shows an example of an actual execution plan generated for a query against the AdventureWorks database. It uses the familiar treelike structure that was also present in SQL Server 2000, but the display has been enhanced for SQL Server 2005. The ToolTips that are displayed when you mouse over a node in the execution plan include additional information; you can see that information in a more static form in the Properties window if you right-click the node and select Properties. The icons in the graphical plan have changed, and the display is generally easier to read in SQL Server 2005.
)
Figure 3.15 Displaying an actual execution plan in Query Editor.
Query plans generated in the Query Editor are easier to distribute in SQL Server 2005. You have several options for capturing query plan output so that you can save it or send it to someone else for analysis. If you right-click an empty section of the Execution Plan window, you can select the Save Execution Plan As option, which allows you to save the execution plan to a file. By default, the file has the extension .sqlplan. This file can be opened using SSMS on another machine to display the graphical output.
The query plan can also be output in XML format and distributed in this form. You make this happen by using the SET SHOWPLAN_XML ON option. This option generates the estimated execution plan in a well-defined XML document. The best way to do this is to turn off the display of the actual execution plan and execute the SET SHOWPLAN_XML ON statement in the code pane window. Next, you set the Query Editor to return results in grid format and then execute the statements for which you want to generate a query plan. If you double-click the grid results, they are displayed in the SSMS XML editor. You can also save the results to a file. If you save the file with the .sqlplan extension, the file will display the graphical plan when opened in SSMS.
Using the Query Designer in the Query Editor
A graphical query design tool is now accessible from the Query Editor window where you write your queries. This is a great option that was missing in prior versions of SQL Server. With SQL Server 2000, you could access a graphical query designer by opening a table in Enterprise Manager and selecting Query, but this option was disconnected from the Query Analyzer environment, where the queries were authored.
With SQL Server 2005, you can right-click in the Query Editor window and choose Design Query in Editor. A dialog box appears, allowing you to add tables to the graphical query designer surface. The tables that are selected are shown in a window that allows you to select the columns you want to retrieve. Columns that are selected appear in a SELECT statement that is displayed at the bottom of the Query Designer window. Figure 3.16 shows an example of the Query Designer window that contains two tables from the AdventureWorks database. The two tables selected in this figure are related, as indicated by the line between them.
)
Figure 3.16 Designing queries in the Query Editor.
The T-SQL statements are generated automatically as you select various options on the Query Designer screen. If you select Sort Type, an ORDER BY clause is added. If you choose an alias for a column, it is reflected in the T-SQL. If tables are related, the appropriate joins are generated.
When you click OK on the Query Designer window, the related T-SQL is automatically placed in the Query Editor window. You can edit the T-SQL as needed or use it as is. You can imagine the time savings you can achieve by using this tool.
Managing Projects in SSMS
Project management capabilities like those available in Visual Studio are now available in SSMS. Queries, connections, and other files that are related can be grouped into projects. A project or set of projects is further organized or grouped as a solution. This type of organization is the same as in the Visual Studio environment.
Projects and solutions are maintained and displayed with the Solution Explorer. The Solution Explorer contains a tree-like structure that organizes the projects and files in the solution. It is a component window within SSMS that you launch by selecting View, Solution Explorer. Figure 3.17 shows an example of the Solution Explorer. The solution in this example is named EmployeeUpgrade, and it contains two projects, named Phase1 and Phase2. Each project contains a set of connections, a set of T-SQL scripts, and a set of miscellaneous files.
)
Figure 3.17 Solutions and projects listed in the Solution Explorer.
The first thing to do when using the project management capabilities in SSMS is to add a project. To do this, you select File, New, and when the New dialog appears, you select project to add a new project. It is a bit odd, but you must create the project before you can create the solution. When adding the new project, you are given a choice of the type of project, and you must select either SQL Server Scripts, Analysis Services Scripts, or SQL Mobile Scripts. Each one of these project types is geared toward the respective SQL Server technology.
After the project is added, you can add the related connections and files. To add a new connection, you simply right-click the Connections node. The Connections entries allow you to store SQL Server connection information that relates to the project you are working on. For example, you could have a connection to your test environment and another connection to the production environment that relates to the project. When a connection is included in the project, you can double-click it, and a new query window for that connection is established.
SQL script files are added to a project in a similar fashion to connections: You right-click the Queries node and select the New Query option. A new Query Editor window appears, allowing you to enter the T-SQL commands. Any T-SQL script is viable for this category, including those that relate to database objects such as stored procedures, triggers, and tables.
You can also add existing files to a project. To do this, you right-click the project node, select Add, and then select Existing Item. The file types listed in the drop-down at the bottom of the Add Existing Item dialog include SQL Server files (*.sql), SQL deadlock files (*.xdl), XML files (*.xml), and execution plan files (*.sqlplan). SQL Server files are added, by default, to the Queries node. All the other file types are added to the Miscellaneous node. The connection entries are not stored in a separate file but are contained in the project file itself.
Integrating SSMS with Source Control
SSMS has the capability to integrate database project files into a source control solution. Source control provides a means for protecting and managing files. Source control applications typically contain features that allow you to track changes to files, control and track who uses the files, and provide a means for tagging the files with a version stamp so that the files can be retrieved at a later time, by version.
SSMS can integrate with a number of different source control applications. Visual SourceSafe is Microsoft’s basic source control solution, but other source control applications can be used instead. The source control client application must be installed on the machine on which SSMS is running. When this is complete, you can set the source control application that SSMS will use within SSMS. To do this, you select Tools, Options and navigate to the Source Control node. The available source control clients are listed in the Current Source Control Plug-in drop-down.
The link between SSMS and the source control application is the database solution. After a solution has been created, it can be added to source control. To add a solution to a source control application, you open the Solution Explorer and right-click the solution or any of the projects in the solution. You then see the Add Solution to Source Control option. You must then log in to the source control application and select a source control project to add the solution to.
When the solution has been added to a source control application, all the related projects and project files are added as well. The projects and files that are in the source control application have additional options available in the Solution Explorer. Figure 3.18 shows a sample solution that has been added to a source control application. A subset of the source control options that are available when right-clicking project files are shown in this figure as well.
)
Figure 3.18 Source control options in the Solution Explorer.
The options that relate to source control are listed toward the bottom of the options list. The options that are available depend on the status of the selected file. For example, if a file has been checked out, additional options are displayed that relate to checking the file back in. The following are some of the common source control options:
-
Check Out for Edit—This option allows you get a copy of the file from the source control application so that you can modify the file. When you check out the file, the source control provider can keep track of the user who has checked out the file, and it can also prevent other users from checking the file out.
-
Check In—This option copies the locally modified file into the source control solution. The file must first be checked out for editing before you can use the Check In option. A new version for the file is established, and any prior versions of the file are retained as well.
-
Get Latest Version—This option gets a read-only copy of the latest version of the project file from the source control application. The file is not checked out with this option.
-
Compare—This option enables you to compare version of source control files. The default comparison that is shown is between the file in the source control application and the local file on your machine.
-
Get—This option is similar to the Get Latest Version option, but it retrieves a read-only copy of the file. With this option, a dialog box appears, allowing you to select the file(s) that you want to retrieve.
-
View History—This option lists all versions of the files that have been checked into the source control application. The History dialog box has many options that you can use with the different versions of the file. You can view differences between versions of the files, view the contents of a specific version, generate reports, or get an older version of the file.
-
Undo Checkout—This option changes the checkout status in the source control application and releases the file to other source control users. Any changes that were made to the local copy of the file are not added to the source control version.
Other source control options are available via the Source Control menu in SSMS. You select an item in the Solution Explorer and then select File, Source Control. You can use this menu to check the status of a file by using the SourceSafe Properties option, set source control properties, launch the source control application, and perform other source control operations.
Using SSMS Templates
Templates provide a framework for the creation of database objects in SSMS. They are essentially boilerplate files that help generate scripts for common database objects. They can speed up the development of these scripts and help enforce consistency in the generation of the underlying database objects.
SQL Server 2005 has expanded the features available for generating templates. One substantial change is the addition of the Template Explorer. The Template Explorer is a component window that is available in SSMS and replaces the Template tab that was available in the SQL Server 2000 Query Analyzer. Figure 3.19 shows the Template Explorer and the available SQL Server template folders. Separate templates also exist for Analysis Services and SQL Server Mobile Edition. You can view them by selecting the related icon at the top of the Template Explorer.
)
Figure 3.19 The SSMS Template Explorer.
You access the available templates by expanding the template folder in the Template Explorer tree. For example, if you expand the Index folder, you see six different types of index templates. If you double-click one of the templates, a new Query Editor window appears, populated with the template script. Figure 3.20 shows the template script that is displayed when you open the Create Index Basic template.
)
Figure 3.20 The template script for creating a basic index.
The template script contains template parameters that have the following format within the script:
<parameter_name, data_type, value>.
You can manually replace these parameters in the script, or you can use the Specify Values for Template Parameters option from the Query menu to globally replace the parameters in the script with the desired values. Selecting Query, Specify Values for Template Parameters launches the Specify Values for Template Parameters dialog box, which enables you to enter the parameter values (see Figure 3.21).
)
Figure 3.21 The Specify Values for Template Parameters dialog box.
After you have entered the parameter values and clicked OK, the values are reflected in the script. For example, the values shown in Figure 3.21 for the basic index template result in the following script:
-- =============================================
-- Create index basic template
-- =============================================
USE AdventureWorks
GO
CREATE INDEX NC_Address_Person
ON Person.Address
(
PostalCode
)
GO
You also have the option of creating your own custom templates. These templates can contain parameters just like those that are available with the default templates. You can also create your own template folder that will be displayed in the Template Explorer tree. To create a new template folder, you right-click the SQL Server Templates node in the Template Explorer tree and select New, Folder. A new folder appears in the tree, and you can specify a new folder name. Figure 3.22 shows the Template Explorer with a set of custom templates found under the _mytemplates folder. The code pane in this figure shows the contents of a new custom template named sys.objectSelectWithParameters. This custom template contains two parameter declarations: object_type and modify_date. When you select the Specify Values for Template Parameters options for this custom template, you have the opportunity to change the values, just as you can with the default templates.
)
Figure 3.22 A custom template example.