Shulou(Shulou.com)05/31 Report--

SQLServer2000 how to get automatic numbering field, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

How to get the automatic numbering field when designing tables in SQLServer2000

① is like an automatic numbering field in Access

Right-click your table-- > Design Table-- > find your id field (type int)-- > identity-- > Yes-- > identify seed (initial value)-- > identify incremental-- > OK.

IDENTITY (SEED,INCREMENT) parameter for ②

Seed- start value

Increment- increment

CREATETABLE table name (

Your IDIDENTITY (1d1) NOTNULL, your other fields...)

CREATETABLE table name (

Your field IDAUTOINCREMENT (1000510), other fields...)

③ modifies the start value and step value

ALTERTABLE table name ALTERCOLUMN your field IDCOUNTER (2000 dint 50)

④ makes a deleted table automatically increase the starting value of the field from 1 again.

ALTERTABLE table name ALTERCOLUMN your field IDCOUNTER (1dint 1)

The above 34 is only applicable to Access,COUNTER as a data type. A field that is not automatically numbered can be designated as an automatic numbering field in Access, or an automatic numbering field can be automatically numbered again from the specified value according to the specified step. However, if there is data in the table, the user cannot use this statement to change the data type of the column to the COUNTER data type. Not supported for SQLServer.

For SQLServer, we may always want to use the AlterTable table name to AlterColumn your field IDENTITY (1d1)

To specify that the field should be counted again from 1, but the sentence itself is wrong, and for a long time I wondered why it could not be executed. If we look at the definition of the AlterTable statement in MS, it is clear that this statement is fundamentally wrong. Here is the definition of the AlterTable statement by MS.

How to get the automatic numbering field when designing tables in SQLServer2000

The following is the referenced content:

ALTERTABLEtable

{[ALTERCOLUMNcolumn_name

{new_data_type [(precision [, scale])]

[COLLATE]

[NULL | NOTNULL]

| | {ADD | DROP} ROWGUIDCOL} |

]

| | ADD |

{[]

| | column_nameAScomputed_column_expression |

} [,... n]

| | [WITHCHECK | WITHNOCHECK] ADD |

{} [,... n]

| | DROP |

{[CONSTRAINT] constraint_name

| | COLUMNcolumn} [,... n] |

| | {CHECK | NOCHECK} CONSTRAINT |

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

| | {ENABLE | DISABLE} TRIGGER |

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

}

:: =

{column_namedata_type}

[[DEFAULTconstant_expression] [WITHVALUES]

| | [IDENTITY [(seed,increment) [NOTFORREPLICATION] |

]

[ROWGUIDCOL]

[COLLATE]

[] [... n]

:: =

[CONSTRAINTconstraint_name]

{[NULL | NOTNULL]

| | [{PRIMARYKEY | UNIQUE} |

[CLUSTERED | NONCLUSTERED]

[WITHFILLFACTOR=fillfactor]

[ON {filegroup | DEFAULT}]

]

| | [[FOREIGNKEY] |

REFERENCESref_table [(ref_column)]

[ONDELETE {CASCADE | NOACTION}]

[ONUPDATE {CASCADE | NOACTION}]

[NOTFORREPLICATION]

]

| | check [NOTFORRESCATION] |

(logical_expression)

}

:: =

[CONSTRAINTconstraint_name]

{[{PRIMARYKEY | UNIQUE}]

[CLUSTERED | NONCLUSTERED]

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

[WITHFILLFACTOR=fillfactor]

[ON {filegroup | DEFAULT}]

]

| | FOREIGNKEY |

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

REFERENCESref_table [(ref_column [,... n])]

[ONDELETE {CASCADE | NOACTION}]

[ONUPDATE {CASCADE | NOACTION}]

[NOTFORREPLICATION]

| | DEFAULTconstant_expression |

[FORcolumn] [WITHVALUES]

| | check [NOTFORRESCATION] |

(search_conditions)

}

As you can see, IDENTITY is only in, that is, we can use it in this way.

AlterTable table name Add field name IntIDENTITY (1pm 1)

That is, we can add a field and specify it as an automatic numbering field. But you can't change a field to an auto-numbering field (or maybe I can't find a way). That is, if we want to add an autonumbered field to the table, we can only use the method of adding a field, not change an existing field to an autonumbered field.

If you need to change the auto-numbering field count starting value, you can use the DBCC command:

DBCCCHECKIDENT (table name, RESEED,100)

The next automatic numbering field starts at 101.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.