Query Editor in SQL Server Management Studio

Editing and submitting queries to the database engine is probably the most common operation you will perform when working with SQL Server. This functionality is provided by Query Editor, part of SQL Server Management Studio. Thus, Query Editor is the graphical tool of choice when working with SQL Server. As soon as you are connected to the server, you can invoke Query Editor by clicking on the New Query button on the standard SQL Server Management Studio toolbar. Alternatively, you can first select the type of query you wish to author from the File menu, as shown in Figure 5-1, and you will be prompted with the Connection Parameters dialog.

As you can see, Query Editor supports authoring and executing multiple query types, including Analysis Services and SQL Server Mobile. Analysis Services querying capabilities are covered in great depth in Chapter 14, "Inside Analysis Services Data Mining Tools."

Functionally, a SQL Server Mobile query is a subset of a Database Engine query and differs only in the connection type. Upon connection you need to either provide a valid database connection file or create a new one. Because query syntax is also just a subset of true SQL, the query carries the extension sqlce instead of sql.

Let's concentrate our efforts on Query Editor support for SQL Server Database Engine. After you type a query, you typically want to execute it against the database engine right away to see the results. You can do it directly from the standard tool bar, using the Execute button.

By default, results appear in the bottom pane in grid format. You can switch the view to display textual output directly. This option comes in handy if you want to cut and paste text.

If you want to change the font used by Query Editor, you can easily change it by following the Tools > Options menu items. Keep in mind that new settings will be global and not for a particular instance of your authoring session, so after you are finished, you need to go back and restore the defaults by clicking the Use Defaults button, as shown in Figure 5-2.

Figure 5-2. Query Editor: Changing fonts and colors.

[View full size image]

From the same Options dialog, you can also change many of the configuration options for Query Execution, as shown in Figure 5-3.

Figure 5-3. Query Editor for SQL Server Database Engine Advanced Execution options.

[View full size image]

When you execute a query that brings back a large result set, you may notice that the first rows start showing up immediately and the scroll bar continually reflects new rows constantly coming in. Moreover, even if the result set is huge, overall process memory of SQL Server Management Studio remains steadily low, and scrolling and viewing of the data is not affected. To understand how this is accomplished, let's look at the internal architecture of Query Editor.

Query Editor Architecture

The Query Editor internal architecture is shown in Figure 5-4. The user interface component responsible for displaying data and scrolling is called the Grid Control. It does not contain data; its only responsibility is coordinating what data is currently in a scroll view and which needs to be fetched for display.

Data fetched from the server is immediately placed into physical Data Storage, that is, a temporary file on the disk. These files get created for each outstanding query that has any results and get cleaned up after the query window is closed or a new execution is initiated. Data that needs to be read from the file is retrieved by a series of data readers called Storage View components. A Storage View component understands the internal format of the storage data file and can access the file in random fashion, de-serializing any data column object that is requested by the presentation layer. The Grid Storage component sits between the data layer and the user interface layer and provides "glue" that ties everything together. For example, the Grid Storage component produces application-specific formatting of the data for display. It's worth noting that when a user moves columns around the grid control or sorts data, the actual data on disk remains intact, and only the display changes. Grid Storage keeps track of those changes and translates physical data into data for display. If row sorting is requested, another component called Sort View gets involved. Sort View has its own Storage View component that references Data Storage data, performs all required sorting operations, and builds an index of the relative order of rows in storage required for display. The Grid Storage component constantly references Sort View to find out the absolute row number for the relative row requested for display, and subsequently makes the proper request to its own Storage View to fetch data.

XML Result Support

When query data contains data of the XML data type, Query Editor handles it in a special way. It provides a link inside the grid for each value of such data type, as shown in Figure 5-5.

By clicking on the link you can bring up the XML editor with column data conveniently loaded.