pengembangan-web-mp.com

Reset benih identitas setelah menghapus catatan di SQL Server

Saya telah memasukkan catatan ke tabel database SQL Server. Tabel memiliki kunci utama yang ditentukan dan seed identitas kenaikan otomatis diatur ke "Ya". Ini dilakukan terutama karena dalam SQL Azure, setiap tabel harus memiliki kunci utama dan identitas yang ditentukan. 

Tetapi karena saya harus menghapus beberapa catatan dari tabel, seed identitas untuk tabel-tabel itu akan terganggu dan kolom indeks (yang dihasilkan secara otomatis dengan selisih 1) akan terganggu.

Bagaimana saya bisa mereset kolom identitas setelah saya menghapus catatan sehingga kolom memiliki urutan dalam urutan numerik?

Kolom identitas tidak digunakan sebagai kunci asing di manapun dalam basis data.

553
Romil N

Perintah DBCC CHECKIDENT manajemen digunakan untuk mengatur ulang penghitung identitas. Sintaks perintahnya adalah:

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

Contoh: 

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

Itu tidak didukung di versi sebelumnya dari Azure SQL Database, tetapi didukung sekarang.


Harap dicatat bahwa argumen new_reseed_value bervariasi di seluruh versi SQL Server sesuai dengan dokumentasi

Jika baris ada dalam tabel, baris berikutnya dimasukkan dengan nilai new_reseed_value. Dalam versi SQL Server 2008 R2 dan sebelumnya, baris berikutnya yang dimasukkan menggunakan new_reseed_value + nilai kenaikan saat ini.

Namun, Saya menemukan informasi ini menyesatkan (sebenarnya salah sebenarnya) karena perilaku yang diamati menunjukkan bahwa setidaknya SQL Server 2012 masih menggunakan new_reseed_value + logika nilai kenaikan saat ini. Microsoft bahkan bertentangan dengan Example C sendiri yang ditemukan di halaman yang sama:

C. Memaksa nilai identitas saat ini ke nilai baru

Contoh berikut memaksa nilai identitas saat ini di Kolom AddressTypeID di tabel AddressType dengan nilai 10 . Karena tabel memiliki baris yang sudah ada, baris berikutnya yang dimasukkan akan menggunakan 11 sebagai nilai, yaitu nilai kenaikan saat ini yang baru ditentukan untuk nilai kolom ditambah 1.

USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);  
GO

Namun, ini semua meninggalkan opsi untuk perilaku yang berbeda pada versi SQL Server yang lebih baru. Saya kira satu-satunya cara untuk memastikan, sampai Microsoft menjernihkan hal-hal dalam dokumentasinya sendiri, adalah dengan melakukan tes yang sebenarnya sebelum digunakan.

920
Petr Abdulin
DBCC CHECKIDENT ('TestTable', RESEED, 0)
GO

Di mana 0 adalah identity Nilai awal

188
anil shah

Perlu dicatat bahwa JIKA semua data dihapus dari tabel melalui DELETE (yaitu tidak ada WHERE klausa), maka selama a) izin mengizinkannya, dan b) tidak ada FK yang merujuk pada tabel (yang tampaknya menjadi kasus di sini), menggunakan TRUNCATE TABLE akan lebih disukai karena lebih efisien DELETE/ dan me-reset benih IDENTITY pada saat yang sama. Detail berikut diambil dari halaman MSDN untuk TRUNCATE TABLE :

Dibandingkan dengan pernyataan DELETE, TRUNCATE TABLE memiliki kelebihan berikut:

  • Ruang log transaksi lebih sedikit digunakan.

    Pernyataan DELETE menghapus baris satu per satu dan mencatat entri dalam log transaksi untuk setiap baris yang dihapus. TRUNCATE TABLE menghapus data dengan mendeallocating halaman data yang digunakan untuk menyimpan data tabel dan mencatat hanya deallocations halaman dalam log transaksi.

  • Lebih sedikit kunci biasanya digunakan.

    Ketika pernyataan DELETE dijalankan menggunakan kunci baris, setiap baris dalam tabel dikunci untuk dihapus. TRUNCATE TABLE selalu mengunci tabel (termasuk kunci skema (SCH-M)) dan halaman tetapi tidak setiap baris.

  • Tanpa kecuali, nol halaman tersisa di tabel.

    Setelah pernyataan DELETE dijalankan, tabel masih dapat berisi halaman kosong. Misalnya, halaman kosong di tumpukan tidak dapat dibatalkan alokasi tanpa setidaknya kunci tabel eksklusif (LCK_M_X). Jika operasi hapus tidak menggunakan kunci tabel, tabel (heap) akan berisi banyak halaman kosong. Untuk indeks, operasi penghapusan dapat meninggalkan halaman kosong, meskipun halaman ini akan dengan cepat dialokasikan dengan cepat oleh proses pembersihan latar belakang.

Jika tabel berisi kolom identitas, penghitung untuk kolom itu diatur ulang ke nilai seed yang ditentukan untuk kolom. Jika tidak ada seed yang didefinisikan, nilai default 1 digunakan. Untuk mempertahankan penghitung identitas, gunakan DELETE sebagai gantinya.

Jadi yang berikut ini:

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

Menjadi adil:

TRUNCATE TABLE [MyTable];

Silakan lihat dokumentasi TRUNCATE TABLE (ditautkan di atas) untuk informasi tambahan tentang pembatasan, dll.

71
Solomon Rutzky

Meskipun sebagian besar jawaban menyarankan RESEED ke 0, tetapi seringkali kita perlu kembali ke Id berikutnya yang tersedia

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)

Ini akan memeriksa tabel dan mengatur ulang ke ID berikutnya.

62
Atal Kishore

Saya mencoba jawaban @anil shahs dan mengatur ulang identitas. Tetapi ketika sebuah baris baru dimasukkan, ia mendapatkan identity = 2. Jadi alih-alih saya mengubah sintaks menjadi:

DELETE FROM [TestTable]

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

Maka baris pertama akan mendapatkan identitas = 1.

57
Mikael Engver

Meskipun sebagian besar jawaban menyarankan RESEED ke 0, dan sementara beberapa orang melihat ini sebagai cacat untuk tabel TRUNCATED, Microsoft memiliki solusi yang mengecualikan ID

DBCC CHECKIDENT ('[TestTable]', RESEED)

Ini akan memeriksa tabel dan mengatur ulang ke ID berikutnya. Ini telah tersedia sejak MS SQL 2005 hingga saat ini.

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

15
SollyM

@ Jacob

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

Berhasil bagi saya, saya hanya perlu menghapus semua entri terlebih dahulu dari tabel, lalu menambahkan di atas pada titik pemicu setelah dihapus. Sekarang setiap kali saya menghapus entri diambil dari sana.

5
epic

Setel ulang kolom identitas dengan id baru ...

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

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

mengeluarkan 2 perintah dapat melakukan triknya

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

yang pertama atur ulang identitas menjadi nol, dan selanjutnya akan mengaturnya ke nilai berikutnya yang tersedia. - Jacobs

4
jacob

Ini adalah pertanyaan umum dan jawabannya selalu sama: jangan lakukan itu. Nilai identitas harus diperlakukan sebagai arbitrer dan, dengan demikian, tidak ada urutan "benar".

4
Ben Thul

Truncate table lebih disukai karena membersihkan catatan, me-reset counter dan mengambil kembali ruang dis. 

Delete dan CheckIdent harus digunakan hanya jika kunci asing mencegah Anda memotong

3
Dyna Dave

Jalankan skrip ini untuk mereset kolom identitas. Anda harus melakukan dua perubahan. Ganti tableXYZ dengan tabel apa pun yang Anda perlu perbarui. Juga, nama kolom identitas perlu dijatuhkan dari tabel temp. Ini instan di atas meja dengan 35.000 baris & 3 kolom. Jelas, buat cadangan tabel dan coba ini dulu di lingkungan pengujian. 


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
2
Matthew Baic

Gunakan prosedur tersimpan ini:

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'

Hanya meninjau kembali jawaban saya. Saya menemukan perilaku aneh di sql server 2008 r2 yang harus Anda waspadai.

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

Pilihan pertama menghasilkan 0, Item 1.

Yang kedua menghasilkan 1, Item 1. Jika Anda menjalankan pengaturan ulang tepat setelah tabel dibuat, nilai selanjutnya adalah 0. Jujur, saya tidak terkejut Microsoft tidak bisa memperbaiki masalah ini. Saya menemukannya karena saya memiliki file skrip yang mengisi tabel referensi yang kadang saya jalankan setelah saya membuat ulang tabel dan kadang-kadang ketika tabel sudah dibuat.

1
costa

Untuk baris DELETE lengkap dan setel ulang jumlah IDENTITAS, saya menggunakan ini (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
Fandango68

Saya menggunakan skrip berikut untuk melakukan ini. Hanya ada satu skenario di mana ia akan menghasilkan "kesalahan", yaitu jika Anda telah menghapus semua baris dari tabel, dan IDENT_CURRENT saat ini diatur ke 1, yaitu hanya ada satu baris dalam tabel untuk memulai.

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)
;
0
Chris Mack
DBCC CHECKIDENT (<TableName>, reseed, 0)

Ini akan menetapkan nilai identitas saat ini ke 0.

Saat memasukkan nilai berikutnya, nilai identitas akan bertambah menjadi 1.

0
Bimzee