Several setting options are frequently used when SQL Server is used!

1. Set deadlock_priority

Note: control the session response mode when a deadlock occurs. If both processes lock data and wait until other processes release their own locks, each process can release its own locks, that is, deadlock occurs.

Syntax: Set deadlock_priority {LOW | normal | @ deadlock_var}

Parameter: Low specifies the current session as the deadlock victim of choice. Microsoft SQL Server automatically rolls back the transaction of the deadlock victim and returns the 1205 deadlock error message to the client application.
Normal specifies the default deadlock handling method returned by the session.
@ Deadlock_var is the character variable that specifies the deadlock handling method. If low is specified, @ deadlock_var is 3; if normal is specified, @ deadlock_var is 6.

Note: Set deadlock_priority is set during execution or running, rather than during analysis.

Permission: Set deadlock_priority permission is granted to all users by default.

 

2. Set lock_timeout

Description: The number of milliseconds the specified statement waits for the lock to be released.

Syntax: Set lock_timeout timeout_period

Parameter: timeout_period is the number of milliseconds that have elapsed before Microsoft SQL Server Returns a locking error. When the value is-1 (default value), it indicates that there is no timeout period (that is, waiting indefinitely ).
When the lock wait value exceeds the timeout value, an error is returned. If the value is 0, the system does not wait, and information is returned when a lock is encountered.

Note: When the connection starts, the value of this setting is-1. After the settings are changed, the new settings remain valid for the rest of the connection time.
Set lock_timeout is set during execution or running, rather than during analysis.
The readpast lock prompt provides another method for the set option.

Permission: Set lock_timeout permission is granted to all users by default.

Example: In the following example, the lock timeout period is set to 1,800 Ms.

Set lock_timeout 1800
Go

 

3. @ lock_timeout

Note: The current lock timeout setting for the current session is returned, in milliseconds.

Syntax: @ lock_timeout

Return type: integer

Note: Set lock_timeout allows the application to set the maximum time for the statement to wait for resource blocking. When a statement has waited for more than the time set by lock_timeout, the locked statement is automatically canceled and an error message is returned 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 sets lock_timeout to 1800 milliseconds and then calls @ lock_timeout.

Set lock_timeout 1800
Select @ lock_timeout

The following is the result set:
------------------------------
1800

 

4. Set identity_insert

Note: explicit values can be inserted into the table's ID column.

Syntax: Set identity_insert [database. [owner.] {table} {on | off}

Parameter: database is the name of the database where the specified table resides.
The owner is the name of the table owner.
Table is the name of the table containing the ID column.

Note: at any time, the identity_insert attribute of only one table in the session can be set to on. If a table has set this attribute to on and sends a set identity_insert on statement to the other table, Microsoft SQL server returns an error message, indicates that set identity_insert has been set to on and reports that this attribute has been set to on.
If the inserted value is greater than the current table id value, SQL Server automatically uses the new value as the current ID value.
Set identity_insert is set during execution or running, rather than during analysis.

Permission: by default, the execution permission is granted to the SysAdmin fixed server role, db_owner, db_ddladmin fixed database role, and object owner.

Example: The following example creates a table with an ID column and shows how to use the set identity_insert setting to fill gaps in the ID values caused by the delete statement.

-- 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;
-- Shoshould 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

Note: Set the implicit transaction mode for the connection.

Syntax: Set implicit_transactions {on | off}

Note: When set to on, set implicit_transactions sets the connection to implicit transaction mode. When it is set to off, the connection is returned to the automatic commit transaction mode.
When the connection is in implicit transaction mode and is not currently in the transaction, execute the following statement to start the transaction:

Alter table fetch revoke
Create grant select
Delete Insert truncate table
Drop open update

If the connection is already in an opened transaction, the preceding statement does not start a new transaction.
For a transaction that is automatically opened because the transaction is set to on, You must explicitly commit or roll it back at the end of the transaction. Otherwise, when the user disconnects, the transaction and all its data changes will be rolled back. After the transaction is committed, execute any of the preceding statements to start the new transaction.
The implicit transaction mode remains valid until the connection executes the set implicit_transactions off statement to enable the connection to return to the automatic commit mode. In the automatic submission mode, if each statement is successfully completed, it is submitted.
During the connection, the SQL Server ODBC driver and the Microsoft ole db provider for SQL Server automatically set implicit_transactions to off. For connections from DB-Library applications, set implicit_transactions is off by default.
When set ansi_defaults is on, set implicit_transactions is enabled.
Set implicit_transactions is set during execution or running, rather than during analysis.

Example: The following example shows how to explicitly or implicitly start a transaction when implicit_transactions is set to on. It uses the @ trancount function to demonstrate opened transactions and closed transactions.

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' = @ trancount
Go

Select * from T1
Go

-- Need to commit this Tran too!
Drop table T1
Commit tran
Go

 

6. Set nocount
Note: The returned results do not contain information about the number of rows affected by the transact-SQL statement.

Syntax: Set nocount {on | off}

Note: When set nocount is on, no count is returned (indicating the number of rows affected by the transact-SQL statement ). When set nocount is off, return the count.
Even if set nocount is on, the @ rowcount function is updated.
When set nocount is on, the done_in_proc information of each statement in the stored procedure is not sent to the client. When you use a utility provided by Microsoft SQL server to execute a query) "Nn rows affected" is not displayed in the query results at the end ".
If some statements contained in the stored procedure do not return much actual data, this setting greatly reduces network traffic and significantly improves performance.
Set nocount is set during execution or runtime, rather than during analysis.

Permission: Set nocount permission is granted to all users by default.

Example: When executed in the osql utility or SQL Server Query analyzer, information about the affected rows is displayed.

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
Returns the number of rows affected by the previous statement.

Syntax: @ rowcount

Return type: integer

Note: any statement that does not return rows sets this variable to 0, such as the if statement.

Example: Execute the update statement in the following example and use @ rowcount to check whether there are any changed rows.

Update authors set au_lname = 'Jones'
Where au_id = '2017-888-7777'
If @ rowcount = 0
Print 'Warning: No rows were updated'

 

8. Set rowcount

Enables Microsoft SQL server to stop processing queries after returning the specified number of rows.

Syntax: Set rowcount {number | @ number_var}

Parameter: Number | @ number_var is the number of rows (integers) to be processed before the given query is stopped ).

Note: We recommend that you rewrite the delete, insert, and update statements using the set rowcount statement to use the top syntax. For more information, see Delete, insert, or update.
Ignore the set rowcount option settings for insert, update, and delete statements executed on remote tables and local and remote partition views.
To disable this option to return all rows, set rowcount to 0.
Setting the set rowcount option will stop most Transact-SQL statements from processing when they have been affected by a specified number of rows. This includes data modification statements such as triggers, insert, update, and delete. The rowcount option is invalid for dynamic cursors, But it limits the row set and does not perceive cursors of the key set. Exercise caution when using this option, which is mainly used with the SELECT statement.
If the number of rows is small, set rowcount replaces the top keyword of the SELECT statement.
Set rowcount is set during execution or running, rather than during analysis.

Permission: The set rowcount permission is granted to all users by default.

Example: Set rowcount stops processing after the specified number of rows. In the following example, note that row x meets the condition that the prepayment is less than or equal to $5,000. However, it can be seen from the number of rows returned by the update that not all rows are processed. Rowcount affects all Transact-SQL statements.

Use pubs
Go
Select count (*) as CNT
From titles
Where advance >=5000
Go

The following is the result set:

CNT
-----------
11

(1 row (s) affected)

Now, set rowcount to 4 and update all rows whose prepayment is equal to or greater than $5,000.

-- Set rowcount to 4.
Set rowcount 4
Go
Update titles
Sets advance = 5000
Where advance >=5000
Go

 

9. Set transaction isolation level
Control the default transaction lock behaviors of all Microsoft SQL Server select statements sent by connections.

Syntax: Set transaction isolation level
{Read committed
| Read uncommitted
| Repeatable read
| Serializable
}

Parameter: Read committed specifies to control the shared lock when reading data to avoid dirty reading, but the data can be changed before the transaction ends, resulting in non-repeated read or phantom data. This option is the default value of SQL Server.
Read uncommitted performs dirty read or 0-level isolation lock, which means no shared lock is issued or the exclusive lock is not accepted. When this option is set, uncommitted or dirty reads can be performed on the data. Before the transaction ends, the values in the data can be changed, and the rows can also appear in the dataset or disappear from the dataset. This option is used to set nolock for all tables in all statements in the transaction. This is the minimum limit among the four isolation levels.
Repeatable read locks all data used in the query to prevent other users from updating data. However, other users can insert new Phantom rows into the dataset and the phantom rows are included in subsequent reads of the current transaction. Because concurrency is lower than the default isolation level, this option should be used only when necessary.
Serializable places a range lock on the dataset to prevent other users from updating the dataset or inserting rows into the dataset before the transaction is completed. This is the most restrictive level among the four isolation levels. Because the concurrency level is low, this option should be used only when necessary. This option is used to set holdlock for all tables in all select statements in the transaction.

Note: Only one of these options can be set at a time, and the set options will remain valid for that connection until the option is explicitly changed. This is the default action, unless the optimization option is specified at the table level in the from clause of the statement.
SET transaction isolation level is set during execution or running, rather than during analysis.

Example: The following example sets the transaction isolation level for the session. For each subsequent Transact-SQL statement, SQL server controls all 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

 

10. Set xact_abort

Description: determines whether Microsoft SQL Server automatically rolls back the current transaction when a running error occurs in a Transact-SQL statement.

Syntax: Set xact_abort {on | off}

Note: When set xact_abort is on, if a running error occurs in a Transact-SQL statement, the entire transaction is terminated and rolled back. When it is off, only the wrong Transact-SQL statement is returned, and the transaction will continue to be processed. Compilation errors (such as syntax errors) are not affected by set xact_abort.
For most ole db providers (including SQL Server), xact_abort must be set to on for data modification statements in implicit or explicit transactions. The only case where this option is not required is when the Provider supports nested transactions. For more information, see distributed queries and distributed transactions.
Set xact_abort is set during execution or running, rather than during analysis.

Example: In the following example, a foreign key violation error occurs in transactions containing other Transact-SQL statements. Errors are generated in the first statement set, but other statements are successfully executed and the transaction is successfully committed. In the second statement set, set xact_abort to on. This causes a statement error to terminate the batch processing and roll back the transaction.

Create Table T1 (A int primary key)
Create Table T2 (A int references T1 ())
Go
Insert into T1 values (1)
Insert into T1 values (3)
Insert into T1 values (4)
Insert into T1 values (6)
Go
Set xact_abort off
Go
Begin tran
Insert into T2 values (1)
Insert into T2 values (2)/* foreign key Error */
Insert into T2 values (3)
Commit tran
Go

Set xact_abort on
Go

Begin tran
Insert into T2 values (4)
Insert into T2 values (5)/* foreign key Error */
Insert into T2 values (6)
Commit tran
Go

/* Select shows only keys 1 and 3 added.
Key 2 insert failed and was rolled back,
Xact_abort was off and rest of transaction
Succeeded.
Key 5 insert error with xact_abort on caused
All of the second transaction to roll back .*/

Select *
From T2
Go

Drop table T2
Drop table T1
Go