Saya sudah memasukkan data ke database SQL Server meja. Tabel memiliki primary key yang ditetapkan dan auto increment identitas benih diatur ke "Ya". Hal ini dilakukan terutama karena di SQL Azure, masing-masing tabel harus memiliki primary key dan identitas didefinisikan.
Tapi karena aku harus menghapus beberapa catatan dari meja, identitas benih untuk tabel tersebut akan terganggu dan indeks kolom (yang dihasilkan secara otomatis dengan kenaikan 1) akan terganggu.
*Bagaimana saya dapat me-reset kolom identitas setelah saya menghapus catatan sehingga kolom memiliki urutan menaik numerik order?**
Kolom identitas tidak digunakan sebagai foreign key di mana saja di database.
The DBCC CHECKIDENT
manajemen perintah ini digunakan untuk me-reset identitas counter. Sintaks perintah ini:
DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])
[ WITH NO_INFOMSGS ]
Contoh:
DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO
Itu tidak didukung dalam versi sebelumnya dari SQL Azure Database, tetapi didukung sekarang.
Harap dicatat bahwa new_reseed_value
argumen bervariasi di SQL Server versi menurut dokumentasi:
Jika baris yang ada di tabel, baris berikutnya dimasukkan dengan new_reseed_value nilai. Pada versi SQL Server 2008 R2 dan sebelumnya, baris-baris selanjutnya dimasukkan menggunakan new_reseed_value + saat ini kenaikan nilai.
Namun, saya menemukan informasi ini menyesatkan (just plain wrong sebenarnya) karena mengamati perilaku menunjukkan bahwa setidaknya SQL Server 2012 masih menggunakan new_reseed_value + saat ini kenaikan nilai logika. Microsoft bahkan bertentangan dengan Contoh C
ditemukan di halaman yang sama:
C. Memaksa saat ini identitas nilai baru nilai
berikut ini contoh kekuatan saat ini identitas nilai dalam AddressTypeID kolom di AddressType tabel untuk nilai 10. Karena meja telah ada baris, baris-baris selanjutnya dimasukkan akan menggunakan 11 sebagai nilai, yang, baru saat ini kenaikan nilai yang ditetapkan untuk kolom nilai plus 1.
USE AdventureWorks2012;
GO
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);
GO
Namun, ini semua membuat pilihan untuk perilaku yang berbeda pada baru versi SQL Server. Saya kira satu-satunya cara untuk memastikan, sampai Microsoft membersihkan hal-hal dalam dirinya sendiri dokumentasi, adalah untuk melakukan tes yang sebenarnya sebelum penggunaan.
Perlu dicatat bahwa JIKA all dari data yang dihapus dari tabel melalui HAPUS
(yaitu tidak ada WHERE
), maka selama a) izin memungkinkan untuk itu, dan b) ada tidak ada FKs referensi tabel (yang tampaknya menjadi kasus di sini), menggunakan TRUNCATE TABLE
akan menjadi pilihan seperti itu tidak lebih efisien HAPUS
and me-reset IDENTITAS
benih pada waktu yang sama. Berikut rincian yang diambil dari MSDN halaman TRUNCATE TABLE:
Dibandingkan dengan pernyataan DELETE, TRUNCATE TABLE memiliki keuntungan sebagai berikut:
- Kurang transaksi log ruang yang digunakan.
pernyataan DELETE menghapus baris pada satu waktu dan merekam entri dalam log transaksi untuk masing-masing dihapus-turut. TRUNCATE TABLE menghilangkan data deallocating data halaman yang digunakan untuk menyimpan tabel data dan catatan hanya halaman deallocations dalam log transaksi.
- lebih Sedikit kunci yang biasanya digunakan.
Ketika MENGHAPUS pernyataan ini dieksekusi menggunakan baris kunci, masing-masing baris pada tabel terkunci untuk penghapusan. TRUNCATE TABLE selalu mengunci tabel (termasuk skema (SCH-M) lock) dan halaman tapi tidak setiap baris.
- Tanpa terkecuali, nol halaman-halaman yang tersisa di meja.
Setelah MENGHAPUS pernyataan yang dijalankan, masih dapat berisi halaman kosong. Misalnya, halaman kosong di tumpukan tidak dapat deallocated tanpa setidaknya eksklusif (LCK_M_X) tabel kunci. Jika menghapus operasi tidak menggunakan kunci tabel, tabel (tumpukan) akan berisi banyak halaman kosong. Untuk indeks, hapus operasi dapat meninggalkan halaman kosong di belakang, meskipun halaman-halaman ini akan deallocated dengan cepat oleh latar belakang proses pembersihan.
Jika tabel berisi kolom identitas, counter untuk kolom tersebut adalah me-reset ke nilai benih yang ditentukan untuk kolom. Jika tidak ada benih yang ditetapkan, nilai default 1 digunakan. Untuk mempertahankan identitas counter, gunakan MENGHAPUS sebaliknya.
Jadi berikut ini:
DELETE FROM [MyTable];
DBCC CHECKIDENT ('[MyTable]', RESEED, 0);
Menjadi:
TRUNCATE TABLE [MyTable];
Silakan lihat TRUNCATE TABLE
dokumentasi (terkait di atas) untuk informasi tambahan mengenai pembatasan, dll.
Meskipun sebagian besar jawaban yang menunjukkan RESEED ke 0, Tapi banyak kali kita hanya perlu reseed untuk selanjutnya Id 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 meja dan me-reset ke depan ID.
Aku mencoba @anil shah
menjawab dan me-reset identitas. Tapi jika baris baru disisipkan itu punya identitas = 2
. Jadi, bukannya saya mengubah sintaks untuk:
DELETE FROM [TestTable]
DBCC CHECKIDENT ('[TestTable]', RESEED, 0)
GO
Kemudian baris pertama akan mendapatkan identitas = 1.
Meskipun sebagian besar jawaban yang menunjukkan RESEED
ke 0
, dan sementara beberapa melihat ini sebagai kelemahan untuk TERPOTONG
tabel, Microsoft memiliki solusi yang tidak termasuk ID
DBCC CHECKIDENT ('[TestTable]', RESEED)
Ini akan memeriksa meja dan me-reset ke depan ID
. Ini telah tersedia sejak MS SQL 2005 hingga saat ini.
@jacob
DBCC CHECKIDENT ('[TestTable]', RESEED,0)
DBCC CHECKIDENT ('[TestTable]', RESEED)
Bekerja untuk saya, saya hanya harus menghapus semua entri pertama dari tabel, kemudian ditambahkan di atas di trigger point setelah menghapus. Sekarang setiap kali aku menghapus entri diambil dari sana.
Reset kolom identitas dengan id baru...
DECLARE @MAX INT
SELECT @MAX=ISNULL(MAX(Id),0) FROM [TestTable]
DBCC CHECKIDENT ('[TestTable]', RESEED,@MAX)
Memotong
meja lebih disukai karena itu membersihkan catatan, me-reset counter dan mengambil kembali ruang disk.
Hapus
dan CheckIdent
harus digunakan hanya di mana asing tombol mencegah anda dari truncating.
Menjalankan script ini untuk me-reset kolom identitas. Anda akan perlu untuk membuat dua perubahan. Mengganti tableXYZ dengan apapun tabel yang perlu anda update. Juga, nama kolom identitas perlu turun dari tabel temp. Ini seketika di atas meja dengan 35,000 baris & 3 kolom. Jelas, cadangan meja dan pertama kali mencoba ini 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
Gunakan prosedur ini disimpan:
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. Aku datang di sebuah perilaku aneh di sql server 2008 r2 yang anda harus menyadari.
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
Pertama pilih menghasilkan 0, Item 1
.
Yang kedua menghasilkan 1, Butir 1
. Jika anda menjalankan ulang setelah tabel dibuat selanjutnya nilai adalah 0. Jujur, saya tidak terkejut Microsoft tidak bisa mendapatkan hal ini benar. Saya menemukan hal itu karena saya memiliki sebuah file script yang memuat tabel referensi yang saya kadang-kadang berjalan setelah saya re-membuat tabel dan kadang-kadang ketika tabel sudah dibuat.
Untuk MENGHAPUS baris dan reset IDENTITAS menghitung, 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
Saya menggunakan script berikut untuk melakukan hal ini. Ada's hanya satu skenario di mana itu akan menghasilkan "kesalahan", yang jika anda telah menghapus semua baris dari tabel, dan IDENT_CURRENT
saat ini di set ke 1, yaitu hanya ada satu baris dalam tabel untuk mulai dengan.
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)
;
Selalu lebih baik untuk menggunakan MEMOTONG bila mungkin bukan menghapus semua catatan seperti itu doesn't menggunakan log ruang juga.
Dalam hal ini kita perlu menghapus dan perlu untuk me-reset benih, selalu ingat bahwa jika meja itu tidak pernah dihuni dan digunakan DBCC CHECKIDENT('tablenem',RESEED,0)
itulah catatan pertama akan mendapatkan identitas = 0
seperti yang disebutkan pada msdn dokumentasi
Dalam kasus anda hanya membangun kembali indeks dan don't khawatir tentang kehilangan seri identitas seperti ini adalah skenario umum.