Identity column in SQL Server (self-growing field)

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

4. 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

5. 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

6. Get the seed value of the identity column

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

7. Get increment of identity column

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

8. 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.

9.[sql Server] about the identity column starting from 1 counting problem
In SQL Server, we sometimes need to re-add records after emptying the data table, and the identity column starts counting again from 1.
We just need to execute the following command before inserting the record:
DBCC checkident (table name, reseed, 0)
Execution of TRUNCATE TABLE can also be done, and high efficiency because:

Using Delete will record each operation to the log, so the efficiency is low, TRUNCATE TABLE is one time, the efficiency is much faster.
However, Truncate table has restrictions, such as that the identity column is a foreign key to another table, and the identity column is currently 1, and the Insert row starts at 2.

While DBCC CHECKIDENT (table name, reseed, 0) can be used even if there are foreign keys in the case.

Identity column in SQL Server (self-growing field)