Parameters, also called variables, can be embedded in a SQL statement without pre-defining the data type and value. All variable names are highlighted in red (by default) after the formatting. Variables with or without a "@" sign are recognized. When you optimize the SQL statement or retrieve the query plan, run time, the run result for a SQL statement that uses a parameter, you are prompted to enter the variables using the Parameters window.
Note: You can retrieve the query plan for a SQL statement that has a variable without the need to enter the data type or value by executing the Show Default Plan function from the SQL menu.
The data type and value of parameters are entered using the Parameters window. If parameters are embedded in the SQL statement, the Parameters window displays each time you optimize the SQL statement, execute the Show Plan, Run Result, Run Time, Batch Run functions, or create a temporary table through the User-Defined Temp Table window.
The parameter values that you have previously entered can be saved by selecting the Enable SQL parameter history Preferences setting. If the same parameter is used in a SQL statement again, the value and the data type from the last time it was used is automatically entered for you.
To help identify the data type and value, click Browse to expand the Parameters window. The bottom pane displays displaying object and data information corresponding to the selected objects from the SQL statement. Select the object to browsed using the Objects and Columns drop-down list. If a column is selected (asterisk * selects all columns from the selected object), the corresponding data type is shown.
Click Load Data and highlight the cell with the variable value from the data grid. Then click Use Data & Datatype or double-click the cell to copy the compatible data type and value to the parameter selected at the top of the window.
Click Use Datatype to copy a compatible data type to the parameter selected at the top of the window.
After the data type and value of all the parameters have been selected, click OK.
When you are selecting database objects from the list of all objects, you can filter the list in order to more quickly locate specify objects.
To open the Add Database Objects window.
Enter the following information to filter the database objects.
Specify the database name where the objects reside.
Specify the owner of the database objects.
Select the database object type from the list.
Using the % wildcard that replaces multiple characters, enter the filtering criteria. The filter is case sensitive, so you must match the upper or lowercase characters of the database object name.
The SQL Information pane displays in several modules. It provides a variety of information about the SQL statement like the query plan, abstract plan, trace on and run time information. The specific information is dependent upon the function of the module.
The SQL Information pane displays SQL information according to the button you select from the button bar at the top of this pane. The exact buttons that you find at the top of this pane varies from one module to the next. These buttons include: query plan, abstract plan, trace on, other information (SQL classification, connection information, warning messages, etc), all records statistics, first record statistics, Scanner temp table and checked details.
Displays the SQL query plan. When two SQL Information panes are displayed side-by-side, as in the SQL Comparer module and the Analyzer modules, the differences between the operations are highlighted in green.
The Abstract Plan button is only available for Adaptive Server 15 or later and you have selected Dump abstract plan option in the Preferences window.
Displays the abstract plan which describes the query plan using a language created for that purpose. This language contains operators to specify the choices and actions that can be generated by the optimizer.
The Trace On button is only available if you have selected dbcc traceon (3604, 302, 310) option in the Preferences window and have sa_role privilege.
Trace on shows the reason why the Adaptive Server optimizer chose a particular way of executing the original SQL statement, displaying the reasons for index and table joins selection.
The exact information depends on the module. It always displays the SQL classification type. This classification is dependent on the parameters set in the Preferences window to identify whether the original SQL statement is potentially Problematic. It may also display:
Warning or alert information about the SQL statement.
SQL statement type classification: Problematic, Complex, Simple, or Invalid.
Database error message if SQL is classified as Invalid.
SQL conversion applied, if conversions have been added to the scanned SQL text in order for it to generate a query plan, this information is also displayed.
Start position of the SQL statement (SQL Scanner only, for database object, TXT and SQL files).
Connection information (Login name, Server name, Database and User)
Database Settings from the Preferences window.
Session settings for the optimization timeout limit and optimization goals. (Available in Adaptive Server 15 or later) The optimization timeout limit is an Adaptive Server configuration parameter that specifies the amount of time Adaptive Server can spend optimizing a query as a fraction of the total time spent processing the query. The optimization goals are selected by you in the Preferences settings.
Displays the run time information that is collected when retrieving all records of the query.
Displays the run time information that is collected when retrieving the first record of the query.
Both All Records and First Record buttons display the run time statistical information:
The amount of disk activities needed to execute the SQL statement.
Number of scans performed
Number of pages read from the data cache
Number of pages placed into the cache
Number of pages read from disk
Number of scans performed
Number of milliseconds required to parse, compile, and execute each SQL statement.
SQL script used to test run the SQL statement.
Displays the temporary table SQL statement assumed to create or modify the temporary table used on the scanned SQL statement if the SQL Scanner finds it in the source.
If the selected SQL statement is checked, then this page displays the date time of when the SQL statement was checked, the name of person who checked the SQL, the status, and the checked description.
Displays information about how the index will be created and the estimated amount of space required for the index.