Automatically get number fields in SQL Server

① an AutoNumber field like access

Right click on your table--> design Table--> Find your ID field (class int type)--> identity--> is--> identity seed (initial value)--> identity Increment-->ok

② identity (seed,increment) parameters

Seed-Starting value

Increment-Increment

CREATE Table Table name (

Your ID IDENTITY (1, 1) is not NULL, your other fields ...)

CREATE Table Table name (

Your field ID AutoIncrement (1000,10), other fields ...)

③ modify starting and stepping values

ALTER TABLE name ALTER COLUMN your field ID COUNTER (2000,50)

④ let an empty table automatically increase the start value of the field again starting from 1

ALTER TABLE name ALTER COLUMN your field ID COUNTER (1,1)

The above 3 4 applies only to Access,counter for one of its data types, you can specify in Access that an AutoNumber field is an AutoNumber field, or you can have an AutoNumber field be automatically numbered from the specified value at the specified step. However, if there is data in the table, the user cannot use the statement to change the column's data type to the counter data type. Not supported for SQL Server.

For SQL Server, we may always want to use Alter Column, your field IDENTITY (1,1), with ALTER TABLE name

To specify that the field be counted again starting at 1, but this sentence itself is wrong, for a long time I also wondered why this sentence could not be carried out. If we look at the definition of the ALTER TABLE statement by MS, the sentence is simply wrong. The following is the definition of MS for ALTER TABLE statements.

ALTER Table Table

{[ALTER COLUMN column_name

{New_data_type [(precision [, scale])]

[COLLATE < collation_name >]

[NULL | Not NULL]

| {ADD | DROP} ROWGUIDCOL}

]

| ADD

{[< column_definition >]

| column_name as Computed_column_expression

} [,... N]

| [With CHECK | With NOCHECK] ADD

{< Table_constraint >} [,... N]

| DROP

{[CONSTRAINT] Constraint_name

| Column column} [,... n]

| {CHECK | NOCHECK} CONSTRAINT

{all | constraint_name [,... n]}

| {ENABLE | DISABLE} TRIGGER

{all | trigger_name [,... n]}

}

< Column_definition >:: =

{column_name Data_type}

[[DEFAULT constant_expression] [with VALUES]

| [IDENTITY [(Seed, increment) [Not for REPLICATION]]]

]

[ROWGUIDCOL]

[COLLATE < collation_name >]

[< Column_constraint >] [... n]

< Column_constraint >:: =

[CONSTRAINT Constraint_name]

{[NULL | Not NULL]

| [{PRIMARY KEY | UNIQUE}

[CLUSTERED | Nonclustered]

[With FILLFACTOR = FILLFACTOR]

[ON {filegroup | DEFAULT}]

]

| [[FOREIGN KEY]

REFERENCES ref_table [(Ref_column)]

[on DELETE {CASCADE | NO ACTION}]

[on UPDATE {CASCADE | NO ACTION}]

[Not for REPLICATION]

]

| CHECK [Not for REPLICATION]

(Logical_Expression)

}

< Table_constraint >:: =

[CONSTRAINT Constraint_name]

{[{PRIMARY KEY | UNIQUE}

[CLUSTERED | Nonclustered]

{(column [,... n])}

[With FILLFACTOR = FILLFACTOR]

[ON {filegroup | DEFAULT}]

]

| FOREIGN KEY

[(column [,... n])]

REFERENCES ref_table [(Ref_column [,... n])]

[on DELETE {CASCADE | NO ACTION}]

[on UPDATE {CASCADE | NO ACTION}]

[Not for REPLICATION]

| DEFAULT constant_expression

[For column] [With VALUES]

| CHECK [Not for REPLICATION]

(search_conditions)

}

As you can see, identity is only in < Column_definition >, that is to say, we can use

Alter table name Add field name Int IDENTITY (1,1)

That is, we can add a field and specify it as an AutoNumber field. But you can't change a field to an AutoNumber field (or maybe I didn't find a way). That is, if we want to add an AutoNumber field to a table, you can only use the method that adds the field, not the AutoNumber field.

If you need to change the AutoNumber field count start value, you can use the DBCC command:

DBCC checkident (table name, reseed,100)

The AutoNumber field is next starting at 101.