Identity columns in SQL Server

definition of identity column and features the identity column in SQL Server is also called the identifier column, which is also called self-increment. This type of column has the following three characteristics:1, the data type of the column is a numeric type without decimals2, the value of the column is generated by the system on a regular basis and does not allow null values when inserting (insert) operations3, column values are not duplicated and have the effect 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. The composition of the identity column creates an identity column, typically specifying 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 decimal and numeric are selected, the number of decimal digits must be zero also note the range of values for all representations of each data type2, Seed (seed) is the value assigned to the first row in the table, which defaults to 13, increment (increment) the increment between adjacent two identity values, which defaults to 1. Third, the creation and modification of the identity column, usually in Enterprise Manager and with Transact-SQL statements can be implemented, using Enterprise Management Manager is relatively simple, please refer to the online Help for SQL Server, this is only to discuss the use of Transact-Methods of SQL1, when you create a table, the Identity column identity column can be established by identifying columns, so in SQL Server, it is also known 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 of 1, create TABLE t_test (IDintIDENTITY (1,1), Name varchar ( -))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 TABLE T_test (Name varchar ( -))--inserting data Insert T_test (Name) VALUES ('Zhang San')--add an identity column alter TABLE T_testadd IDintIDENTITY (1,1)3and a sentence a table has an identity column you can use the OBJECTPROPERTY function to determine whether a table has an identity (identity) column, usage: Select objectproperty (object_id ('Table name'),'tablehasidentity'If there is, return 1, otherwise return 04, determine if a column is an identity column use the COLUMNPROPERTY function to determine if a column has an IDENTITY property, using Select ColumnProperty (object_id ('Table name'),'Column Name','isidentity'returns 1 if the column is an identity, otherwise 04, query the column name of a table identity column SQL Server does not have an out-of-the-box function to implement this functionality, and the SQL statements implemented are as follows select column_name from Information_schema.columnswhere table_ NAME='Table name'and ColumnProperty (object_id ('Table name'), COLUMN_NAME,'isidentity')=15, identity column reference if you reference an identity column in an SQL statement, use the keyword IDENTITYCOL instead for example, to query the row with the ID equal to 1 in the previous example, the following two query statements are equivalent select* FROM T_test WHERE identitycol=1SELECT* FROM T_test WHERE id=16, get the seed value of the identity column can use the function ident_seed, usage: SELECT ident_seed ('Table name')7, get increment of identity column can use function IDENT_INCR, usage: SELECT ident_incr ('Table name')8, get the last generated identity value in the specified table using 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. Using SQL statements to modify an identity column to create a modified SQL using SQL statements<wbr>Server Identity column (that is, autogrow column)--copying data to a staging tableSelect* Into #aclist fromaclist--Delete data table drop table Aclist--Create a data table (and set the identity column) creation table aclist (IDintIdentity1,1), [date] datetime,version nvarchar (6),[class] nvarchar (Ten), Actitle nvarchar ( -), Acdetail nvarchar (max), author nvarchar ( -))--setting an identity column allows insertingSetidentity_insert aclist on--transfer data from temporary tables insert into aclist (id,[date],version,[class],actitle,acdetail,author)Selectid,[date],version,[class],actitle,acdetail,author from#aclist--Turn off identity column insertionSetIdentity_insert aclist off--force set the starting value for the identity column: DBCC checkident (table name, Reseed,1) --forces the identity value to start at 1.----------------Modify the original field, do not delete the table, directly modify the fields in the table, delete the data after processing. ---CREATE table with no autogrow data creation table [Tbmessage] ([id] [decimal]( -,0), [msg_content] [varchar] (max) NULL) on [Primary]go----inserting test data insert INTO [Tbmessage] ([id],[msg_content]) VALUES ( -,'you know what? 'INSERT INTO [Tbmessage] ([id],[msg_content]) VALUES ( +,'you know what? 201') Go--View Data---Select* fromTbmessage--inserting temporary tablesSelect* Into #tbMessage from[Tbmessage]go--Delete table Data delete [Tbmessage]go--Delete field Idalter table [tbmessage] Drop column [ID]---Add ID auto grow field ALTER TABLE [Tbmessage] add [id]intIdentity1,1)SetIdentity_insert [Tbmessage] on--transfer data from a temporary table insert into [Tbmessage] ([Msg_content],[id])Select[Msg_content],[id] from#tbMessage--Turn off identity column insertionSetIdentity_insert [tbmessage] off---Delete temporary table drop table #tbMessage--------------------------------------------------/*drop table Tbmessage---------------detect if the autogrow field is normal--------------get the seed data Select Ident_seed (' [tbmessage] ')---drop Table Tbmessage---Inserting two data insert INTO [Tbmessage] ([msg_content]) VALUES (' You know 20111 ') insert INTO [Tbmessage] ([msg_ Content]) VALUES (' You know 20112 ')---See if this id, normal growth select * from Tbmessage*/

Identity columns in SQL Server