SQL Serverでレコードを削除した後、IDシードをリセットします

SQLServerデータベーステーブルにレコードを挿入しました。テーブルには主キーが定義されており、自動インクリメントIDシードは「はい」に設定されています。これは主に、SQL Azureでは、各テーブルに主キーとIDを定義する必要があるためです。

ただし、テーブルからいくつかのレコードを削除する必要があるため、それらのテーブルのIDシードが妨害され、インデックス列（1の増分で自動生成される）が妨害されます。

レコードを削除した後、ID列をリセットして、列の番号が昇順になるようにするにはどうすればよいですか？

ID列は、データベース内のどこでも外部キーとして使用されません。

DBCC CHECKIDENT管理コマンドは、IDカウンタをリセットするために使用されます。コマンド構文は次のとおりです。

DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])
[ WITH NO_INFOMSGS ]

例：

DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO

以前のバージョンのAzureSQLデータベースではサポートされていませんでしたが、現在はサポートされています。

SQL Serverでレコードを削除した後、IDシードをリセットしますのおかげで、コマンドのドキュメントが修正されました。

DBCC CHECKIDENT ('TestTable', RESEED, 0)
GO

ここで、0はidentity開始値です。

IFことに留意すべき全てのデータが経由してテーブルから削除されているDELETE（すなわちノーWHEREであるように思われる（表を参照ないFKSが存在しない）、その後限り）アクセス許可が、aとbを可能にするように、句）ここでの場合）、TRUNCATE TABLEより効率的であると同時にシードをリセットするため、を使用することDELETE をお勧めしますIDENTITY。以下の詳細は、TRUNCATETABLEのMSDNページから抜粋したものです。

DELETEステートメントと比較すると、TRUNCATETABLEには次の利点があります。

• 使用されるトランザクションログスペースが少なくなります。

  DELETEステートメントは、一度に1行ずつ削除し、削除された各行のエントリをトランザクションログに記録します。TRUNCATE TABLEは、テーブルデータの格納に使用されるデータページの割り当てを解除することでデータを削除し、ページの割り当て解除のみをトランザクションログに記録します。

• 通常、使用されるロックは少なくなります。

  行ロックを使用してDELETEステートメントを実行すると、テーブル内の各行が削除のためにロックされます。TRUNCATE TABLEは、常にテーブル（スキーマ（SCH-M）ロックを含む）とページをロックしますが、各行はロックしません。

• 例外なく、テーブルにはゼロページが残っています。

  DELETEステートメントが実行された後でも、テーブルには空のページを含めることができます。たとえば、ヒープ内の空のページは、少なくとも排他的（LCK_M_X）テーブルロックがないと割り当てを解除できません。削除操作でテーブルロックを使用しない場合、テーブル（ヒープ）には多くの空のページが含まれます。インデックスの場合、削除操作によって空のページが残る可能性がありますが、これらのページはバックグラウンドクリーンアッププロセスによってすばやく割り当て解除されます。

テーブルにID列が含まれている場合、その列のカウンターは、その列に定義されているシード値にリセットされます。シードが定義されていない場合は、デフォルト値1が使用されます。IDカウンターを保持するには、代わりにDELETEを使用します。

したがって、次のようになります。

DELETE FROM [MyTable];
DBCC CHECKIDENT ('[MyTable]', RESEED, 0);

ちょうどなる：

TRUNCATE TABLE [MyTable];

TRUNCATE TABLE制限事項などの詳細については、上記のドキュメント（上記のリンク）を参照してください。

ほとんどの回答はRESEEDを0に提案していますが、多くの場合、利用可能な次のIDに再シードする必要があります

declare @max int
select @max=max([Id])from [TestTable]
if @max IS NULL   //check when max is returned as null
  SET @max = 0
DBCC CHECKIDENT ('[TestTable]', RESEED,@max)

これにより、テーブルがチェックされ、次のIDにリセットされます。

私は@anil shahs答えようとしました、そしてそれはアイデンティティをリセットしました。しかし、新しい行が挿入されると、が取得されましたidentity = 2。そこで、代わりに構文を次のように変更しました。

DELETE FROM [TestTable]

DBCC CHECKIDENT ('[TestTable]', RESEED, 0)
GO

次に、最初の行はID = 1を取得します。

ほとんどの回答はを示唆RESEEDして0おり、これをTRUNCATEDテーブルの欠陥と見なす人もいますが、Microsoftには、ID

DBCC CHECKIDENT ('[TestTable]', RESEED)

これにより、テーブルがチェックされ、次のにリセットされIDます。これは、MS SQL2005から現在まで利用可能です。

https://msdn.microsoft.com/en-us/library/ms176057.aspx

2コマンドを発行するとトリックが可能です

DBCC CHECKIDENT ('[TestTable]', RESEED,0)
DBCC CHECKIDENT ('[TestTable]', RESEED)

最初はIDをゼロにリセットし、次はそれを次に使用可能な値に設定します--jacob

@jacob

DBCC CHECKIDENT ('[TestTable]', RESEED,0)
DBCC CHECKIDENT ('[TestTable]', RESEED)

私のために働いた、私は最初にテーブルからすべてのエントリをクリアし、次に削除後のトリガーポイントに上記を追加する必要がありました。これで、削除するたびにそこからエントリが取得されます。

Truncate テーブルは、レコードをクリアし、カウンターをリセットし、ディスク領域を再利用するため、推奨されます。

DeleteまたCheckIdent、外部キーによって切り捨てができない場合にのみ使用してください。

ID列を新しいIDでリセット...

DECLARE @MAX INT
SELECT @MAX=ISNULL(MAX(Id),0) FROM [TestTable]

DBCC CHECKIDENT ('[TestTable]', RESEED,@MAX)

これはよくある質問であり、答えは常に同じです。そうしないでください。ID値は任意として扱う必要があるため、「正しい」順序はありません。

このスクリプトを実行して、ID列をリセットします。2つの変更を加える必要があります。tableXYZを、更新する必要のあるテーブルに置き換えます。また、ID列の名前を一時テーブルから削除する必要があります。これは、35,000行3列のテーブルでは瞬時に行われました。明らかに、テーブルをバックアップし、最初にテスト環境でこれを試してください。

select * 
into #temp
From tableXYZ

set identity_insert tableXYZ ON

truncate table tableXYZ

alter table #temp drop column (nameOfIdentityColumn)

set identity_insert tableXYZ OFF

insert into tableXYZ
select * from #temp

これを行うには、次のスクリプトを使用します。「エラー」が発生するシナリオは1つだけです。これは、テーブルからすべての行を削除し、IDENT_CURRENT現在1に設定されている場合、つまり、最初はテーブルに1行しかなかった場合です。

DECLARE @maxID int = (SELECT MAX(ID) FROM dbo.Tbl)
;

IF @maxID IS NULL
    IF (SELECT IDENT_CURRENT('dbo.Tbl')) > 1
        DBCC CHECKIDENT ('dbo.Tbl', RESEED, 0)
    ELSE
        DBCC CHECKIDENT ('dbo.Tbl', RESEED, 1)
    ;
ELSE
    DBCC CHECKIDENT ('dbo.Tbl', RESEED, @maxID)
;

DBCC CHECKIDENT (<TableName>, reseed, 0)

これにより、現在のID値が0に設定されます。

次の値を挿入すると、ID値は1に増分されます。

このストアドプロシージャを使用します。

IF (object_id('[dbo].[pResetIdentityField]') IS NULL)
  BEGIN
    EXEC('CREATE PROCEDURE [dbo].[pResetIdentityField] AS SELECT 1 FROM DUMMY');
  END
GO

SET  ANSI_NULLS ON
GO
SET  QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[pResetIdentityField]
  @pSchemaName NVARCHAR(1000)
, @pTableName NVARCHAR(1000) AS
DECLARE @max   INT;
DECLARE @fullTableName   NVARCHAR(2000) = @pSchemaName + '.' + @pTableName;

DECLARE @identityColumn   NVARCHAR(1000);

SELECT @identityColumn = c.[name]
FROM sys.tables t
     INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
     INNER JOIN sys.columns c ON c.[object_id] = t.[object_id]
WHERE     c.is_identity = 1
      AND t.name = @pTableName
      AND s.[name] = @pSchemaName

IF @identityColumn IS NULL
  BEGIN
    RAISERROR(
      'One of the following is true: 1. the table you specified doesn''t have an identity field, 2. you specified an invalid schema, 3. you specified an invalid table'
    , 16
    , 1);
    RETURN;
  END;

DECLARE @sqlString   NVARCHAR(MAX) = N'SELECT @maxOut = max(' + @identityColumn + ') FROM ' + @fullTableName;

EXECUTE sp_executesql @stmt = @sqlString, @params = N'@maxOut int OUTPUT', @maxOut = @max OUTPUT

IF @max IS NULL
  SET @max = 0

print(@max)

DBCC CHECKIDENT (@fullTableName, RESEED, @max)
go

--exec pResetIdentityField 'dbo', 'Table'

私の答えをもう一度見てください。私はあなたが知っておくべきSQLServer2008r2で奇妙な振る舞いに出くわしました。

drop table test01

create table test01 (Id int identity(1,1), descr nvarchar(10))

execute pResetIdentityField 'dbo', 'test01'

insert into test01 (descr) values('Item 1')

select * from test01

delete from test01

execute pResetIdentityField 'dbo', 'test01'

insert into test01 (descr) values('Item 1')

select * from test01

最初の選択でが生成され0, Item 1ます。

2番目のものはを生成し1, Item 1ます。テーブルが作成された直後にリセットを実行すると、次の値は0になります。正直なところ、Microsoftがこのようなものを正しく取得できないのは驚きではありません。テーブルを再作成した後や、テーブルがすでに作成されているときに実行することがある参照テーブルにデータを入力するスクリプトファイルがあるために発見しました。

私は開発中に多数のテーブルに対してこれを実行しようとしてきましたが、これは魅力として機能します。

DBCC CHECKIDENT('www.newsType', RESEED, 1);
DBCC CHECKIDENT('www.newsType', RESEED);

したがって、最初に強制的に1に設定し、次にテーブルに存在する行の最高のインデックスに設定します。idexの残りの部分をすばやく簡単に。

完全なDELETE行とIDENTITYカウントをリセットするには、これを使用します（SQL Server 2008 R2）

USE mydb

-- ##################################################################################################################
-- DANGEROUS!!!! USE WITH CARE
-- ##################################################################################################################

DECLARE
  db_cursor CURSOR FOR
    SELECT TABLE_NAME
      FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_TYPE = 'BASE TABLE'
       AND TABLE_CATALOG = 'mydb'

DECLARE @tblname VARCHAR(50)
SET @tblname = ''

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tblname

WHILE @@FETCH_STATUS = 0
BEGIN
  IF CHARINDEX('mycommonwordforalltablesIwanttodothisto', @tblname) > 0
    BEGIN
      EXEC('DELETE FROM ' + @tblname)
      DBCC CHECKIDENT (@tblname, RESEED, 0)
    END

  FETCH NEXT FROM db_cursor INTO @tblname
END

CLOSE db_cursor
DEALLOCATE db_cursor
GO

テーブル全体をクリーンアップしない限り、0に再シードすることはあまり実用的ではありません。

そうでなければ、アンソニー・レイモンドによって与えられた答えは完璧です。最初に最大ID列を取得してから、maxをシードします。