使用sql語句建立修改SQL Server辨別列(即自動增長列)

一、辨別列的定義以及特點

SQL Server中的辨別列又稱辨別符列,習慣上又叫自增列。

該種列具有以下三種特點：

1、列的資料類型為不帶小數的數值類型

2、在進行插入(Insert)操作時，該列的值是由系統按一定規律生成,不允許空值

3、列值不重複，具有辨別表中每一行的作用,每個表隻能有一個辨別列。

由于以上特點，使得辨別列在資料庫的設計中得到廣泛的使用。

二、辨別列的組成

建立一個辨別列，通常要指定三個内容:

1、類型（type）

在SQL Server 2000中，辨別列類型必須是數值類型，如下：

decimal、int、numeric、smallint、bigint 、tinyint

其中要注意的是，當選擇decimal和numeric時，小數位數必須為零

另外還要注意每種資料類型所有表示的數值範圍

2、種子(seed)

是指派給表中第一行的值,預設為1

3、遞增量(increment)

相鄰兩個辨別值之間的增量，預設為1。

三、辨別列的建立與修改

辨別列的建立與修改，通常在企業管理器和用Transact-SQL語句都可實作，使用企業管理管理器比較簡單，請參考SQL Server的聯機幫助，這

裡隻讨論使用Transact-SQL的方法

1、建立表時指定辨別列

辨別列可用 IDENTITY 屬性建立，是以在SQL Server中，又稱辨別列為具有IDENTITY屬性的列或IDENTITY列。

下面的例子建立一個包含名為ID,類型為int,種子為1，遞增量為1的辨別列

CREATE TABLE T_test

(ID int IDENTITY(1,1),

Name varchar(50)

)

2、在現有表中添加辨別列

下面的例子向表T_test中添加一個名為ID,類型為int,種子為1，遞增量為1的辨別列

--建立表

(Name varchar(50)

--插入資料

INSERT T_test(Name) VALUES('張三')

--增加辨別列

ALTER TABLE T_test

ADD ID int IDENTITY(1,1)

3、判段一個表是否具有辨別列

可以使用 OBJECTPROPERTY 函數确定一個表是否具有 IDENTITY（辨別）列,用法:

Select OBJECTPROPERTY(OBJECT_ID('表名'),'TableHasIdentity')

如果有，則傳回1,否則傳回0

4、判斷某列是否是辨別列

可使用 COLUMNPROPERTY 函數确定 某列是否具有IDENTITY 屬性,用法

SELECT COLUMNPROPERTY( OBJECT_ID('表名'),'列名','IsIdentity')

如果該列為辨別列，則傳回1,否則傳回0

4、查詢某表辨別列的列名

SQL Server中沒有現成的函數實作此功能，實作的SQL語句如下

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns

WHERE TABLE_NAME='表名' AND COLUMNPROPERTY(

OBJECT_ID('表名'),COLUMN_NAME,'IsIdentity')=1

5、辨別列的引用

如果在SQL語句中引用辨別列，可用關鍵字IDENTITYCOL代替

例如，若要查詢上例中ID等于1的行，

以下兩條查詢語句是等價的

SELECT * FROM T_test WHERE IDENTITYCOL=1

SELECT * FROM T_test WHERE ID=1

6、擷取辨別列的種子值

可使用函數IDENT_SEED,用法：

SELECT IDENT_SEED ('表名')

7、擷取辨別列的遞增量

可使用函數IDENT_INCR ,用法：

SELECT IDENT_INCR('表名')

8、擷取指定表中最後生成的辨別值

可使用函數IDENT_CURRENT，用法:

SELECT IDENT_CURRENT('表名')

注意事項：當包含辨別列的表剛剛建立,為經過任何插入操作時，使用IDENT_CURRENT函數得到的值為辨別列的種子值，這一點在開發資料庫應用程式的時候尤其應該注意。

利用 SQL 語句修改出一個辨別列使用sql語句建立修改SQL <wbr>Server辨別列(即自動增長列)

--将資料複制到臨時表

select * into #aclist from aclist

--删除資料表

drop table aclist

--建立資料表（并設定辨別列）

create table aclist(id int identity(1,1),[date] datetime,version nvarchar(6),[class] nvarchar(10),actitle nvarchar(50),acdetail nvarchar(max),author nvarchar(50))

--設定辨別列允許插入

set identity_insert aclist on

--将資料從臨時表轉移過來

insert into aclist(id,[date],version,[class],actitle,acdetail,author)

select id,[date],version,[class],actitle,acdetail,author from #aclist

--關閉辨別列插入

set identity_insert aclist off

--強制設定辨別列的起始值:

DBCC CHECKIDENT (表名, RESEED, 1) --強制使辨別值從1開始.

----------------

修改原有字段中，不删除表，直接修改表中字段，删除資料後，處理。

---建立沒有自動增長的資料表

CREATE TABLE [tbMessage](

[id] [decimal](18, 0),

[msg_content] [varchar](max) NULL

) ON [PRIMARY]

GO

----插入測試資料

insert into [tbMessage]([id],[msg_content])

values(20,'你知道嗎？')

values(21,'你知道嗎？201')

go

--檢視資料

---select * from tbMessage

--插入臨時表

select * into #tbMessage from [tbMessage]

--删除表資料

delete [tbMessage]

--删除字段ID

alter table [tbMessage] drop column [ID]

---增加ID自動增長字段

alter table [tbMessage] add [id] int identity(1,1)

set identity_insert [tbMessage] on

insert into [tbMessage]([msg_content]

,[id])

select [msg_content]

,[id] from #tbMessage

set identity_insert [tbMessage] off

---删除臨時表

drop table #tbMessage

--------------------------------------------------

/*

drop table tbMessage

---------------檢測自動增長字段是否正常----------

----擷取種子資料

SELECT IDENT_SEED ('[tbMessage]')

---drop table tbMessage

---插入二條資料

insert into [tbMessage]([msg_content])

values('你知道嗎20111')

values('你知道嗎20112')

---檢視這個ID是否,正常增長

select * from tbMessage

*/