SQL Server Command Prompt Management Tools

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 Server—copies 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

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.


Session Settings In Effect by Default within Client Tools

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

 

* NOTE: Although Query Analyer allows the “textsize” option to be set the UI parameters override this setting with a maximum of 8192. By default, QA only bring back 256 characters of text.
Session Setting Issues for Query Analyzer and osql.exe

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:

  • Effects of SQL-92 Options
  • isql Utility
  • osql Utility
  • SET
  • Set Options that Affect Results

 

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.

 

 


SQL Query Analyzer Keyboard Shortcuts

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+DEL

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+DEL

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