Avec le soft que j'utilise, c'est simplissime, tu coches des cases dans une fenêtre d'options ![[:cosmoschtroumpf]](https://forum-images.hardware.fr/images/perso/cosmoschtroumpf.gif "[:cosmoschtroumpf]")
Pour les sybasiens, un c/c de l'aide sql server (compatible sybase) (pavé inside)
For more information about how SQL Server 6.5 affects existing database systems and for updated SQL Server 6.5 syntax, see SET Statement.
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.
Syntax
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
ANSI_NULL_DFLT_OFF
Alters the session's behavior not to use ANSI compatibility for nullability. When this session option is turned on, it overrides the default database nullability configured by the sp_dboption system stored procedure. New columns defined without explicit nullability will be defined not to allow NULLs (as long as the underlying datatype is nullable). For more information on datatypes, see the Datatypes topic.
ANSI_NULL_DFLT_ON
Alters the sessions behavior to ANSI compatibility for nullability. When this session option is turned on, it overrides the default database nullability configured by the sp_dboption system stored procedure. New columns defined without explicit nullability will be defined to allow NULLs.
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.
ARITHABORT
Terminates a query when an overflow or divide-by-zero error occurs during query execution. It is possible for rows in a results set to be returned prior to the overflow or divide-by-zero occurrence.
ARITHIGNORE
Returns NULL when an overflow or divide-by-zero error occurs during a query. No warning message is returned.
Note If neither ARITHABORT nor ARITHIGNORE is set, SQL Server returns NULL and returns a warning message after the query is executed.
FMTONLY
Returns only metadata to the client. No rows will be processed or sent to the client as a result of the request when FMTONLY is turned ON.
FORCEPLAN
Makes the SQL Server optimizer process joins in the same order as tables appear in the FROM clause. FORCEPLAN essentially overrides the optimizer.
IDENTITY_INSERT [database.[owner.]]tablename
Specifies the database, owner and table in which explicit identity values can be inserted. Only one table per user can have the IDENTITY_INSERT option set. If the value inserted is larger than the current identity value for the table, SQL Server will automatically use the new inserted value as the current identity value. For details, see the IDENTITY property.
NOCOUNT
Turns off the message returned at the end of each statement that states how many rows were affected by the statement. The global variable @@ROWCOUNT is updated even when NOCOUNT is turned ON.
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.
NOEXEC
Compiles each query but does not execute it. NOEXEC is often used with SHOWPLAN. Once NOEXEC is turned on, no subsequent statements are executed (including other SET statements) until NOEXEC is turned OFF.
Statement processing consists of two phases, compilation and execution. NOEXEC stops the processing after compilation.
OFFSETS
Returns the keyword_list offset (position in relation to the beginning of the query) of specified keywords in SQL statements. The keyword_list is a list, separated with commas, that can include any of these SQL constructs: SELECT, FROM, ORDER, COMPUTE, TABLE, PROCEDURE, STATEMENT, PARAM, and EXECUTE.
The OFFSETS option is used only in a DB-Library application.
PARSEONLY
Checks the syntax of each query and returns any error messages without generating a sequence tree, compiling, or executing the query. Do not use PARSEONLY in a stored procedure or a trigger. PARSEONLY returns offsets if the OFFSETS option is ON and no errors occur.
PROCID
Returns the identification number of the stored procedure to DB-Library (not to the user) before sending rows generated by that stored procedure.
QUOTED_IDENTIFIER
Causes the current session to differentiate between single and double quotation marks when evaluating an expression. When QUOTED_IDENTIFIER is turned ON, strings with double quotation marks (" ) will not be evaluated or checked against keywords. For details, see the Identifiers topic.
SHOWPLAN
Generates a description of the processing plan for the query and immediately processes it unless NOEXEC is set. This option can indicate whether an index is being used to retrieve query results.
STATISTICS IO
Displays the number of scans, the number of logical reads (pages accessed in cache), and the number of physical reads (number of times the disk was accessed) for each table referenced in the statement.
STATISTICS TIME
Displays the time, in milliseconds, required to parse and compile each command and the time required to execute each step of the command.
DATEFIRST number
Sets the first weekday to a number from 1 through 7. The U.S. English default is 7 (Sunday).
DATEFORMAT format
Sets the order of the date parts (month/day/year) for entering datetime or smalldatetime data. Valid parameters include mdy, dmy, ymd, ydm, myd, and dym. The U.S. English default is mdy.
DEADLOCKPRIORITY {LOW | NORMAL}
Controls how this session reacts when in a deadlock situation. If set to LOW, this process will be the preferred victim of a deadlock situation. Use the NORMAL option to return the session to the default deadlock-handling method.
LANGUAGE language
Specifies the language in which system messages are displayed. The language must be available on the server. The default is us_english.
ROWCOUNT number
Causes SQL Server to stop processing the query after the specified number of rows are returned. To turn this option off (so that all rows are returned), use SET ROWCOUNT 0.
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.
TEXTSIZE number
Specifies the size, in bytes, of text data to be returned with a SELECT statement. If you specify a TEXTSIZE of 0, the size is reset to the default (4K). Setting TEXTSIZE affects the global variable @@TEXTSIZE.
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.
TRANSACTION ISOLATION LEVEL
Controls the default transaction locking behavior for all SQL Server SELECT statements for this connection. Only one of the options can be set at a time, and it remains set for that connection unless it is explicitly changed. This becomes the default behavior unless an optimization option is specified at the table level in the FROM clause of your statement. For details, see the SELECT statement. To see what isolation level is currently set, use DBCC USEROPTIONS. For more information on DBCC, see the DBCC statement.
where
READ COMMITTED
Is the default mode for SQL Server. Setting this option returns transaction locking behavior to the SQL Server default, and you will not encounter "dirty reads." However, because shared locks do not exclusively hold records until the completion of a transaction, you might still encounter non-repeatable reads and phantom values later within your transaction.
READ UNCOMMITTED
Implements "dirty read," or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted data, have values change if reread in the same transaction, and have phantom values.
REPEATABLE READ | SERIALIZABLE
Are currently implemented identically. In some database systems, REPEATABLE READ means that "dirty reads" and non-repeatable reads are not possible but phantoms are possible. However, because REPEATABLE READ is a synonym for SERIALIZABLE in SQL Server's implementation, phantoms are not allowed when one of these is set. Because concurrency is lower, use these options only when necessary.
These options have the same effect as setting HOLDLOCK on all tables in all SELECT commands within a transaction.
Remarks
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.
Examples
A. SHOWPLAN
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)
B. SHOWPLAN and NOEXEC
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
C. ROWCOUNT
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)
D. ANSI NULL Default, Create Tables and sp_help
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:
· Explicitly define the nullability for each column.
· Manually set the nullability for the script's session using one of the ANSI NULL default options.
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
E. TRANSACTION ISOLATION LEVEL in an Explicit Transaction
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
F. Arithmetic Handling
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)
Message édité par Fred999 le 29-03-2010 à 16:10:45
![[:sadnoir]](https://forum-images.hardware.fr/images/perso/sadnoir.gif "[:sadnoir]")
J'ai déjà été amené à faire ça dans le cadre de calcul matriciel.![[:cerveau ouch]](https://forum-images.hardware.fr/images/perso/cerveau ouch.gif "[:cerveau ouch]")
![[:ddrs55]](https://forum-images.hardware.fr/images/perso/ddrs55.gif "[:ddrs55]")
![[:pingouino]](https://forum-images.hardware.fr/images/perso/pingouino.gif "[:pingouino]")
)![[:bien]](https://forum-images.hardware.fr/images/perso/bien.gif "[:bien]")
![[:kbchris]](https://forum-images.hardware.fr/images/perso/kbchris.gif "[:kbchris]")
![[:romf]](https://forum-images.hardware.fr/images/perso/romf.gif "[:romf]")