SQL Server command prompt management tools allow you to enter Transact-SQL statements and execute script files. The following table describes the most frequently used command prompt utilities that are provided with SQL Server. Each file is an executable program.
Utility |
Description |
|
|
isql.exe |
Former utility used for automating scripts and batches to the server. The communications are made using the now outdated DBLibrary (DBLib) to communicate with SQL Server. This is included in SQL Server 2000 for backward compatibility only. All batches that use isql.exe should be changed to use osql.exe however; the scripts may behave differently between these two tools. * |
osql.exe |
Is the updated client utility for scripting and batch automation. This updated version uses Open Database Connectivity (ODBC) to communicate with SQL Server. Connections made using osql.exe will have many ANSI behavioral options turned on. * |
bcp.exe |
Batch utility used to import and export data to and from SQL Servercopies data to or from a data file in a user-specified format See the Bulk Insert statement instead. |
*IMPORTANT: Fully test batches moved from isql.exe to osql.exe as session settings between dblib and odbc differ. This may cause your scripts to produce different results when using osql.
Session settings are environment options defined by the SET statement that can impact the way that Transact-SQL code executes. The SET statements can be generally grouped into these categories.
Date and time Handling date and time data. For example, mdy is the expected format for inserted data in the datetime data type.
Lock and Isolation Levels Handling lock behavior lock granularity and types/length of time that locks are held.
Query Execution and Statistics Displaying information about how a query was processed and how many I/Os were performed to read the data.
ANSI SQL-92 settings Handling a variety of ANSI related issues such as how NULL values are used in computations and what type of syntax is supported when referencing rows that have a column value of NULL.
Option |
isql.exe |
osql.exe |
Query Analyzer |
textsize |
4096 |
2147483647 |
64512* |
language |
us_english |
us_english |
us_english |
dateformat |
mdy |
mdy |
mdy |
datefirst |
7 |
7 |
7 |
quoted_identifier |
NOT SET |
NOT SET |
SET |
arithabort |
NOT SET |
SET |
SET |
ansi_null_dflt_on |
NOT SET |
SET |
SET |
ansi_defaults |
NOT SET |
NOT SET |
SET |
ansi_warnings |
NOT SET |
SET |
SET |
ansi_padding |
NOT SET |
SET |
SET |
ansi_nulls |
NOT SET |
SET |
SET |
concat_null_yields_null |
NOT SET |
SET |
SET |
Session settings between these two utilities differ most apparent is the session setting for QUOTED_IDENTIFIER. The QUOTED_IDENTIFIER session setting is ON in Query Analyzer and is OFF in osql. Session settings can have a profound effect on how code executes and whether or not certain statements perform well. We will look more at session settings and what they impact, throughout the day.
Topics of importance from the Books Online are:
Note |
If you change session settings using the SET statement, the SET statements stay in effect for the session until you execute the SET statement again and turn the option OFF or until you end the session.
This table displays the keyboard shortcuts available in SQL Query Analyzer.
Activity |
Shortcut |
Bookmarks: Clear all bookmarks. |
CTRL-SHIFT-F2 |
Bookmarks: Insert or remove a bookmark (toggle). |
CTRL+F2 |
Bookmarks: Move to next bookmark. |
F2 |
Bookmarks: Move to previous bookmark. |
SHIFT+F2 |
Cancel a query. |
ALT+BREAK |
Connections: Connect. |
CTRL+O |
Connections: Disconnect. |
CTRL+F4 |
Connections: Disconnect and close child window. |
CTRL+F4 |
Database object information. |
ALT+F1 |
Editing: Clear the active Editor pane. |
CTRL+SHIFT+ |
Editing: Comment out code. |
CTRL+SHIFT+C |
Editing: Copy. You can also use CTRL+INSERT. |
CTRL+C |
Editing: Cut. You can also use SHIFT+DEL. |
CTRL+X |
Editing: Decrease indent. |
SHIFT+TAB |
Editing: Delete through the end of a line in the Editor
pane. |
CTRL+ |
Editing: Find. |
CTRL+F |
Editing: Go to a line number. |
CTRL+G |
Editing: Increase indent. |
TAB |
Editing: Make selection lowercase. |
CTRL+SHIFT+L |
Editing: Make selection uppercase. |
CTRL+SHIFT+U |
Editing: Paste. You can also use SHIFT+INSERT. |
CTRL+V |
Editing: Remove comments. |
CTRL+SHIFT+R |
Editing: Repeat last search or find next. |
F3 |
Editing: Replace. |
CTRL+H |
Editing: Select all. |
CTRL+A |
Editing: Undo. |
CTRL+Z |
Execute a query. You can also use CTRL+E (for
backward compatibility). |
F5 |
Help for SQL Query Analyzer. |
F1 |
Help for the selected Transact-SQL statement. |
SHIFT+F1 |
Navigation: Switch between query and result panes. |
F6 |
Navigation: Switch panes. |
Shift+F6 |
Navigation: Window Selector. |
CTRL+W |
New Query window. |
CTRL+N |
Object Browser (show/hide). |
F8 |
Object Search. |
F4 |
Parse the query and check syntax. |
CTRL+F5 |
Print. |
CTRL+P |
Results: Display results in grid format. |
CTRL+D |
Results: Display results in text format. |
CTRL+T |
Results: Move the splitter. |
CTRL+B |
Results: Save results to file. |
CTRL+SHIFT+F |
Results: Show Results pane (toggle). |
CTRL+R |
Save. |
CTRL+S |
Templates: Insert a template. |
CTRL+SHIFT+INSERT |
Templates: Replace template parameters. |
CTRL+SHIFT+M |
Tuning: Display estimated execution plan. |
CTRL+L |
Tuning: Display execution plan (toggle ON/OFF). |
CTRL+K |
Tuning: Index Tuning Wizard. |
CTRL+I |
Tuning: Show client statistics |
CTRL+SHIFT+S |
Tuning: Show server trace. |
CTRL+SHIFT+T |
Use database. |
CTRL+U |