SET fmtonly ON returns only field information @@ Error @@ Trancount1. SET DEADLOCK_PRIORITY2. SET LOCK_TIMEOUT3. @@ LOCK_TIMEOUT4.SET IDENTITY_INSERT5.SET IMPLICIT_TRANSACTIONS6.SET NOCOUNT7. @@ ROWCOUNT8.SET ROWCOUNT9.SET TRANSACTION ISOLATION LEVEL10.SET XACT_ABORT1. Set Deadlock_Priority Description: Controlling the reactive way of the session when there is a deadlock. If the two processes lock the data, and until the other process releases your own lock, each process can release your lock, that is, the deadlock occurs. Syntax: set deadlock_priority {low | normal | @deadlock_var} Parameters: Low Specifies the current session as the preferred deadlock victim. Microsoft® SQL ServerTM automatically returns a transaction of the dead lock, and returns the client application to the 1205 deadlock error message. NORMAL specifies the session to return to the default deadlock processing method. @Deadlock_var is a character variable that specifies the deadlock processing method. If you specify a Low, @deadlock_var is 3; if Normal is specified, @deadlock_var is 6. Note: The setting of Set DeadLock_Priority is set in execution or runtime, not when analyzing. Permissions: Set Deadlock_Priority Permissions Award all users by default. 2. SET LOCK_TIMEOUT Description: Specifies the number of milliseconds waiting for the statement waiting to be released. Syntax: SET LOCK_TIMEOUT TIMEOUT_PERIOD Parameters: Timeout_Period is the number of milliseconds passing before the Microsoft® SQL ServerTM returns to lock errors. The value is -1 (default) indicates that there is no timeout period (ie, indefinitely waiting). When the lock waits to exceed the timeout value, an error will be returned. When the value is 0, it means that it is not waiting at all, and the information is returned to the lock. Note: When the connection starts, the value is -1. After setting changes, new settings have been valid in the rest of the connection time. Set Lock_Timeout settings are set when executed or runtime, not when analyzing. The READPAST Lock prompts another way to the SET option. Permissions: set lock_timeout Permissions Award all users by default. Example: The following example sets the lock timeout period to 1,800 milliseconds. Set lock_timeout 1800 go 3. @@ lock_timeout Description: Returns the current lock timeout setting of the current session, in milliseconds. Syntax: @@ lock_timeout Return Type: Integer Note: SET LOCK_TIMEOUT Allows the application to set the statement to wait for the longest time of blocking resources. When a statement has waited more than the time set for Lock_Timeout, the locked statement will be canceled automatically and returns an error message to the application. At the beginning of a connection, @@ lock_timeout returns a -1 value. Example: The following example shows the result set when a Lock_Timeout value is not set.
SELECT @@ lock_timeout The following is the result set: --------------- -1 The following example setting lock_timeout is 1800 ms, then call @@ lock_timeout. SET LOCK_TIMEOUT 1800 SELECT @@ Lock_timeout below is the result set: ------------------------------ 1800 4. Set Identity_Insert Description: Allow Insert the explicit value insert the identity column of the table. Syntax: set Identity_Insert [Database] {Table} {on | OFF} Parameters: Database is the database name that specifies the table resides. Owner is the name of the table owner. Table is a table name containing the identity column. Note: At any time, only one table of iDentity_insert attributes in the session can be set to ON. If a table has set this property to ON, and the SETITENTITY_INSERT ON statement is sent to another table, Microsoft® SQL ServerTM returns an error message, indicating that set Identity_Insert is set to ON and report this property is set to ON. Table. If the insert value is greater than the current identification value of the table, the SQL Server automatically uses the new insert value as the current identifier value. Set Identity_INSERT settings are set when executed or runtime, not when analyzing. Permissions: Execution Permissions The default grants Sysadmin fixed server roles and DB_OWNER and DB_DDLADMIN fixed database roles and object owners. Example: The following example creates a table containing the identity column and displays how to set the gap in the identity value caused by the delete statement using the set Identity_Insert.
- Create products table CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar (40)) GO -.. Inserting values into products table INSERT INTO products (product) VALUES ( 'screwdriver') INSERT INTO products (product) VALUES ( 'hammer') INSERT INTO products (product) vALUES ( 'saw') INSERT INTO products (product) vALUES ( 'shovel') GO -. Create a gap in the identity values DELETE products WHERE product = 'saw' GO SELECT * FROM products GO - Attempt to insert an explicit ID value of 3; -.. should return a warning INSERT INTO products (id, product) VALUES (3, 'garden shovel') GO - SET IDENTITY_INSERT to ON SET IDENTITY_INSERT products ON GO - Attempt to insert an explicit ID value of 3 INSERT INTO products (id, product) VALUES (3, 'garden shovel') GO SELECT * FROM products GO -. Drop products table DROP TABLE products GO 5. SET Implicit_Transactions Description: Sets the hidden transaction mode for connectivity. Syntax: set implicit_transactions {on | OFF} Note: When set to ON, Set Implicit_Transactions will set the connection to the hidden transaction mode. When set to OFF, the connection returns to the automatic submission of transaction mode. When the connection is a hidden transaction mode and is currently not in the transaction, the following statement will start the transaction: ALTER TABLE FETCH Revoke Create Grant Select Delete Insert Truncate Table Drop Open Update If the connection is already open, the above statement does not start the new Transaction. For transactions that are automatically opened because of this set to ON, the user must explicitly submit or roll back when the transaction ends. Otherwise, when the user is disconnected, the transaction and all data changes therebetween will roll back. After the transaction is submitted, you can start a new transaction in any of the above sentences. The hidden transaction mode will remain valid until the connection execution set implicit_transactions off statement returns the connection to Auto Submit mode. In automatic submission mode, if each statement is successfully completed, it is submitted. When connecting, the SQL Server ODBC driver and the Microsoft OLE DB provider for SQL Server are set to OFF.
Connections from the DB-Library application, Set Implicit_Transactions defaults to OFF. Set Implicit_Transactions will be enabled when SET ANSI_DEFAULTS is ON. Set Implicit_Transactions sets settings in execution or runtime, not when analyzing. Example: The following example demonstrates explicitly or implicitly launched the transaction when setting the implicit_transactions set to ON. It uses the @@ TRANCOUNT function to demonstrate the open transaction and the closed transaction. USE pubs GO CREATE table t1 (a int) GO INSERT INTO t1 VALUES (1) GO PRINT 'Use explicit transaction' BEGIN TRAN INSERT INTO t1 VALUES (2) SELECT 'Tran count in transaction' = @@ TRANCOUNT COMMIT TRAN SELECT 'Tran count outside transaction '= @@ TRANCOUNT GO PRINT' Setting IMPLICIT_TRANSACTIONS ON 'GO SET IMPLICIT_TRANSACTIONS ON GO PRINT' Use implicit transactions' GO -. No BEGIN TRAN needed here INSERT INTO t1 VALUES (4) SELECT 'Tran count in transaction' = @@ TRANCOUNT COMMIT TRAN SELECT 'Tran count outside transaction' = @@ TRANCOUNT GO PRINT 'Use explicit transactions with IMPLICIT_TRANSACTIONS ON' GO BEGIN TRAN INSERT INTO t1 VALUES (5) SELECT 'Tran count in transaction' = @@ TRANCOUNT COMMIT TRAN SELECT 'TRAN Count Outside Transaction * from T1 Go - Need to Commit This TRAN TOO! DROP TABLE T1 Commit TRAN Go 6. SET NOCOUNT Description: Make the return result does not contain the transact-sql statement Information about the number of rows affects. Syntax: Set NoCount {on | OFF} Note: When set nocount is ON, the count is not returned (indicating the number of rows affected by the Transact-SQL statement). Returns the count when SET NOCOUNT is OFF. Even when SET NOCOUNT is ON, the @@ rowcount function is updated. When the SET NOCOUNT is ON, the DONE_IN_PROC information of each statement in the stored procedure will not be sent to the client.
When using the Utility provided by Microsoft® SQL ServerTM, the "NN Rows Affected" will not be displayed in the query results at the end of the query result when using the Using Microsoft® SQL ServerTM. If some of the statements contained in the stored procedure do not return a lot of actual data, the setting can significantly improve performance due to a large number of network traffic. Set NoCount Settings are set when executed or runtime, not when analyzing. Permissions: Set NoCount Permissions Award all users by default. Example: The following example is performed in an OSQL utility or a SQL Server query analyzer to prevent information about the number of rows on the affected row. USE pubs GO - Display the count message SELECT au_lname FROM authors GO USE pubs GO -. SET NOCOUNT to ON and no longer display the count message SET NOCOUNT ON GO SELECT au_lname FROM authors GO -. Reset SET NOCOUNT to OFF SET. Nocount Off Go 7. @@ rowcount Description: Returns the number of rows affected by the above statement. Syntax: @@ rowcount Return Type: Integer Note: Any statement that does not return row set this variable to 0, such as an IF statement. Example: The following example executes the Update statement and use @@ rowcount to detect a row that has changed. Update authors set au_lname = 'Jones' WHERE AU_ID = '999-888-7777' if @@ rowcount = 0 Print 'Warning: no rows were updated' 8. SET ROWCOUNT Description: Make Microsoft® SQL ServerTM to return to the specified row Stop processing queries after the number. Syntax: set rowcount {number | @number_var} Parameters: Number | @number_var is the number of rows (integers) to process before stopping given query. Note: It is recommended to rewrite the DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT to use Top syntax. For more information, see Delete, INSERT, or UPDATE. Ignore the Set RowCount option settings for the INSERT, UPDATE, and DELETE statements executed on the remote table and the local and remote partition view. To turn off this option (in order to return all rows), specify set rowcount as 0. NOTE Setting The Set RowCount option will cause most Transact-SQL statements to stop processing after the specified number of rows. This includes data modifying statements such as triggers and INSERT, UPDATE and DELETE. The RowCount option is invalid to the dynamic cursor, but limits the rows of key sets and not knowing the game. Be cautious when using this option, it is mainly used with the SELECT statement. If the value of the line is smaller, set rowcount replaces the SELECT statement Top keyword.
Set RowCount settings are set when executed or runtime, not when analyzing. Permissions: SET ROWCOUNT permissions are awarded all users. Example: set rowcount stops processing after the specified row. In the following example, note that there is a condition that the X line satisfies less than or equal to or 000; however, it can be seen from the number of rows returned from the update to be processed. ROWCOUNT affects all Transact-SQL statements. Use Pubs Go Select Count (*) AS CNT from Titles Where Advance> = 5000 Go is the result set: CNT ----------- 11 (1 row (s) affected) Now, set ROWCOUNT to 4, and update the advance payment equal to or greater than all rows of 000. - set rowcount to 4. SET ROWCOUNT 4 Go Update Titles Set Advance = 5000 WHERE Advance> = 5000 Go 9. SET Transaction Isolation Level Description: Controls the default transaction lock behavior of all Microsoft® SQL ServerTM SELECT statements issued by the connection. Syntax: SET Transaction | Read Uncommitted | REPEATABLE READ | SERIALIZABLE} Parameted: Read Committed Specifies that the shared lock is controlled to avoid dirtying, but the data can be changed before the end of the transaction, resulting in non-repeatable reading Take or phantom data. This option is the default value of SQL Server. Read uncommitted performs dirty or 0-level isolation lock, which means that it does not make a sharing lock, nor does it accept the lock. When this option is set, the execution of the data may not be submitted to read or dirty; the value within the data can be changed before the end of the transaction, and the line can also appear in the data set or disappear from the data set. The role of this option is the same as all tables in all statements in transactions. This is the minimum level in the four isolation levels. The REPEATABLE READ locks all the data used in the query to prevent other users from updating data, but other users can insert new phantom rows into the dataset, and the phantom row includes in subsequent reading in the current transaction. Because it is concurrent than the default isolation level, this option should only be used if necessary. Serializable places a range lock on the data set to prevent other users from updating the data set before the transaction is completed or insert the row into the data set. This is the maximum level in the four isolation levels. Because concurrent levels are lower, this option should only be used if necessary. The role of this option is set to Holdlock on all tables in all SELECT statements in the transaction. Note: One can only set one of these options at a time, and the options will always be valid for that connection until this option is explicitly changed. This is the default behavior, unless specified an optimized option on the table level in the FROM clause of the statement. The setting of the SET Transaction isolation level is set at the time of execution or runtime, not when the analysis is analyzed. Example: The following example Sets Transaction Isolation Level. For each subsequent Transact-SQL statement, SQL Server controls all shared locks until the end of the transaction.
