Home > Articles

  • Print
  • + Share This
This chapter is from the book

Using the Query Analyzer

You have probably already used the SQL Query Analyzer tool to execute a query against an SQL Server 2000 database. Microsoft developed a device version of Query Analyzer, similar to SQL Server Query Analyzer, to manage the SQL CE 2.0 database on a device and SQL Server Query Analyzer functionality is extended to devices too. With SQL Server Compact Edition 3.0 you can manage the database using SQL Server Management Studio on a desktop. With SQL Server Compact Edition 3.0 you also get the Query Analyzer tool to manage the database on a device and on an emulator.

Query Analyzer allows you to not only execute queries but it also allows you to view database object information and manipulate database objects.

Installing the Query Analyzer

On a desktop, you have SQL Server Management Studio tool to manage an SQL Server Compact Edition database. On a device, you will use the SQL Server Compact Edition Query Analyzer tool. The Query Analyzer gets installed when you install SQL Server Compact Edition on a device or on an emulator. If you are a developer and deploying an application using Visual Studio in debug mode, both SQL Server Compact Edition and Query Analyzer are installed.

You can also manually install SQL Server Compact Edition and Query Analyzer onto a device. To install the Query Analyzer on a device you should install the .NET Compact framework and the SQL Server Compact Edition database. The procedures for deploying .NET Compact Framework and SQL Server Compact Edition database is explained in Chapter 2, Platform Support and Installation.

Getting Connected to the SQL Server Compact Edition Database

Query Analyzer is a graphical tool that manages an SQL Server Compact Edition database on a device and on an emulator. With SQL Server Compact Edition 3.x you can use Query Analyzer version 3.x. As discussed earlier, SQL CE 2.0 also provides support for Query Analyzer. Query Analyzer for SQL CE 2.0 and SQL Server Compact Edition 3.x are different executables. Both versions can exist on the same device. Query Analyzer for SQL CE 2.0 will not connect to an SQL Server Compact Edition 3.x database.

Start the Query Analyzer on a device or emulator by clicking on Start | Query Analyzer as shown in Figure 3.12.

Figure 3.12

Figure 3.12 Start Query Analyzer

Figure 3.13 shows the layout of Query Analyzer. It has four tabs—Objects, SQL, Grid, and Notes.

Figure 3.13

Figure 3.13 Connecting the Database

  1. To connect to the SQL Server Compact Edition database using Query Analyzer, click on the Connect button inside the rectangle as shown in Figure 3.13.
  2. Specify the path of the database and click Connect as shown in Figure 3.14.
    Figure 3.14

    Figure 3.14 Specify the SQL Server CE Database File

The database is open. It has Objects, SQL, Grid, and Notes tabs as shown in Figure 3.15. Under the Objects tab you can browse the database objects. Under the SQL tab you can write SQL queries. You can view the results of SQL queries under the Grid tab. The Notes tab shows remarks such as errors in executing query, time taken to execute queries, and rows impacted.

Figure 3.15

Figure 3.15 Tools Menu

Using the Tools Menu

At the bottom of the Query Analyzer dialog box there is a Tools option. The Tools option is available under all four tabs. As shown in Figure 3.15, the Tools menu has subitems—Refresh, Logging, Fonts, About, and Exit.

Refresh

The Objects tab displays the tree view of the SQL Server Compact Edition database object. You can click on the Refresh menu to refresh the tree view.

Logging

Logging is an interesting option. You can click on Logging to enable and disable this feature. After enabling logging, any DDL or DML operation that you do using the Object Explorer Graphical menu option is logged in the SQL tab. The SQL statement equivalent to your action is logged in the SQL tab. For example, if you use the New Table menu option, the Create Table command logs into the SQL Tab.

The statements logged or written in SQL Tab are not saved automatically. Use the Save menu option if you need to save the statements for future usage.

  1. Click on the Tools | Logging option as shown in Figure 3.16.
    Figure 3.16

    Figure 3.16 Logging Option

  2. Select a table. Click on the Execute Query button.
  3. Clicking this button while a table is selected shows the content of the table as shown in Figure 3.17.
    Figure 3.17

    Figure 3.17 Table Content

  4. You can check the statement that Query Analyzer has executed. Click on the SQL tab.
  5. The SQL tab shows the "SELECT" statement as shown in Figure 3.18. If you drop a table, column, or index, you will see an equivalent statement in the SQL tab.
    Figure 3.18

    Figure 3.18 SQL Statement

Using the Logging option, you can record the SQL statements corresponding to actions that you do using the graphical tool. If you need to insert one hundred rows into a database and you are not keen on writing SQL Statements, you can generate the first few SQL statements by using the Logging option and then copy and paste to convert them into a hundred rows.

Fonts

You use the Font option to change the setting in Query Analyzer. Click on the Font menu option to change the font in the Font dialog box as shown in Figure 3.19.

Figure 3.19

Figure 3.19 Font Dialog Box

About

The About menu option displays the current versions of Query Analyzer and the SQL Server Compact Edition database.

Exit

The purpose of the Exit menu is to close the Query Analyzer. Clicking on X in the right-hand corner only minimizes the application and leaves it running in the background. In the Visual Studio Tools section you will learn to use the MinimizeBox property to change the default behavior.

Using Tabs

The tabs on the Query Analyzer allow you to access and manipulate database definition and data contents. You can use the Object tab for a database object and the SQL tab for writing SQL queries. The Grid tab shows the results of queries. The Notes tab contains information regarding query execution time and number of rows impacted.

Objects

The Object tab shows the tree view of database objects such as the one shown in Figure 3.20. This relates to Object Explorer in SQL Server Management Studio. At the top of the tree is the Database node. The Database node shows the available connection. The open database connection will have a green icon and a closed database will have a red icon. Under each database there are tables, columns, and indexes.

Figure 3.20

Figure 3.20 Database Objects

Clicking on the Tables node displays a set of icons as shown in Figure 3.21.

Figure 3.21

Figure 3.21 Icons with Tables Object

The second icon is for displaying and hiding system tables. You can toggle the option by clicking on it. Figure 3.22 shows a Hide System Table. Clicking on the Hide System Table icon hides the table.

Figure 3.22

Figure 3.22 Hide System Table

Figure 3.23 shows a Show System Table. Clicking on the Show System Table button shows system tables.

Figure 3.23

Figure 3.23 Show System Table

The icon with a plus sign shown in Figure 3.24 is used to create a new table. The icon is shown when you select Tables node.

Figure 3.24

Figure 3.24 Create New Table

You can click on a specific table in Object tree. When you select a table in Object tree, icons appear at the bottom as shown in Figure 3.25.

Figure 3.25

Figure 3.25 Icons with Table

Clicking on the button shown in Figure 3.26 displays the content of the table you have selected.

Figure 3.26

Figure 3.26 Execute Query

Clicking on the button shown in Figure 3.27 opens a dialog box for adding a new column object.

Figure 3.27

Figure 3.27 New Column

Clicking on the button shown in Figure 3.28 opens a dialog box for adding a new index object.

Figure 3.28

Figure 3.28 New Index

SQL

The SQL tab shown in Figure 3.29 is an editor to write and execute SQL queries. This relates to Query Editor in SQL Server Management Studio.

Figure 3.29

Figure 3.29 SQL Tab and Menu Options

When you move to the SQL Tab, the Query Analyzer shows the SQL menu option on the lower left-hand side. The SQL option has suboptions to open a new query, open an existing query, save a query, and to show the execution plans. You will be using these options in upcoming chapters.

The Word Wrap option is specific to Query Analyzer. The Word Wrap option is useful for the small screen and wraps an SQL statement to the visible part. A similar option is not available in SQL Server Management Studio.

Next to the SQL menu option there is the Execute icon. (This icon is shown in Figure 3.27.) Use this function to execute queries.

On the right side of the Execute icon you have standard Cut, Copy, and Paste icons as shown in Figure 3.30. These are standard features and you can utilize them while writing queries.

Figure 3.30

Figure 3.30 Cut, Copy, and Paste Buttons

The icon on the far right as shown in Figure 3.31 is the Preset button. This saves a frequently used SQL statement.

Figure 3.31

Figure 3.31 Preset Button

You will be using the Preset button in conjunction with ten SQL statement buttons. These ten buttons are shown in Figure 3.32.

Figure 3.32

Figure 3.32 Ten SQL Statement Buttons

You may have noticed these ten buttons at the top of SQL Query Editor.

The Preset button allows you to save up to ten frequently used SQL statements. By clicking on one of these buttons you can invoke an SQL statement.

Saving SQL Statements

To save SQL statements, click on the PreSet button. It will start the Button Presets dialog box. Type the SQL statements and click OK. Figure 3.33 demonstrates a scenario in which two SQL statements are written.

Figure 3.33

Figure 3.33 Button Preset Dialog Box

Type an SQL statement. Highlight the statement as shown in Figure 3.34 and then click on the Preset button.

Figure 3.34

Figure 3.34 Highlighted SQL Statement

The Preset dialog will open. Click on the button number where you want to store an SQL statement. The example shown in Figure 3.35 stores a statement at number three.

Figure 3.35

Figure 3.35 Adding SQL in Button Preset

Using a Saved SQL Statement

Once you have saved SQL statements, you can retrieve them by simply clicking on the button on top of SQL Query Editor. The SQL statement corresponding to the button number will be copied to SQL Query Editor.

Grid

The Grid node displays the results of query execution. When you double click on the Execute Query button, the Query Analyzer automatically activates the Grid tab and displays the results. If the query has an error, the Notes tab will activate. Figure 3.36 demonstrates the Grid tab.

Figure 3.36

Figure 3.36 Grid Tab

Notes

When you execute a query, the Notes section contains the amount of time it took to execute the query similar as shown in Figure 3.37. In the event the query has an error, the Notes tab will display the error message.

Figure 3.37

Figure 3.37 Notes Tab

Now you know how to utilize Query Analyzer functionality. In the next three chapters you will learn more about the SQL Server Compact Edition database and you will use Query Analyzer interface for other operations such as creating, compacting, and deleting the database.

  • + Share This
  • 🔖 Save To Your Account