Informal discussion on identity columns in SQL Server (i)

Server
Informal discussion on identity columns in SQL Server (i)

I. Definition and characteristics of the identity column

The identity column in SQL Server, also known as an identifier column, is customarily called a self-added column.
The column has the following three features:

1. The data type of the column is a numeric type with no decimal number
2, in the Insert (insert) operation, the value of the column is generated by the system according to a certain law, do not allow null values
3. Column values are not duplicated, with the function of identifying each row in the table, each table can have only one identity column.

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

Ii. 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 should be noted that when you select decimal and numeric, the scale must be zero
Also pay attention to the range of values represented by each data type

2. Seeds (Seed)
is the value assigned to the first row in the table, and defaults to 1

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

Iii. creation and modification of identity columns
Identity column creation and modification, usually implemented in Enterprise Manager and Transact-SQL statements, easy to use Enterprise management Manager, refer to the online Help for SQL Server, which

We only discuss the use of Transact-SQL methods.

1. Specify an identity column when creating a table
The identity column can be established with the identity property, so in SQL Server, it is also known as a column or identity column with the IDENTITY property.
The following example creates an identity column with the name ID, the type int, the seed 1, and an increment of 1.
CREATE TABLE T_test
(ID int IDENTITY (1,1),
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 1 to table t_test.
--Create a table
CREATE TABLE T_test
(Name varchar (50)
)

--Inserting data
INSERT t_test (Name) VALUES (' John ')

--Adding identity columns
ALTER TABLE T_test
ADD ID int IDENTITY (1,1)

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 use:
Select objectproperty (object_id (' table name '), ' tablehasidentity ')
If there is, return 1, otherwise return 0

4, to determine whether a column is an identity column

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

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

5, the Identity column reference

If you refer to an identity column in an SQL statement, use the keyword IDENTITYCOL instead
For example, to query the row with 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:
SELECT ident_seed (' Table name ')

7. Get the increment of the identity column

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

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

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