Manage Learn to apply best practices and optimize your operations.

Authoring Activities Using SQL Server Management Studio

SQL Server Management Studio gives you an authoring environment for all types of queries. Read more in this excerpt from SQL Server 2005: A Beginner's Guide.

Microsoft SQL Server 2005: The Complete Reference The following was excerpted from Chapter 2, 'SQL Server 2005 Management Studio,' of the book SQL Server 2005: A Beginner's Guide written by Dusan Petkovic and published by McGraw-Hill Osborne Media. Click here for the book excerpt series.

Bonus: TechTarget members take 45% off the price of this book at McGraw-Hill's online bookstore. Just enter promotion code AETT511 on the homepage.

Authoring Activities Using SQL Server Management Studio

In the previous section we described the capabilities of SQL Server Management Studio, which concern management tasks. Beside these, SQL Server Management Studio gives you a complete authoring environment for all types of queries in SQL Server. You can create, save, load, and edit queries that execute SQL Server and other queries.

Figure 2-10: The final diagram of the sample database

SQL Server Management Studio allows you to work on queries without being connected to a particular SQL Server instance. This tool also gives you the option of developing your queries in a particular environment, where you can associate your queries with projects.

The authoring capability in SQL Server is associated with the Query Editor window of SQL Server Management Studio.

Query Editor

If you want to launch the Query Editor window, click the New Query button in the toolbar of SQL Server Management Studio. The Query Editor window appears.

Once you open the Query Editor window, the status bar at the bottom of the window will tell you whether your query is in a connected or disconnected state. If you are not connected automatically to the server, the Connect to SQL Server dialog box appears, where you can type the name of the database server to which you want to connect and select the authentication mode.

NOTE: Disconnected editing has more flexibility than connected editing. You can edit queries without having to choose a server, and you can disconnect a given Query Editor window from one server and connect it to another, without having to open another window. (You can use the disconnected editing if you click the Cancel button in the Connect to SQL Server dialog box.)

Query Editor can be used by end users for the following tasks:

  • Generating and executing Transact-SQL statements
  • Storing the generated Transact-SQL statements in a file
  • Analyzing execution plans for generated queries
  • Graphically illustrating the execution plan for a selected query
  • NOTE: In this chapter we will discuss the first two topics, while the other two will be explained in detail in Chapters 9 and 22.

    Query Editor contains an internal text editor and a selection of buttons in its toolbar. The main window is divided into a query pane (upper) and a results pane (lower). (See Figure 2-11.) Users enter the Transact-SQL statements (queries) that they want to execute into the query pane, and after SQL Server has processed the queries, the output is displayed in the results pane.

    The following example will show you how queries are entered and processed by SQL Server. If you type the two Transact-SQL statements shown in Figure 2-11, click the Query button in the Query Editor's toolbar and select Execute or click F5. The results of these statements will be shown in the results pane of Query Editor.

    NOTE: You can open several different windows—that is, several different connections to one or more SQL Server instances. You create new connections by clicking the New Query button in the toolbar.

    Figure 2-11: Query Editor with a query and its results

    NOTE: The first statement in Figure 2-11, USE, specifies the AdventureWorks database as the current database. The second statement, SELECT, displays all the rows of the Customer table, which belongs to this database.

    SQL Server contains the additional information that is displayed in the status bar of the Query Editor window. The following information is displayed:

  • The status of the current operation (in the left corner of the window)
  • Database server name
  • Current user name and server process ID
  • Current database name
  • Elapsed time for the execution of the last query
  • The number of retrieved rows
  • Query Pane

    One of the main properties of SQL Server Management Studio is ease of use. This general idea is also applied to the Query Editor component. Query Editor supports a lot of features that make coding of Transact-SQL statements easier. First, Query Editor uses syntax highlighting to improve the readability of Transact-SQL statements. It displays all reserved words in blue, all variables in black, strings in red, and comments in green. (For a discussion of reserved words, see the next chapter.)

    There is also the context-sensitive help function called Dynamic Help that allows you to get help on a particular statement. If you do not know the syntax of a statement, just highlight that statement in the editor and select the Dynamic Help function on the Help menu. You can also highlight options of different SQL statements to get the corresponding text from Books Online.

    Object Explorer can also help you edit queries. For instance, if you want to see the corresponding CREATE TABLE statement for the employee table, drill down to this database object, right-click the table name, select Script Table as, and choose CREATE to New Query Editor Window. Figure 2-12 shows the Query Editor window with the CREATE TABLE statement. (This capability extends also to other objects, such as stored procedures and functions.)

    Solution-Based Query Editing

    Query editing in SQL Server Management Studio is solution-based. If you start a blank query using the New Query button, it will still be based on a blank solution. You can see this by choosing Solution Explorer from the View menu, right after opening your blank query.

    Figure 2-12: The Query Editor windows with the CREATE TABLE statementults

    A solution can have zero, one, or more projects associated with it. If you want to associate a project with the solution, close your blank solution, Solution Explorer, and the Query Editor window, and start a new project by clicking on the File Menu and selecting New and then Project. The project is a method of organizing files in a selected location. You can choose a name for the project and select its location on disk. When you create a new project, by default you will start a new solution. You can add a project to an existing solution using the Solution Explorer component.

    Once the new project and solution are created, Solution Explorer will show nodes in each project for Connections, Queries, and Miscellaneous files.

    SQL Server Management Studio supports version control using the integration with MS Visual Source Safe. As we already stated, Solution Explorer allows you to organize and store related scripts as parts of a project. These script files can be checked in and out of the version control system directly from Solution Explorer.

    Click for the complete book excerpt series.

    Dig Deeper on SQL Server Database Modeling and Design