- Using SQL Server Management Studio
- Using the Query Analyzer
- Using Visual Studio
- Summary
This chapter is from the book
This chapter is from the book
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 Start Query Analyzer
Figure 3.13 shows the layout of Query Analyzer. It has four tabs—Objects, SQL, Grid, and Notes.
)
Figure 3.13 Connecting the Database
- 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.
- Specify the path of the database and click Connect as shown in 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 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.
- Click on the Tools | Logging option as shown in Figure 3.16.
Figure 3.16 Logging Option
- Select a table. Click on the Execute Query button.
- Clicking this button while a table is selected shows the content of the table as shown in Figure 3.17.
Figure 3.17 Table Content
- You can check the statement that Query Analyzer has executed. Click on the SQL tab.
- 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 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 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 Database Objects
Clicking on the Tables node displays a set of icons as shown in 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 Hide System Table
Figure 3.23 shows a Show System Table. Clicking on the Show System Table button shows system tables.
)
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 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 Icons with Table
Clicking on the button shown in Figure 3.26 displays the content of the table you have selected.
)
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 New Column
Clicking on the button shown in Figure 3.28 opens a dialog box for adding a new index object.
)
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 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 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 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 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 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 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 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 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 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.