Several setting options frequently used when using SQL Server

SQL Server Query SQL Execution time,

 

Enter the following content in the query Analyzer:

Set statistics profile on
Set statistics Io on
Set statistics time on
Go
<Write your statement here...>
Go
Set statistics profile off
Set statistics Io off
Set statistics time off

 

Check the SQL statement in the project and find something different from your imagination. The SQL statement:

Select min (ID) from

(Select top 25 ID from tblinfoWhereCharcityfrom = 'shijiazhuang'Order by id desc) as temp

 

1 scan count, 9 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.
CPU time = 0 ms, occupied time = 2 ms.

 

 

 

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 best choice deadlock victim. 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 at all, and information is returned as soon as the lock is met.

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 all times, only the identity_insert attribute of 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: The execution permission is granted to the SysAdmin fixed server role and db_owner and db_ddladmin fixed database role and object owner by default.

Example: In the following example, create a table with an ID column and show how to use the set identity_insert setting to fill the 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. He 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: All statements that do not return rows set 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 = 999-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, we can see that not all rows are processed from the number of rows returned by the update. 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)

Currently, 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 exclusive lock is 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 current 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 program 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 that contain 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