Mark column (auto-increment column) Summary

SQL ID column
I. Definition and features of an ID column

The ID column in SQL Server, also known as the identifier column, is also called an auto-incrementing column.
This type of column has the following three features:

1. The column data type is Numeric without decimal places.
2. During the insert operation, the value of this column is generated by the system according to certain rules, and null values are not allowed.
3. The column values are unique and can identify each row in the table. Each table can only have one column.

Because of the above features, the logo column is widely used in the database design.

Ii. Composition of ID Columns
To create an ID column, you must specify three items:
1. Type)
In SQL Server 2000, the column type must be numeric, as shown below:
Decimal, Int, numeric, smallint, bigint, tinyint
Note that when decimal and numeric are selected, the number of decimal places must be zero.
In addition, pay attention to the value ranges of all values in each data type.

2. Seed)
Is the value assigned to the first row in the table. The default value is 1.

3. incremental (increment)
Increment between two adjacent ID values. The default value is 1.

3. Create and modify an ID column
The creation and modification of the ID column can be achieved in the Enterprise Manager and by using the transact-SQL statement. It is relatively simple to use the enterprise management manager. Please refer to the online help of SQL Server.

Only the methods for using Transact-SQL are discussed.

1. Specify the ID column when creating a table
The identity column can be created using the identity attribute. Therefore, in SQL Server, the identity column is also called the column with the identity attribute or the identity column.
The following example creates an ID column with the name ID, int type, 1 seed, and 1 increment.
Create Table t_test
(ID int identity (1, 1 ),
Name varchar (50)
)

2. Add an ID column to an existing table
The following example adds an ID column named "ID" to the t_test table, whose type is int, whose seed is 1, and the increment is 1.
-- Create a table
Create Table t_test
(Name varchar (50)
)

-- Insert data
Insert t_test (name) values ('zhang san ')

-- Adds an ID column.
Alter table t_test
Add ID int identity (1, 1)

3. Determine whether a table in a segment has an ID column

You can use the objectproperty function to determine whether a table has an identity column. Usage:
Select objectproperty (object_id ('table name'), 'tablehasauth ')
If yes, 1 is returned; otherwise, 0 is returned.

4. Determine whether a column is an ID column.

You can use the columnproperty function to determine whether a column has the identity attribute.
Select columnproperty (object_id ('table name'), 'column name', 'isidentity ')
If this column is an identifier column, 1 is returned; otherwise, 0 is returned.

4. query the column names of an identity column in a table.
SQL Server does not have ready-made functions to implement this function. The SQL statements implemented are as follows:
Select column_name from information_schema.columns
Where table_name = 'table name' and columnproperty (
Object_id ('table name'), column_name, 'isidentity ') = 1

5. Identify column reference

If you reference the ID column in an SQL statement, use the keyword identitycol instead.
For example, to query the rows in the previous example with the ID equal to 1,
The following two query statements are equivalent.
Select * From t_test where identitycol = 1
Select * From t_test where id = 1

6. Obtain the seed value of the ID column

You can use the ident_seed function. Usage:
Select ident_seed ('table name ')

7. Get the increment of the ID column

You can use the ident_incr function. Usage:
Select ident_incr ('table name ')

8. Obtain the last generated id value in the specified table.

You can use the ident_current function. Usage:
Select ident_current ('table name ')
Note: When the table that contains the ID column is just created and any insert operation is performed, the value obtained by using the ident_current function is the seed value of the ID column, pay special attention to this when developing database applications.