For SQL Server 6.5 information, see SET Statement in What's New for SQL Server 6.5.
Sets SQL Server query-processing options for the duration of the user's work session, or for the duration of a running trigger or a stored procedure.
SET {
{{{ANSI_NULL_DFLT_OFF | ANSI_NULL_DFLT_ON}
| ARITHABORT
| ARITHIGNORE
| FMTONLY
| FORCEPLAN
| IDENTITY_INSERT [database.[owner.]]tablename
| NOCOUNT
| NOEXEC
| OFFSETS {keyword_list}
| PARSEONLY
| PROCID
| QUOTED_IDENTIFIER
| SHOWPLAN
| STATISTICS IO
| STATISTICS TIME}
{ON | OFF}}
| DATEFIRST number
| DATEFORMAT format
| DEADLOCKPRIORITY {LOW | NORMAL}
| LANGUAGE language
| ROWCOUNT number
| TEXTSIZE number
| TRANSACTION ISOLATION LEVEL {READ COMMITTED | READ
UNCOMMITTED | REPEATABLE READ | SERIALIZABLE}}
where
Note ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF are mutually exclusive options, yet both options exist to determine whether or not the database option should be overridden. When turned ON, each option forces the opposite option to OFF. Neither option, when set OFF, turns the opposite option ON. Instead, turning an option OFF only discontinues the current ON setting.
Note If neither ARITHABORT nor ARITHIGNORE is set, SQL Server returns NULL and returns a warning message after the query is executed.
In SQL Server, SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced.
Statement processing consists of two phases, compilation and execution. NOEXEC stops the processing after compilation.
The OFFSETS option is used only in a DB-Library application.
Note Setting the ROWCOUNT option causes all Transact-SQL statements to stop processing when they have each affected number rows. This includes triggers and data modification statements such as INSERT, UPDATE, and DELETE as well as the filling of a keyset cursor at the specified number of rows. This option should be used with caution and primarily with the SELECT statement.
The DB-Library variable DBTEXTLIMIT also limits the size of text data returned with a SELECT statement. If DBTEXTLIMIT is set to a smaller size than TEXTSIZE, only the amount specified by DBTEXTLIMIT is returned. For more information, see Microsoft SQL Server Programming DB-Library for C.
where
These options have the same effect as setting HOLDLOCK on all tables in all SELECT commands within a transaction.
If you use the SET statement inside a trigger or a stored procedure, the option reverts to its former setting after the trigger or stored procedure is executed.
Options changed with the SET statement take effect at the end of the batch. You can combine SET statements and queries in the same batch, but the SET options won't apply to the queries in that batch.
This example shows that setting the SHOWPLAN returns a description of the processing plan for each query prior to returning the results set. Notice that the SET option must be turned on prior to the execution of the SELECT statement.
SET SHOWPLAN ON go select * from publishers go STEP 1 The type of query is SELECT FROM TABLE publishers Nested iteration Table Scan pub_id pub_name city state country ------ ------------------------- ------------- -------- ------------ 0736 New Moon Books Boston MA USA 0877 Binnet & Hardley Washington DC USA 1389 Algodata Infosystems Berkeley CA USA 1622 Five Lakes Publishing Chicago IL USA 1756 Ramona Publishers Dallas TX USA 9901 GGG&G München (null) Germany 9952 Scootney Books New York NY USA 9999 Lucerne Publishing Paris (null) France (8 row(s) affected)
This example shows how the query plan can be viewed without returning the results.
SET SHOWPLAN ON SET NOEXEC ON go SELECT * FROM publishers go STEP 1 The type of query is SELECT FROM TABLE publishers Nested iteration Table Scan
ROWCOUNT stops processing after the specified number of rows. In this example, note that x rows meet the criteria of advances less than or equal to $5,000; however, from the number of rows returned by the update, you can see that not all rows were processed. ROWCOUNT affects all Transact-SQL statements.
SELECT Count = count(*)
FROM titles
WHERE advance >= 5000
go
Count
-----------
10
(1 row(s) affected)
SET ROWCOUNT 4
go
UPDATE titles
SET advance = 5000
WHERE advance >= 5000
go
(4 row(s) affected)
The publishers table is created without explicitly defining the nullability for each of the columns. In the INSTPUBS.SQL script, all nullability is explicitly defined. But for this example, all NULL definitions were replaced by a single SET option defined prior to the CREATE TABLE batch. Also note that the SET option is explicitly turned off within the script.
In scripts run against databases where nullability may be changed, make sure to do one of the following:
SET ANSI_NULL_DFLT_ON ON
go
CREATE TABLE publishers
(
pub_id char(4) NOT NULL
CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED
CHECK (pub_id in ('1389', '0736', '0877', '1622', '1756')
OR pub_id like '99[0-9][0-9]'),
pub_name varchar(40),
city varchar(20),
state char(2),
country varchar(30) DEFAULT('USA')
)
go
sp_help publishers
go
/* For simplicity only a subset of the results of sp_help is
shown here. */
Column_name Nullable
------------- --------------
pub_id no
pub_name yes
city yes
state yes
country yes
SET ANSI_NULL_DFLT_ON OFF
In this example, the TRANSACTION ISOLATION LEVEL is set for the session. For each Transact-SQL statement that follows, SQL Server will hold all of the shared locks until the end of the transaction.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ go BEGIN TRANSACTION SELECT * FROM publishers SELECT * FROM authors ... COMMIT TRANSACTION
In this example, all arithmetic SET options and the results displayed by an invalid entry are shown. Table t1 is created with only one column of type tinyint. In the statements that follow, the default SQL Server behavior, and ARITHABORT and ARITHIGNORE are shown.
CREATE TABLE t1
(
col1 tinyint NOT NULL
)
go
INSERT t1 VALUES(-1)
go
Arithmetic overflow occurred.
(0 row(s) affected)
SET ARITHABORT ON
go
INSERT t1 VALUES(-1)
go
Arithmetic overflow error for type tinyint, value = -1.
SET ARITHABORT OFF
SET ARITHIGNORE ON
go
INSERT t1 VALUES(-1)
go
(0 row(s) affected)
| Batches | Keywords |
| CREATE PROCEDURE | NULL Values |
| CREATE TRIGGER | Quoted Identifiers |
| IDENTITY |