Using SQL Prompt to speed up your queries: examples

The following examples show how SQL Prompt can help you to write queries more quickly. All these examples use the Adventure Works database for SQL Server 2005.

Example 1: Writing a simple query

1. In your query editor, start a new query on the AdventureWorks database.
2. Type:

   se (press Tab) * fr

   As you type, the suggestions box is displayed. Whenever you press the Tab key, SQL Prompt inserts the currently highlighted suggestion into the query:

   

   Press Tab, then the Space bar, then type:

   p

   The suggestions box shows all relevant suggestions beginning with p :

   

   Note: SQL Prompt always places the most contextually relevant types of suggestions at the top of the list, depending on what you have typed in your query.

3. Press the Down arrow key once to highlight the ProductCategory table. You can check if this is the correct table by looking at the column names and data types in the object definition box:

   

4. Press Enter to insert the table and complete the query. Your query should now look like this:

   

Note that in SQL Server 2005 and 2008, SQL Prompt automatically inserts the schema name for objects in non-default schemas.

Quick tips:

• Use the Up and Down arrow keys to move through the list of suggestions.
• Press Tab or Enter to insert the current suggestion. To set other insertion keys, for example Space bar or Dot (.), see Setting main behavior options.
• To view the object creation script for an object instead of a summary, click the Script tab in the object definition box. The Script tab will be the default view from this point onwards.
• To turn off the automatic display of the object definition box, see Using the object definition box.
• Note that SQL Prompt automatically replaces lower case keywords with upper case as you type. You can change the case used for keywords. See Options for formatting your code.
• You can also change how object names are qualified when inserted into your code. See Inserting suggestions into your code.

Example 2: Using the Column Picker to select and insert columns

In this example, the Space bar is used as an additional insertion key. To define the Space bar as an insertion key:

• On the SQL Prompt menu, select Options, then
• In the Options dialog box, go to the Main > Behavior page, then under Insertion Keys, select Space bar, then click OK.

From now on, each time you press the Space bar, the currently highlighted suggestion will be inserted into your query.

1. Create a new query and type:

   se * fr cus (include all the spaces)

   then press Enter.

   SQL Prompt will insert the matching suggestion whenever you press the Space bar, so your query should now look like this:

   

   Note: you can move the cursor to just after the * and press Tab to insert all columns:

   

2. To insert specific columns from the CUSTOMER table, rather than all the columns, move the cursor to just after the * and click the Backspace button to delete *.
3. Press Ctrl + Space to display the suggestions box.
4. Press Ctrl + Left arrow to show the Column Picker:

   

   The column picker allows you to choose multiple columns from the table. Primary key  and foreign key  columns are indicated by key icons.

   

5. Use the Up and Down arrow keys to move throught the list, then press the Space bar to select a column for insertion. You can also use the mouse to select the check box for each column to be inserted:

   

6. Press Enter to insert all the columns into your query. Your query should now look like this:

   

   Columns are inserted in the order in which they were selected; and automatically formatted based on the options set in the Format pages of the SQL Prompt Options dialog box.

Quick tips:

• By default, columns are listed in alphabetical order. To list them in the order they are defined in the table, select List in table order. When inserting columns from more than one table, List in table order groups columns by table rather than in a single alphabetical list.
• When the Column Picker is displayed, type the first few letters of a column name to filter the list. Any columns already selected are displayed at the bottom of the list and will still be inserted.
• Clicking Select All toggles between selecting all columns and selecting none.
• You can change how SQL Prompt lays out the inserted code (for example, to place each column on a new line). See Formatting your code.
• The first time you display the column picker, a callout message box appears; click X to close it. Once you close it, the tooltip will not reappear.

Example 3: Using a snippet to insert a block of code

1. Create a new query and type s

   The suggestions box shows all suggestions that start with s.

2. Press Ctrl + Down arrow twice to change the category of suggestions to Snippets.
3. Press the Down arrow key to move down the list of snippets to highlight ssf.

   

4. Press any of the insertion keys (for example Space bar, Tab or Enter) to insert the snippet code. The position of the cursor after the snippet has been inserted is specified as part of the snippet definition.

   

Quick tips:

• Use Ctrl + Down to change the category of suggestion (for example, to see only views or stored procedures in the suggestions box).
• To make a snippet from a block of code in your editor, highlight it, then right-click and select Make Snippet.
• SQL Prompt is pre-configured with a large number of default snippets. To see a list of all snippets, and to edit, delete or create new snippets, on the SQL Prompt menu, select Snippet Manager.

For more information on snippets, see Managing snippets.

Example 4: Writing a full INSERT statement

1. Create a new query and type ii.
2. This is a snippet; Press Tab to expand it to an INSERT INTO statement and press the Space bar to display the suggestions box again:

   

3. Type cus to filter the list to the two customer tables, then press Enter to insert the first (highlighted) table: Customer.

   SQL Prompt completes the syntax of the INSERT statement, and inserts the name, data type, and default value for each column as comments:

   

   The insertion point is positioned for you to insert the values.

Quick tips:

• By default, column names, data types and default values are automatically inserted when you write an INSERT statement. You can turn off some or all of these defaults using the SQL Prompt options dialog. See Customizing inserted code.
• To see a list of columns in a table, and their data type, move the mouse pointer over a table name to display the tooltip, then click the tooltip:

  

  The object definition box is displayed, showing the summary for the object. If the Script tab is displayed instead, click Summary:

  

Example 5: Executing functions and stored procedures

1. Create a new query and type EXEC
2. Press the Space bar.

   The suggestions box is displayed, with all the functions and stored procedures at the top of the list. Select a function in the list, then click the Script tab to see a preview of the object creation SQL script in the object definition box for the selected object.

   

   Note: You can click the Summary tab in the object definition box to view a list of parameters for that function:

   

3. Press Enter to insert the function into your code:

   

Quick tips:

• By default, parameters are automatically inserted into functions and stored procedures, and the data type and default value is displayed for each paramater. You can change this behavior in the SQL Prompt Options dialog box. See Customizing inserted code for more details.
• To see only stored procedures in the suggestions box, click the All Suggestions tab at the bottom of the suggestions box, then click Stored Procedures:

  

• To view system functions and stored procedures in the suggestions box, select List system objects on the Suggestions > Types of suggestion page in the SQL Prompt Options dialog box.

Example 6: Writing a cross-database query

Cross-database queries are automatically supported in SQL Prompt. You do not need to select an option to enable them.

1. Create a new query on the AdventureWorks database.
2. Type SELECT * FROM pub
3. SQL Prompt lists the pubs database as a matching suggestion:

   

4. Press Tab to insert the database name into your query, then type . (Dot). As soon as you press Dot, SQL Prompt reads all the database objects from the pubs database. For large databases, this may take a few minutes.
5. If you do not have permissions to connect to that database, or to access some objects in the database, a warning dialog is displayed:

   

   Note: This warning may also be displayed if SQL Prompt has run out of memory to store the objects from this database.

   See Managing connections and memory.