本文主要向大家介绍了SQLServer数据库自增列(Identity列)的所有操作,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。
一、定義
a)在CREATE TABLE 中:
[sql] view plain copy
1. CREATE TABLE dbo.Identity_test
2. (
3. ID INT IDENTITY(1,1), --(seed = 1,increment = 1) 從1開始,每次遞增1
4. Content NVARCHAR(200)
5. )
b)在SELECT INTO 中:
[sql] view plain copy
1. SELECT
2. IDENTITY(INT,100,1) ID,
3. ColName
4. INTO #temp FROM TableName
二、自增列屬性函數
一個表最多只能有一個自增列。
下面的三個函數,可以很方便地查看表中identity列的情況
[sql] view plain copy
1. SELECT
2. IDENT_SEED('dbo.Identity_test') [IDENT_SEED] -- 表中自增列的種子值
3. ,IDENT_INCR('dbo.Identity_test') [IDENT_INCR] -- 表中自增列的的增量
4. ,IDENT_CURRENT('dbo.Identity_test') [IDENT_CURRENT] -- 表中自增列的當前值
表中的自增列一旦創建就不能更改種子值和增量值了,只能更改當前值。要修改只有先刪除再重建了
使用上面三個函數的一例:
[sql] view plain copy
1. INSERT INTO Identity_test(Content) -- 不寫出ID列,它自動增加
2. SELECT 'Str1' UNION ALL
3. SELECT 'Str2' UNION ALL
4. SELECT 'Str3' UNION ALL
5. SELECT 'Str4' UNION ALL
6. SELECT 'Str5'
7.
8.
9. SELECT
10. IDENT_SEED('dbo.Identity_test') [IDENT_SEED] -- 1
11. ,IDENT_INCR('dbo.Identity_test') [IDENT_INCR] -- 1
12. ,IDENT_CURRENT('dbo.Identity_test') [IDENT_CURRENT] -- 5
DELETE 不會讓ID斷流,下次的ID值是ID列的增量+上次的ID值。
Truncate 會讓ID列重新開始。
三、全局變量@@Identity
@@Identity可以獲取最近產生的一個ID值,一般是Insert 之後產生的。不區分具體表。
在編程中可以用這個變量,而沒必要用select Max(ID) From TableName 這樣的語句,如果對其不放心,可以用ident_current()函數。
四、顯示插入ID列
這個需要先開啟 SET IDENTITY_INSERT TableName ON,而且不能省略Insert後面括號裏的列名列表。
例如:
[sql] view plain copy
1. SET IDENTITY_INSERT dbo.Identity_test ON
2.
3. INSERT INTO Identity_test(ID,Content) -- 顯示寫出列表,至少ID列表不能少
4. SELECT 11,'Repeat'
5.
6. SET IDENTITY_INSERT dbo.Identity_test OFF
注意,只要ID列不是主鍵,沒有設唯一索引,它的值是可以重覆的。
五、重置當前ID值
在必要的時候,可以用DBCC CHECKIDENT命令來重置表中自增列的當前ID值。
如下示例將表的重置為10:
[sql] view plain copy
1. DBCC CHECKIDENT('dbo.Identity_test',RESEED,10) --下次從11開始
六、將Identity(1,1)改為Identity(10000,1)
這個沒有直接的辦法,只能繞道而行:
[sql] view plain copy
1. ALTER TABLE dbo.Identity_test DROP COLUMN ID
2. GO
3. ALTER TABLE dbo.Identity_test ADD ID INT IDENTITY(10000,1)
4. Go
執行後,表中所有記錄的ID會自動按新的種子和遞增賦值。
七、查詢數據庫的所有自增列(Identity列)的情況
查詢數據庫中所有含自增列的:表名,表中自增列列表,種子值,增量,當前ID值
[sql] view plain copy
1. SELECT
2. b.name TableName
3. ,a.name ColumnName
4. ,a.seed_value
5. ,a.increment_value
6. ,a.last_value
7. FROM sys.identity_columns a
8. INNER JOIN sys.tables b ON a.object_id = b.object_id
本文由职坐标整理并发布,希望对同学们学习SQL Server有所帮助,更多内容请关注职坐标数据库SQL Server数据库频道!