ALTER TABLE admin
ALTER COLUMN  ID (IDENTITY (1, 1) NOT NULL)

错误在哪里？没办法执行，我想把他改为 自动增长的列。

[ 本帖最后由 ysf0181 于 2011-11-24 12:37 编辑 ]

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只是在< column_definition >中，也就是说，我们可以这样使用

Alter Table 表名 Add 字段名 Int IDENTITY(1,1)

即，我们可以增加一个字段并指定它为自动编号字段。但是不能更改一个字段为自动编号字段（也或许我没找到方法）。即，如果我们想给表增加自动编号字段，只能使用添加字段的方法，而不能更改一个已有的字段为自动编号字段。

至于如果需要更改自动编号字段计数起始值可以使用DBCC命令：

DBCC CHECKIDENT (表名,RESEED,100)

自动编号字段下一个从101开始计。