IDENTITY related functions, commands, system variables, catalog views

In the previous posts we have examined IDENTITY columns in detail. We discussed several IDENTITY related functions, commands, system variables and terms. This post intends to summarize them.
IDENTITY Property
IDENTITY is an attribute that you can assign to an integer column in SQL Server. It is NOT a DATA TYPE as many people think. It is just an attribute that instructs SQL Server to generate a new value every time a row is inserted. IDENTITY property is different from IDENTITY() function and this post explains them both.
SEED value
Every IDENTITY column has a SEED value/attribute. The IDENTITY value starts with the SEED value. If you create an IDENTITY column with a SEED value of 1000, the first IDENTITY value generated will be 1000. The default value of an IDENTITY SEED is 1. If you have not specified a SEED value, SQL Server will assume 1 as the SEED value and the first IDENTITY value generated will be 1.
INCREMENT value
Every IDENTITY column has an INCREMENT value/attribute. The INCREMENT value is used to generate the next IDENTITY value. New IDENTITY value is generated by adding INCREMENT to the PREVIOUS IDENTITY value. The default value of INCREMENT is 1, but you can change it. To generate ODD numbers (1, 3, 5 etc), set SEED to 1 and INCREMENT to 2. To generate EVEN numbers (0, 2, 4 etc), set SEED to 0 and INCREMENT to 2.
@@IDENTITY System Variable
@@IDENTITY is a system variable that holds the last IDENTITY value generated in the current session for any table. Note that it does not necessarily give you the IDENTITY value generated by the INSERT statement you just fired. If you try to insert a row into TableA and if the table has a trigger that performs an insert into TableB, @@IDENTITY will return the value generated for TableB; not TableA. This is explained in this post.
SCOPE_IDENTITY() Function
SCOPE_IDENTITY() returns the last IDENTITY value generated in the current scope. Let us revisit the example I wrote earlier. If you try to insert a row into TableA and if the table has a trigger that performs an inserts into TableB, SCOPE_IDENTITY() will return the IDENTITY value generated for Table A. See this post for more information.
IDENT_CURRENT() Function
IDENT_CURRENT() returns the last generated IDENTITY value of a given table. You can use this function to find the current IDENTITY value of tables. See this post for an example.
IDENT_SEED() Function
IDENT_SEED() returns the IDENTITY SEED value of an IDENTITY column. See this post for an example.
IDENT_INCR() Function
IDENT_INCR() function returns the IDENTITY INCREMENT value of an IDENTITY column. See this post for an example.
IDENTITY() Function
IDENTITY() function can be used to generate a sequence number in a SELECT query. This function can be used only in an INSERT INTO .... SELECT.. type of query. You can pass the Data Type, IDENTITY SEED and INCREMENT values to the function and it generates IDENTITY values accordingly. See this post for more details.
DBCC CHECKIDENT Command
DBCC CHECKIDENT() can be used to query the current IDENTITY value of a table or to change the SEED and INCREMENT attributes of an IDENTITY column. See this post for a demonstration.
SET IDENTITY_INSERT ON|OFF Command
SET IDENTITY_INSERT ON can be used to insert explicit values to an IDENTITY column. When IDENTITY_INSERT is set to ON, you MUST provide an explicit IDENTITY value, else the insert operation will fail. Only one table at a time (in a single session) can have IDENTITY_INSERT set to ON. See this post for more details.
is_identity column in sys.columns
System catalog view sys.columns lists all the columns in all the tables of the current database. The is_identity column of sys.columns can be used find out the IDENTITY column of a table. See this post for an example.
sys.identity_columns Catalog View
sys.identity_column lists all the identity columns in all the tables of the current database. It exposes the following attributes of an IDENTITY column.

• seed_value - Stores the SEED value of an IDENTITY column
• increment_value - Stores the INCREMENT value of an IDENTITY column
• last_value - Stores the Last IDENTITY value generated for the column.

Finding out who dropped a table using the transaction log

a particular table was dropped plus the UID and SPID of who dropped it.

Drop:

SELECT  [Transaction Id], [Transaction Name],[Begin Time], [SPID] ,[TRansaction SID] FROM ::fn_dblog (NULL, NULL) WHERE


user_transaction
DROPOBJ
CREATE INDEX
FirstPage Alloc
INSERT
NULL
UpdateQPStats
Allocate Root
SplitPage
CREATE TABLE
StartupDB [Transaction Name] = 'DROPOBJ'

Database Timeout Settings

Database Timeout Settings

The first place that you need to check is your database server, to see if the server accepts long running queries/procedures. You can do this by checking the properties of the database. If you are using enterprise manager right click on the server name and select properties.

By changing the query timeout, you can make sure that the database server can tolerate a prolonged query execution as configured.

Connection Timeout

The next setting that needs to be checked is the connection timeout. Connection timeouts can be configured either specifying the "timeout" value in the connection string or by explicitly modifying property of SqlConnection object.

The following example shows how to specify a connection timeout in the connection string.

data source=myServer;initial catalog=pubs;UID=sa;PWD=sa;timeout=6000

The above connection string specifies a timeout value of 6000 seconds.

If you do not prefer to set the timeout in the connection string, you can set the value directly to the ConnectionTimeout property of SQLConnection object.

This has been a cause of major misunderstanding with most people. I had been, and many other people might have been considering this setting as the query timeout. Many times in the past I had been wondering why my queries get a timeout, eventhough I have a longer value set in the connection string, as well as in the database timeout. But it was a wrong perception. The timeout value that you set to a connection string has got nothing to do with the query execution. This value is used to specify how much time the object has to wait while attempting to establish a connection to the server.

To execute a query or procedure we need a two step process.

1. Connect to the data source
2. Execute the SQL command.

The timeout value above controls #1. It specifies how much time the connection object has to wait when attempting to establish a connection to the database server.
So the question is "how do I control my query timeout then?" Well, thats where we need CommandTimeout property of the SQLCommand object.

Command Timeout
Command execution (query, stored procedure etc) can be controlled by assigning the timeout value to CommandTimeout proeprty of SQLCommand object. The default timeout is 30 seconds. Some of you might have experienced that your queries are getting a timeout after 30 seconds.
SQLConnection allows you to specify a timeout value in the web.config, however SQLCommand does not allow that. You will have to explicitly specify the timeout value before the command is executed. However you can crate a custom configuration key and set the value in the configuration file, then before the command is executed read the value from the configuration file and assign to the timeout property. This way, you can globally controll the timeout settings without the need to scan the entire source code and change the timeout settings.