To create a modified SQL Server identity column (that is, the autogrow column) by using the SQL statement

Original: Use SQL statement to create a modified SQL Server identity column (that is, autogrow column)

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.


Using SQL statements to modify an identity column to create a modified SQL <wbr>server identity column using SQL statements (that is, the autogrow column)
--copying data to a temporary table
SELECT * to #aclist from aclist

--Delete data table
drop table Aclist

--Create a data table (and set the identity column)
CREATE TABLE aclist (ID int identity (), [date] datetime,version nvarchar (6), [class] nvarchar (+), Actitle nvarchar (50) , Acdetail nvarchar (max), author nvarchar (50))

--Set the identity column to allow insertion
SET Identity_insert aclist on

--transfer data from a temporary table.
Insert into Aclist (Id,[date],version,[class],actitle,acdetail,author)
Select Id,[date],version,[class],actitle,acdetail,author from #aclist

--Turn off identity column insertion
SET Identity_insert aclist off

--Forces the starting value of the identity column to be set:
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 a data table without autogrow
CREATE TABLE [Tbmessage] (
[ID] [decimal] (18, 0),
[Msg_content] [varchar] (max) Null
) on [PRIMARY]

GO
----Inserting test data
insert INTO [Tbmessage] ([id],[msg_content])
VALUES (20, ' You know? ‘)

insert INTO [Tbmessage] ([id],[msg_content])
VALUES (21, ' You know? 201 ')
Go
--View data
---select * from Tbmessage

--Insert temporary table
SELECT * into #tbMessage from [Tbmessage]
Go
--Delete table data
Delete [Tbmessage]
Go

--delete Field ID
ALTER TABLE [tbmessage] Drop column [ID]
---Increase the ID auto-grow field
ALTER TABLE [tbmessage] add [id] int identity (+)

SET IDENTITY_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 insertion
SET IDENTITY_INSERT [Tbmessage] Off

---delete temporary tables
drop table #tbMessage
--------------------------------------------------
/*
drop table Tbmessage
---------------detect if the autogrow field is normal----------
----Get 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 is growing normally
SELECT * FROM Tbmessage

*/

To create a modified SQL Server identity column (that is, the autogrow column) by using the SQL statement