SQL Server自己増分列(Identity列)のすべての操作
2595 ワード
一、定義
a)CREATE TABLEで:
CREATE TABLE dbo.Identity_test
(
ID INT IDENTITY(1,1), --(seed = 1,increment = 1) 1 , 1
Content NVARCHAR(200)
)
b)SELECT INTO:SELECT
IDENTITY(INT,100,1) ID,
ColName
INTO #temp FROM TableName
二、自増列属性関数
1つのテーブルには最大1つの自己増分列しかありません.
次の3つの関数は、テーブルのidentity列を簡単に表示できます.
SELECT
IDENT_SEED('dbo.Identity_test') [IDENT_SEED] --
,IDENT_INCR('dbo.Identity_test') [IDENT_INCR] --
,IDENT_CURRENT('dbo.Identity_test') [IDENT_CURRENT] --
表の自己増分列を作成すると、シード値と増分値は変更できません.現在の値のみ変更できます.修正するには削除してから再構築するしかない次の3つの関数の例を使用します.
INSERT INTO Identity_test(Content) -- ID ,
SELECT 'Str1' UNION ALL
SELECT 'Str2' UNION ALL
SELECT 'Str3' UNION ALL
SELECT 'Str4' UNION ALL
SELECT 'Str5'
SELECT
IDENT_SEED('dbo.Identity_test') [IDENT_SEED] -- 1
,IDENT_INCR('dbo.Identity_test') [IDENT_INCR] -- 1
,IDENT_CURRENT('dbo.Identity_test') [IDENT_CURRENT] -- 5
DELETEはIDをストリーム化しません.次のID値はID列のインクリメンタル+前回のID値です.TruncateはID列を再開します.
三、グローバル変数@@Identity
@@@Identityは、最近生成されたID値を取得できます.一般的には、Insertの後に生成されます.体表を区別しない.
プログラミングではこの変数を使うことができますが、select Max(ID)From Table Nameのような文を使う必要はありません.不安ならident_current()関数.
四、挿入ID列を表示する
これはSET IDENTITYをオンにする必要があります.INSERT Table NameがONし、Insertの後かっこの列名リストを省略することはできません.
例:
SET IDENTITY_INSERT dbo.Identity_test ON
INSERT INTO Identity_test(ID,Content) -- , ID
SELECT 11,'Repeat'
SET IDENTITY_INSERT dbo.Identity_test OFF
なお、ID列がプライマリ・キーではなく、一意のインデックスが設定されていない限り、その値は上書き可能である.
五、現在のID値をリセットする
必要に応じて、DBCC CHECKIDENTコマンドを使用して、テーブル内の自増列の現在のID値をリセットできます.
次の例では、テーブルを10にリセットします.
DBCC CHECKIDENT('dbo.Identity_test',RESEED,10) -- 11
六、Identity(1,1)をIdentity(10000,1)に変更する
これは直接的な方法がなく、迂回するしかありません.
ALTER TABLE dbo.Identity_test DROP COLUMN ID
GO
ALTER TABLE dbo.Identity_test ADD ID INT IDENTITY(10000,1)
Go
実行すると、テーブル内のすべてのレコードのIDが自動的に新しいシードと増分で割り当てられます.
七、クエリーデータベースのすべての自増列(Identity列)の場合
クエリー・データベース内のすべての自増列を含む:テーブル名、テーブル内の自増列リスト、シード値、インクリメンタル、現在のID値
SELECT
b.name TableName
,a.name ColumnName
,a.seed_value
,a.increment_value
,a.last_value
FROM sys.identity_columns a
INNER JOIN sys.tables b ON a.object_id = b.object_id