複製鏈接
請複製以下鏈接發送給好友

標識列

鎖定
標識列,是SQL Server中的標識列又稱標識符列,習慣上又叫自增列。標識列的創建修改,通常在企業管理器和用Transact-SQL語句都可實現。
中文名
標識列
別    名
標識符列
自增列

標識列列具特點

該種列具有以下三種特點:
1、列的數據類型為不帶小數的數值類型
2、在進行插入(Insert)操作時,該列的值是由系統按一定規律生成,不允許空值
3、列值不重複,具有標識表中每一行的作用,每個表只能有一個標識列。
由於以上特點,使得標識列在數據庫的設計中得到廣泛的使用。

標識列組成

創建一個標識列,通常要指定三個內容:
1、類型(type)
在SQL Server 2000中,標識列類型必須是數值類型,如下:
decimal、int、numeric、smallint、bigint 、tinyint
其中要注意的是,當選擇decimal和numeric時,小數位數必須為零
另外還要注意每種數據類型所有表示的數值範圍
2、種子(seed)
是指派給表中第一行的值,默認為1
3、遞增量(increment)
相鄰兩個標識值之間的增量,默認為1。

標識列創建與修改

標識列的創建與修改,使用企業管理管理器比較簡單,請參考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的標識列
--創建表
CREATE TABLE T_test
(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
5、查詢某表標識列的列名
SQL Server中沒有現成的函數實現此功能,實現的SQL語句如下
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME=’表名’ ANDCOLUMNPROPERTY(
OBJECT_ID(’表名’),COLUMN_NAME,’IsIdentity’)=1
6、標識列的引用
如果在SQL語句中引用標識列,可用關鍵字IDENTITYCOL代替
例如,若要查詢上例中ID等於1的行,
以下兩條查詢語句是等價的
SELECT * FROM T_test WHERE IDENTITYCOL=1
SELECT * FROM T_test WHERE ID=1
7、獲取標識列的種子值
可使用函數IDENT_SEED,用法:
SELECT IDENT_SEED (’表名’)
8、獲取標識列的遞增量
可使用函數IDENT_INCR,用法:
SELECT IDENT_INCR(’表名’)
9、獲取指定表中最後生成的標識值
可使用函數IDENT_CURRENT,用法:
SELECT IDENT_CURRENT(’表名’)
注意事項:當包含標識列的表剛剛創建,為經過任何插入操作時,使用IDENT_CURRENT函數得到的值為標識列的種子值,這一點在開發數據庫應用程序的時候尤其應該注意。

標識列總結

標識列在複製中的處理方法
快照複製中,通常無須考慮標識列的屬性。
舉例:
發佈數據庫A,訂閲數據庫B,出版物為T_test_A,訂閲表為T_test_B
CREATE TABLE T_test_A
(ID int IDENTITY(1,1),
Name varchar(50)
CREATE TABLE T_test_B
(ID int IDENTITY(1,1),
Name varchar(50)
在這種情況下,複製代理將無法將新行復制到庫B,因為列ID是標識列,不能給標識列顯示提供值,複製失敗。
這時,需要為標識列設置NOT FOR REPLICATION 選項。這樣,當複製代理程序用任何登錄連接到庫B上的表T_test時,該表上的所有 NOT
FOR REPLICATION 選項將被激活,就可以顯式插入ID列。
這裏分兩種情況:
1、庫B的T_test表不會被用户(或應用程序)更新
最簡單的情況是:如果庫B的T_test不會被用户(或應用程序)更新,那建議去掉ID列的標識屬性,只採用簡單int類型即可。
2、庫B的T_test表是會被其他用户(或應用程序)更新
這種情況下,兩個T_test表的ID列就會發生衝突,舉例:
在庫A中執行如下語句:
INSERT T_test_A(Name) VALUES(’Tom’)(假設ID列為1)
在庫B中執行如下語句:
INSERT T_test_B(Name) VALUES(’Pip’)(假設ID列為1)
這樣,就會在庫A和庫B的兩個表分別插入一條記錄,顯然,是兩條不同的記錄。
然而事情還沒有結束,待到預先設定的複製時間,複製代理試圖把記錄"1 TOM"插入到庫B中的T_test表,但庫B的T_test_B表已經存在
ID為1的列,插入不會成功,通過複製監視器,我們會發現複製失敗了。
解決以上問題的方法有:
⑴為發佈方和訂閲方的標識列指定不同範圍的值,如上例可修改為:
--確保該表記錄不會超過10000000
CREATE TABLE T_test_A
(ID int IDENTITY(1,1),
Name varchar(50)
CREATE TABLE T_test_B
(ID int IDENTITY(10000000,1),
Name varchar(50)
⑵使發佈方和訂閲方的標識列的值不會重複,如
--使用奇數值
CREATE TABLE T_test_A
(ID int IDENTITY(1,2),
Name varchar(50)
--使用偶數值
CREATE TABLE T_test_B
(ID int IDENTITY(2,2),
Name varchar(50)
這種辦法可推廣,當訂閲方和發佈方有四處時,標識列屬性的定義分別如下
(1,4),(2,4),(3,4),(4,4)
採用事務複製中解決方法,只要使發佈表和訂閲表標識列的值不重複既可。