Talk to the identity column in SQL Server (1)
First, the identity of the definition and characteristics
The identity column in SQL Server is also known as the identifier column, and it is accustomed to the column. This spectrum has the following three characteristics:
1, the data type of the column is a numerical type 2 without the decimal, and the value of the column is generated by the system, and the null value is not allowed, the column value is not repeated, and has the identification table. The role of each line, each table can only have one identity column.
Due to the above characteristics, the identity is widely used in the design of the database.
Second, the composition of the identity column creates a identity column, usually to specify three contents: 1. Type (TYPE) In SQL Server 2000, the identity column type must be numeric type, as follows: Decimal, int, Numeric, Smallint, Bigint, Tinyint It should be noted that when Decimal and Numeric are selected, the number of decimal numbers must pay attention to the number of all indicated numerical ranges for each data type.
2, seed (seed) is assigned to the value of the first line in the table, default is 1
3. Increment is increment between the two identification values, the default is 1.
Third, the creation and modification of the identity of the identity column is usually implemented in the Enterprise Manager and the Transact-SQL statement. If you use the Enterprise Management Manager, please refer to SQL Server's online help, this
Only discuss how to use Transact-SQL
1. When you create a table, specify the identity column identity column can be established with the Identity property, so in SQL Server, it is also known as a column or Identity column with an identity property. The following example creates a labeled column containing named ID, type INT, seed as 1, increasing increasing 1, Name VARCHAR (50))
2, adding a list of identity columns below existing table to add a name ID, type Int, type 1, the seed is 1, the increment is 1 identity column - Create Table Create Table T_test (Name Varchar (50 ))
- Insert Data INSERT T_TEST (NAME) VALUES ('Zhang 3')
- Increase Label Alter Table T_TestAdd ID Int Idnessity (1, 1)
3. Decorate whether a table has identification columns
You can use the ObjectProperty function to determine if a table has an Identity (Identity) column, usage: select ObjectProperty (Object_ID ('table name),' TableHasident ') If there is, return 1, otherwise returns 0
4. Judgment whether a column is identity
You can use the ColumnProperty function to determine if a column has an Identity property, usage select columnproperty (Object_ID ('table name),' column name ',' isidentity ') If the list is identity, return 1, otherwise returns 0
4. Query the list of SQL Server in a table identity column implementation This feature is implemented, the SQL statement implemented, the SQL statement is as follows Select Column_name from information_schema.columns where table_name = 'table name' and colornproperty (Object_ID ('Name'), Column_name, 'isidentity') = 1
5, identity column reference
If you reference the identity column in the SQL statement, you can use the keyword IdentityCol, for example, if you want to query the ID equal to 1 in the above example, the following two query statements are the equivalent SELECT * from t_test where IdentityCol = 1select * from t_test where id = 16, get the seed value of the identity column
You can use function Ident_seed, usage: select Ident_seed ('Table ")
7, get the increment of the identity column
Can use function Ident_inCr, usage: select Ident_inCr ('Name ")
8, get the final generated identity value in the specified table
You can use the function ident_current, usage: select Ident_Current ('Name') Note: When the table containing the identity column just creates, use the value of the identity_current function with the value of the identity of the seed value of the marked column, this is Especially when developing database applications.
