An in-depth analysis of SQL self-growth settings and deletions _mysql

Identity (1,1) means that the value of this field is increased by 1 each time a statement is inserted,
Grammar
IDENTITY [(seed, increment)]
Parameters
Seed
The value used by the first row loaded into the table (indicating the seed).
Increment
The increment value that is added to the identity value of the previous loaded row (the markup increment).
Both the seed and increment must be specified, or neither is specified. If neither is specified, the default value (1,1) is taken.
For example:
CREATE TABLE Workroom
(workroomid int NOT null identity (1,1) primary key,
Workroomname Varchar (m) NULL,
TeacherName Varchar (Ten) NULL,
Workroommanager Varchar (Ten) NULL,
Workroomplan Varchar (max) NULL,
Purl Varchar (m) NULL,
Remarks Varchar (max) NULL,
)
Modify S to create a mark and delete the mark:
ALTER TABLE tablename DROP COLUMN ID
ALTER TABLE tablename ADD ID int identity (1,1) NOT NULL

How to remove the column's self-growth (identity) with SQL statements
========================================
How to remove the column's self-growth (identity) with an SQL statement, and unmark the command:
* * Cannot change existing self-added field to non-self-increase by alter
For example, ALTER TABLE A alter ID int, the self added attribute does not remove
By modifying the system table can be done (this method may have unpredictable results, cautious ...)
sp_configure ' allow updates ', 1
Go
Reconfigure with override
Go
Update syscolumns Set colstat = Colstat & 0x0000
where Id=o B J e C t_id (' Table name ') and name= ' field name '
Go
sp_configure ' allow updates ', 0
---------------------------------
--A compromise approach
ALTER TABLE a add XXX int
Update a set Xxx=id
ALTER TABLE a drop column ID
exec sp_rename ' xxx ', ' id ', ' column '
========================================
The first method can be removed, but later may cause problems, suggest a second method, I use the statement is now posted, may need a friend some useful
Delete the Zx_user property method for the field UserID in table:
New temporary field
ALTER TABLE zx_user add useridtmp int null
assigning values
Update Zx_user set useridtmp =userid
Delete primary key
ALTER TABLE Zx_user drop Pk_zx_user
Delete a field
ALTER TABLE zx_user drop column UserID
Add Field
ALTER TABLE zx_user add userid int null
Restore the value back
Update Zx_user Set UserID = useridtmp
Delete temporary fields
ALTER TABLE zx_user drop column useridtmp
Modifying a new field is not empty
ALTER TABLE zx_user ALTER COLUMN userid int NOT NULL
Jian primary key, multiple fields key, separated by commas, such as primary key (USERID,XXXXXX)
ALTER TABLE Zx_user add primary key (UserID)
========================================
Delete primary key, do not know your primary key name, please use the method query:
SELECT * from Syso B J e c ts where xtype= ' PK