Primary Key for automatic SQL Growth

Q: How can I obtain an automatic number field when I configure a table in SQL Server 2005 and in SQL Server Management studio Express?

A: The procedure is as follows:

① Automatically numbered field in access

Right-click your table --> design table --> Find your ID field (INT type) --> id --> yes --> ID seed (initial value) --> incremental id --> OK

② Use the identity (seed, increment) parameter

Seed-start value

Increment-Increment

Create Table Name (

Your ID identity (1, 1) not null, your other fields ...)

Create Table Name (

Your field ID autoincrement (), other fields ...)

③ Modify the start value and step value

Alter table table name alter column your field ID counter (2000,50)

④ Let the field's start value be automatically added to a deleted table and start from 1 again

Alter table table name alter column your field ID counter (1, 1)

The preceding 3 and 4 are applicable only to access. Counter is a data type. You can specify a field not automatically numbered as an automatically numbered field in access, you can also set an automatic number field to be automatically numbered from the specified value according to the specified step size. However, if the table contains data, you cannot use this statement to change the data type of this column to counter. SQL Server is not supported.

For SQL Server, we may always want to use the ALTER TABLE table name alter column. Your field identity)

To specify the field to start counting again from 1, but this sentence itself is incorrect. For a long time, I wonder why this sentence cannot be executed. If we look at the definition of the alter table statement in MS, this statement is completely incorrect. The following is the definition of the alter table statement by Ms.

 

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} [, 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)
}

 

We can see that identity is only in <column_definition>, that is, we can use

Alter table table name add field name int identity (1, 1)

That is, we can add a field and specify it as an automatically numbered field. However, you cannot change a field to an automatically numbered field (or perhaps I have not found a method ). That is, if we want to add an automatic number field to a table, we can only use the method of adding a field, but cannot change an existing field to an automatic number field.

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

DBCC checkident (table, reseed, 100)

The value starts from 101 next to the automatically numbered field.