Microsoft SQL Server 系列之 (自增字段操作)

原文连接:http://blog.sina.com.cn/s/blog_533749e20100b8jq.html

自己总结了一下MS SQL SERVER中添加自增字段的一些东西：

 

最简单的一种，在“设计表”的模式下编辑要设为自增字段的列属性。自增列的类型值必须是decimal、int、numeric、smallint、bigint 或 tinyint中的一种。在“表示”下拉菜单中选“是”，“标识种子”中填入自增字段的初始值，在“标识地增量”中填入自增量。

 

用SQL命令在SQL查询分析器中执行自增列的插入。

ALTER TABLE table_name ADD column column_type IDENTITY(seed,increment)

table_name是要插入自增字段的表格名，column_type必须是decimal、int、numeric、smallint、bigint 或 tinyint中的一种，seed和increment分别是自增字段的初始值和自增量。这两个值要么都指定，要么都留空。如果留空则默认值都是1，即初始值为1，以后每次都增加1。

 

SQL查询分析器好像不支持修改已有字段为自增字段，所以如果要将已有字段改为自增字段，只有先
ALTER TABLE table_name DROP COLUMN column_name把列名为column_name的列先删除，然后再执行

ALTER TABLE table_name ADD column column_type IDENTITY(seed,increment)命令重新插入。当然，删除已有列的前提是该列上不能有关联其他表的约束，否则还要用drop constraint先去除约束。鉴于这么操作过于复杂，而且很容易造成混乱，建议还是建立新列作为自增列。

 

此外，MSSQL SERVER不会重复使用已删除的标识值，所以使用一段时间之后标识符列的值可能会变得较为混乱，可以通过执行以下命令来解决：

DBCC CHECKIDENT ('table_name')

此命令将按其原来的seed和increment值重新对table_name表中的标识列进行赋值

DBCC CHECKIDENT ('table_name', RESEED)

同上

DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)

此命令将从new_reseed_value开始重新对table_name表中的标识列进行赋值

 

关于MSSQL SERVER到底为什么不支持对已有列执行SQL语句来将其修改为自增列，看了看帮助文件中ALTER语句的语法：

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

 

如红色标记的两行所示，对于IDENTITY属性的修改仅有ADD COLUMN支持，而ALTER COLUMN不支持，因此不能通过SQL语句修改已有字段为自增字段。

但是，ALTER COLUMN中提供了{ADD | DROP } ROWGUIDCOL，所以似乎可以迂回地使用 ROWGUIDCOL 属性、uniqueidentifier 数据类型和 NEWID 函数来定义全局唯一标识符列