When executing a query there are several options available to you.
The simplest method of execution is to execute the whole query against a single database. To do this just click the Execute toolbar button while no text is selected (you can also use the shortcuts F5 or Ctrl-E).
You can also execute part of a query by selecting just the text you want to execute and clicking the Execute toolbar button or other execution method.
Query results
When you execute a query, one or more results windows will appear against the query window. One window, the messages window, will detail when the query began, when it completed, and how long it took to execute.
This window also shows any output created during the execution such as PRINT statements or errors.
When a query returns some results, such as SELECT statements, the result of each query is returned to a new results window. The results windows are created in the same order as the statements execute, so if you execute 2 SELECT statements, the first statement will return results to window 1 and the second statement will return results to window 2.
If you execute the same query again then the results will be returned to the same results windows. N.B. Because the order of results is based in the order of execution a results window is not bound to an individual statement within the query, therefore the same statement may return results to a different results window if the statement is modified.
Additional features are available from the results window's context menu.
Auto-size columns
This allows you to automatically size each column in the results to match the content of that column. N.B. On large numbers of records this may take some time to complete.
Client-side sorting and grouping
By clicking on a column header you can sort the data in the results by that column. Clicking the column again toggles the sort direction from ascending to descending. To sort by more that one column use the Shift key when selecting additional columns. N.B. On large numbers of records sorting may take some time to complete. The sorting operation is carried out on the client, no SQL is re-executed.
By dragging and dropping a column header onto the 'Drag a column header here to group by that column' group box you can group the data in the results by that column. You can create nested grouping by dragging additional columns to the group box. Client-side grouping is particularly useful because it does not have to follow SQL GROUP BY clause restrictions, for example you can include columns not in a GROUP BY clause. This can help you determine if the results of a query are correct before applying the appropriate GROUP BY clause. Each group can be collapsed or expanded. To clear grouping drag a column away from the group box. N.B. On large numbers of records grouping may take several some time to complete. The group operation is carried out on the client, no SQL is re-executed.
Reset Client Sorting
This resets the client-side sorting so the data is presented in the original order.
Collapse All Groups
This collapses all groups allowing you to expand only the groups you want to look at.
Expand All Groups
This expands all groups allowing you to see all the available data.
Copy
This copies the content of the selected cell.
Copy Special - Column List
This copies the name of each column in the results to the clipboard. This can be used to quickly create a new SELECT statement.
Copy Special - Selected Column To SQL IN Clause
This copies the values within the selected column for each of the selected rows in the grid to an IN clause placing the results on the clipboard. This can be used to quickly create a new SELECT statement with an IN clause for filtering. By default the name of the column will also be included, e.g MyColumn IN (1, 2, 3). If you do not wish the column name to be included, e.g. IN (1, 2, 3), you can change this via the option dialog (see changing options and preferences for more details).
Export - Excel
This option allows you to save the selected results to an Excel compatible XML file. Enter a name to save the file under or use the default provided.
Row formatting is included, such as client-side grouping, however the underlying data is output so some values will differ from those displayed, e.g. dates and bit values. Only selected rows are output. To output all rows click the blank header cell at the top left of the results grid. N.B. Microsoft Excel 2002 or above must be installed on your local computer for this option to work.
Export - Open With Excel
This option saves the selected results to a file and then opens Excel to display the centent. The file is saved in an Excel compatible XML format to the Export directory under the application's installation directory. When saving the file from Excel you may wish to save it in a native Excel format rather than the original XML format.
Row formatting is included, such as client-side grouping, however the underlying data is output so some values will differ from those displayed, e.g. dates and bit values. Only selected rows are output. To output all rows click the blank header cell at the top left of the results grid. N.B. Microsoft Excel 2002 or above must be installed on your local computer for this option to work.
Visualize data
Same data returned by a query may not be entirely visible within a single line within the results grid, others such as dates may have addition details available. Visualizing this data gives you access to these additional details (see Visualizing data for more details).
Clear Results
The Clear Results command closes the message window and all results windows.
Parse Query
You can check the syntax of the query by using the Parse Query command. This will display any errors in the Messages window.
Execute and Rollback
A useful feature when executing queries is the Execute and Rollback command. This executes the query but then immediately rolls it back. This allows you to 'try' a query before running it for 'real'. The output to the Messages window is the same as if the query had actually run so you can check the number of records affected matches that expected number, and select data from temporary tables etc, to ensure the data will be modified as anticipated.
Executing against multiple databases
In addition to executing a query against a single database you can execute a query against multiple databases in the same operation.
By using the Execution Targets window you can select 1 or more databases to execute the query against and specify the transactional rule to apply.
The Execution Targets window has 2 modes; single database mode and multiple database mode. The Execution Targets window can be shown via the options dialog (see changing options and preferences for more details).
When in single database mode the connection will change when the selected database in changed, however in multiple database mode the connection must be specifically set by clicking the Connect toolbar button.
When you click on the Execute toolbar button or other execution method, SQL Visualizer will execute the query against each of the databases that have been checked in the list.
If the Transactioned option is set and an error occurs whilst executing, then all changes are rolled back across all databases. If the Transactioned option is not set and an error occurs whilst executing then no rollback is performed and those databases that do not cause an error will be fully committed and those that fail may end up in an inconsistent state.
N.B. It is important to realise that a query will only execute against the active connection if it is included in the list of execution targets.
Because multiple database mode is a powerful feature that if used incorrectly could lead to undesirable results, it is recommended you only switch to it when required, and then switch back to single database mode when complete.