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

This article is about how to set the automatic numbering field in SQLServer. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

How to set the automatic numbering field for SQLServer2005 and Access

The specific steps are as follows:

① is like an automatic numbering field in Access.

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

IDENTITY (SEED,INCREMENT) parameter for ②

Seed- starting value increment- increment CREATETABLE table name (your IDIDENTITY (1) NOTNULL, your other fields...) CREATETABLE table name (your field IDAUTOINCREMENT (1000) 10), 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 3 and 4 are 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 AlterColumn your field IDENTITY (1Magne1) to specify that the field starts counting again from 1, but this sentence itself is wrong, and I wonder why it can't be executed for a long time. 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 set the automatic numbering field for SQLServer2005 and Access

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 [CASCADE | NOACTION}] (logical_expression)}: = [CONSTRAINTconstraint_name] {[{PRIMARYKEY | UNIQUE} [CLUSTERED | NONCLUSTERED] {(column [, n])} [WITHFILLFACTOR=fillfactor] [ON {filegroup | DEFAULT}]] | N])] REFERENCESref_table [(ref_column [, n])] [ONDELETE {CASCADE | NOACTION}] [ONUPDATE {CASCADE | NOACTION}] [NOTFORREPLICATION] | DEFAULTconstant_ expression [for column] [WITHVALUES] | check [NotForcolumn] (search_conditions)}

As you can see, IDENTITY is only in, that is, we can use it this way: the AlterTable table name, Add field name, IntIDENTITY (1mem1), 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 starting value of the automatic numbering field count, you can use the DBCC command: DBCCCHECKIDENT (table name, RESEED,100), and the next automatic numbering field starts at 101th.

The above is how to set the automatic numbering field in SQLServer. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.