SQL Server Identity column

I. Definition and characteristics of the identity column

The identity column in SQL Server is also called the identifier column, which is also known as the self-increment column.
This type of column has the following three characteristics :

1. The data type of the column is a numeric type without decimals
2, when inserting (insert) operation, the value of the column is generated by the system according to certain rules, do not allow null values
3. Column values are not duplicated and have the function of identifying each row in the table, and each table can have only one identity column.

Because of the above characteristics, the identity column is widely used in the design of database.

Second, the composition of the identity column
To create an identity column, you typically specify three content:
1. types (Type )
In SQL Server 2000, the identity column type must be a numeric type , as follows:
decimal, int, numeric, smallint, bigint, tinyint
It is important to note that when you select decimal and numeric, the number of decimal digits must be zero
Also note the range of values for all representations of each data type

2. seeds (seed )
is the value assigned to the first row in the table, which defaults to 1

3. Increment amount (increment)
The increment between the adjacent two identity values, which defaults to 1.

Third, the creation and modification of the identity column
Identity column creation and modification, usually in Enterprise Manager and with Transact-SQL statements can be implemented, using Enterprise Management Manager is relatively simple, please refer to the SQL Server online Help, which

Only discuss how to use Transact-SQL

1. Specify an identity column when creating a table
Identity columns can be established by using the IDENTITY property, so in SQL Server, the identity column is also referred to as the column or identity column that has the identity attribute.
The following example creates an identity column that contains the name ID, type int, seed 1, increment 1
CREATE TABLE T_test
(ID int IDENTITY (),
Name varchar (50)
)

2. Add an identity column to an existing table
The following example adds an identity column named ID, type int, seed 1, increment of 1 to table T_test
--Create a table
CREATE TABLE T_test
(Name varchar (50)
)

--Inserting data
INSERT t_test (Name) VALUES (' Zhang San ')

--Increase the identity column
ALTER TABLE T_test
ADD ID int IDENTITY (+)

3. Whether a table has an identity column

You can use the OBJECTPROPERTY function to determine whether a table has an identity (identity) column and uses:
Select objectproperty (object_id (' table name '), ' tablehasidentity ')
Returns 1 if any, otherwise returns 0

4. Determine if a column is an identity column

You can use the COLUMNPROPERTY function to determine whether a column has an identity property, and the usage
SELECT columnproperty (object_id (' table name '), ' column name ', ' isidentity ')
Returns 1 if the column is a label, otherwise 0

5. Query the column name of a table identity column
There are no ready-made functions in SQL Server to implement this functionality, and the SQL statements implemented are
SELECT column_name from Information_schema.columns
WHERE table_name= ' table name ' and ColumnProperty (
object_id (' table name '), column_name, ' isidentity ') =1

6. References to Identity columns

If the identity column is referenced in an SQL statement, the keyword IDENTITYCOL is used instead
For example, to query for rows with an ID equal to 1 in the previous example,
The following two query statements are equivalent
SELECT * from T_test WHERE identitycol=1
SELECT * from T_test WHERE id=1

7. Get the seed value of the identity column

You can use the function ident_seed, usage:
SELECT ident_seed (' Table name ')

8. Get increment of identity column

You can use the function ident_incr, usage:
SELECT ident_incr (' Table name ')

9. Gets the last generated identity value in the specified table

You can use the function ident_current, usage:
SELECT ident_current (' Table name ')
Note: When a table that contains an identity column has just been created, the value that is obtained by using the Ident_current function is the seed value of the identity column for any insert operation, especially when developing a database application.

Here's a summary of how identity columns are handled in replication

1. Snapshot replication
In snapshot replication, it is generally not necessary to consider the properties of the identity column.

2. Transactional replication
Example:
Publish database A, subscribe to Database B, the publication is T_test_a, and the subscription table is T_test_b
CREATE TABLE t_test_a
(ID int IDENTITY (),
Name varchar (50)
)
CREATE TABLE T_test_b
(ID int IDENTITY (),
Name varchar (50)
)

In this case, the replication agent will not be able to copy the new row to library B because the column ID is an identity column and cannot provide a value to the identity column display, and replication fails.
At this point, you need to set the not for REPLICATION option for the identity column. This way, when the replication agent connects to table T_test on library B with any login, all not on that table
The For REPLICATION option is activated so that you can explicitly insert an ID column.

Here are two things:
1, the T_test table of library B will not be updated by the user (or application)
In the simplest case, if the t_test of library B is not updated by the user (or application), it is recommended that the identity attribute of the ID column be removed and only the simple int type be used.

2, the T_test table of library B is updated by other users (or applications)

In this case, the ID column of the two t_test table will conflict, for example:
Execute the following statement in library A:
INSERT t_test_a (Name) VALUES (' Tom ') (assuming ID column 1)
Execute the following statement in Library B:
INSERT t_test_b (Name) VALUES (' Pip ') (assuming ID column 1)
In this way, a record is inserted into the two tables of library A and library B, apparently two different records.
However, it was not over until the pre-set replication time, the replication agent attempted to insert the record "1 TOM" into the T_test table in library B, but the T_test_b table for library B already exists

The column with ID 1, insert will not succeed, through Replication Monitor, we will find that replication has failed.
the methods to solve the above problems are :
(1) Specify a different range of values for the Publisher and subscriber's identity columns, as the previous example can be modified to:
--Ensure that the table record does not exceed 10000000
CREATE TABLE t_test_a
(ID int IDENTITY (),
Name varchar (50)
)
CREATE TABLE T_test_b
(ID int IDENTITY (10000000,1),
Name varchar (50)
)
(2) The value of the identity column of the publisher and the Subscriber is not duplicated, as
--Using odd values
CREATE TABLE t_test_a
(ID int IDENTITY),
Name varchar (50)
)
--Using even values
CREATE TABLE T_test_b
(ID int IDENTITY (2,2),
Name varchar (50)
)
This approach can be generalized, and when subscribers and publishers are everywhere, the definition of identity column properties is as follows
(1,4), (2,4), (3,4), (BIS)

3. Merge replication
Use transactional replication as a workaround, as long as the values of the publication table and the subscription table identity column are not duplicated.

1. When you create a table, the Identity column identity column can be created using the IDENTITY property, so in SQL Server, it is also known as the column or identity column that has the identity attribute. 2. Add an identity column to an existing table The following example adds an identity column named ID, type int, seed 1, increment 1 to table t_test--Create TABLE T_test (Name varchar (50))

SQL Server Identity column