Đặt lại hạt giống danh tính sau khi xóa bản ghi trong SQL Server

735
xorpower 2014-02-17 22:51.

Tôi đã chèn các bản ghi vào bảng cơ sở dữ liệu SQL Server. Bảng đã xác định một khóa chính và hạt giống nhận dạng tự động tăng được đặt thành “Có”. Điều này được thực hiện chủ yếu vì trong SQL Azure, mỗi bảng phải có khóa chính và danh tính được xác định.

Nhưng vì tôi phải xóa một số bản ghi khỏi bảng, hạt giống danh tính cho các bảng đó sẽ bị xáo trộn và cột chỉ mục (được tạo tự động với số gia là 1) sẽ bị xáo trộn.

Làm cách nào để đặt lại cột nhận dạng sau khi tôi xóa các bản ghi để cột có thứ tự theo số tăng dần?

Cột nhận dạng không được sử dụng làm khóa ngoại ở bất kỳ đâu trong cơ sở dữ liệu.

18 answers

1175
Petr Abdulin 2014-02-17 23:04.

Lệnh DBCC CHECKIDENTquản lý được sử dụng để thiết lập lại bộ đếm danh tính. Cú pháp lệnh là:

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

Thí dụ:

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

Nó không được hỗ trợ trong các phiên bản trước của Cơ sở dữ liệu Azure SQL nhưng hiện đã được hỗ trợ.


Cảm ơn Đặt lại hạt giống danh tính sau khi xóa bản ghi trong SQL Server các tài liệu cho lệnh hiện đã được sửa.

231
anil shah 2014-02-17 22:59.
DBCC CHECKIDENT ('TestTable', RESEED, 0)
GO

Trong đó 0 là identitygiá trị Bắt đầu

96
Solomon Rutzky 2014-12-06 08:15.

Cần lưu ý rằng NẾU tất cả dữ liệu đang bị xóa khỏi bảng thông qua DELETE(nghĩa là không có WHEREmệnh đề), thì miễn là a) quyền cho phép nó, và b) không có FK nào tham chiếu đến bảng (có vẻ như trường hợp ở đây), sử dụng TRUNCATE TABLEsẽ được ưu tiên hơn vì nó hiệu quả hơn DELETE đặt lại IDENTITYhạt giống cùng một lúc. Các chi tiết sau được lấy từ trang MSDN cho BẢNG TRUNCATE :

So với câu lệnh DELETE, TRUNCATE TABLE có những ưu điểm sau:

  • Ít không gian nhật ký giao dịch được sử dụng.

    Câu lệnh DELETE xóa từng hàng một và ghi lại một mục nhập trong nhật ký giao dịch cho mỗi hàng đã xóa. TRUNCATE TABLE xóa dữ liệu bằng cách phân bổ các trang dữ liệu được sử dụng để lưu trữ dữ liệu bảng và chỉ ghi lại các phân bổ trang trong nhật ký giao dịch.

  • Thường ít khóa hơn được sử dụng.

    Khi câu lệnh DELETE được thực hiện bằng cách sử dụng khóa hàng, mỗi hàng trong bảng sẽ bị khóa để xóa. TRUNCATE TABLE luôn khóa bảng (bao gồm khóa lược đồ (SCH-M)) và trang nhưng không khóa từng hàng.

  • Không có ngoại lệ, không có trang nào được để lại trong bảng.

    Sau khi câu lệnh DELETE được thực thi, bảng vẫn có thể chứa các trang trống. Ví dụ: không thể phân bổ các trang trống trong một đống mà không có ít nhất một khóa bảng (LCK_M_X) độc quyền. Nếu thao tác xóa không sử dụng khóa bảng, bảng (heap) sẽ chứa nhiều trang trống. Đối với các chỉ mục, thao tác xóa có thể để lại các trang trống, mặc dù các trang này sẽ được xử lý nhanh chóng bằng quy trình dọn dẹp nền.

Nếu bảng chứa một cột nhận dạng, bộ đếm cho cột đó được đặt lại thành giá trị gốc được xác định cho cột. Nếu không có hạt giống nào được xác định, giá trị mặc định 1 được sử dụng. Để giữ lại bộ đếm danh tính, hãy sử dụng DELETE thay thế.

Vì vậy, như sau:

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

Chỉ trở thành:

TRUNCATE TABLE [MyTable];

Vui lòng xem TRUNCATE TABLEtài liệu (được liên kết ở trên) để biết thêm thông tin về các hạn chế, v.v.

91
Atal Kishore 2016-05-08 10:36.

Mặc dù hầu hết các câu trả lời đều gợi ý RESEED về 0, nhưng nhiều khi chúng ta chỉ cần gửi lại Id tiếp theo có sẵn

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)

Thao tác này sẽ kiểm tra bảng và đặt lại thành ID tiếp theo.

64
Mikael Engver 2014-09-11 23:34.

Tôi đã thử @anil shahstrả lời và nó đặt lại danh tính. Nhưng khi một hàng mới được chèn vào thì nó có identity = 2. Vì vậy, thay vào đó, tôi đã thay đổi cú pháp thành:

DELETE FROM [TestTable]

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

Sau đó, hàng đầu tiên sẽ nhận được danh tính = 1.

17
RealSollyM 2015-08-11 23:47.

Mặc dù hầu hết câu trả lời đang đề xuất RESEEDđể 0, và trong khi một số coi đây là một lỗ hổng cho TRUNCATEDbảng, Microsoft đã có một giải pháp mà không bao gồm cácID

DBCC CHECKIDENT ('[TestTable]', RESEED)

Thao tác này sẽ kiểm tra bảng và đặt lại bảng tiếp theo ID. Điều này đã có sẵn kể từ MS SQL 2005 đến nay.

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

9
jacob 2016-07-12 21:57.

ban hành 2 lệnh có thể thực hiện thủ thuật

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

lần đầu tiên đặt lại danh tính về 0 và lần tiếp theo sẽ đặt nó thành giá trị có sẵn tiếp theo - jacob

7
epic 2016-12-07 01:46.

@jacob

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

Làm việc cho tôi, trước tiên tôi phải xóa tất cả các mục nhập khỏi bảng, sau đó thêm các mục ở trên vào một điểm kích hoạt sau khi xóa. Bây giờ bất cứ khi nào tôi xóa một mục nhập được lấy từ đó.

7
Dyna Dave 2017-04-20 06:31.

Truncate bảng được ưu tiên vì nó xóa các bản ghi, đặt lại bộ đếm và lấy lại không gian đĩa.

DeleteCheckIdentchỉ nên được sử dụng khi khóa ngoại ngăn bạn cắt bớt.

5
Mukesh Pandey 2017-03-30 20:00.

Đặt lại cột nhận dạng với id mới ...

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

DBCC CHECKIDENT ('[TestTable]', RESEED,@MAX)
3
Ben Thul 2014-02-18 05:00.

Đây là một câu hỏi phổ biến và câu trả lời luôn giống nhau: đừng làm điều đó. Các giá trị nhận dạng phải được coi là tùy ý và như vậy, không có thứ tự "đúng".

3
Matthew Baic 2014-12-06 05:02.

Chạy tập lệnh này để đặt lại cột danh tính. Bạn sẽ cần thực hiện hai thay đổi. Thay thế tableXYZ bằng bất kỳ bảng nào bạn cần cập nhật. Ngoài ra, tên của cột nhận dạng cần được xóa khỏi bảng tạm thời. Điều này xảy ra ngay lập tức trên một bảng có 35.000 hàng & 3 cột. Rõ ràng, hãy sao lưu bảng và lần đầu tiên thử điều này trong môi trường thử nghiệm.


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
Chris Mack 2019-01-06 12:31.

Tôi sử dụng tập lệnh sau để làm điều này. Chỉ có một trường hợp mà nó sẽ tạo ra "lỗi", đó là nếu bạn đã xóa tất cả các hàng khỏi bảng và IDENT_CURRENThiện được đặt thành 1, tức là chỉ có một hàng trong bảng để bắt đầu.

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)
;
1
Bimzee 2016-05-02 21:11.
DBCC CHECKIDENT (<TableName>, reseed, 0)

Điều này sẽ đặt giá trị nhận dạng hiện tại thành 0.

Khi chèn giá trị tiếp theo, giá trị nhận dạng được tăng lên 1.

1
costa 2017-10-05 10:11.

Sử dụng quy trình được lưu trữ này:

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'

Chỉ cần xem lại câu trả lời của tôi. Tôi đã gặp một hành vi kỳ lạ trong sql server 2008 r2 mà bạn nên biết.

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

Lựa chọn đầu tiên sản xuất 0, Item 1.

Cái thứ hai sản xuất 1, Item 1. Nếu bạn thực hiện thiết lập lại ngay sau khi bảng được tạo, giá trị tiếp theo là 0. Thành thật mà nói, tôi không ngạc nhiên khi Microsoft không thể làm đúng công cụ này. Tôi phát hiện ra nó vì tôi có một tệp tập lệnh chứa các bảng tham chiếu mà đôi khi tôi chạy sau khi tạo lại bảng và đôi khi khi các bảng đã được tạo.

1
KimvdLinde 2020-04-28 04:42.

Tôi đã cố gắng hoàn thành việc này cho một số lượng lớn các bảng trong quá trình phát triển và điều này hoạt động như một sự quyến rũ.

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

Vì vậy, trước tiên bạn buộc nó phải được đặt thành 1, sau đó bạn đặt nó thành chỉ số cao nhất của các hàng có trong bảng. Phần còn lại của Idex nhanh chóng và dễ dàng.

0
Fandango68 2018-05-23 19:16.

Để có hàng DELETE hoàn chỉnh và đặt lại số IDENTITY, tôi sử dụng điều này (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
Ali Sufyan 2019-07-03 19:16.

Gửi lại về 0 không thực tế lắm trừ khi bạn đang dọn dẹp toàn bộ bảng.

khôn ngoan khác, câu trả lời do Anthony Raymond đưa ra là hoàn hảo. Lấy giá trị tối đa của cột nhận dạng trước, sau đó chọn cột tối đa

Related questions

MORE COOL STUFF

Cate Blanchett chia tay chồng sau 3 ngày bên nhau và vẫn kết hôn với anh ấy 25 năm sau

Cate Blanchett chia tay chồng sau 3 ngày bên nhau và vẫn kết hôn với anh ấy 25 năm sau

Cate Blanchett đã bất chấp những lời khuyên hẹn hò điển hình khi cô gặp chồng mình.

Tại sao Michael Sheen là một diễn viên phi lợi nhuận

Tại sao Michael Sheen là một diễn viên phi lợi nhuận

Michael Sheen là một diễn viên phi lợi nhuận nhưng chính xác thì điều đó có nghĩa là gì?

Hallmark Star Colin Egglesfield Các món ăn gây xúc động mạnh đối với người hâm mộ tại RomaDrama Live! [Loại trừ]

Hallmark Star Colin Egglesfield Các món ăn gây xúc động mạnh đối với người hâm mộ tại RomaDrama Live! [Loại trừ]

Ngôi sao của Hallmark Colin Egglesfield chia sẻ về những cuộc gặp gỡ với người hâm mộ ly kỳ tại RomaDrama Live! cộng với chương trình INSPIRE của anh ấy tại đại hội.

Tại sao bạn không thể phát trực tuyến 'chương trình truyền hình phía Bắc'

Tại sao bạn không thể phát trực tuyến 'chương trình truyền hình phía Bắc'

Bạn sẽ phải phủi sạch đầu đĩa Blu-ray hoặc DVD để xem tại sao Northern Exposure trở thành một trong những chương trình nổi tiếng nhất của thập niên 90.

Where in the World Are You? Take our GeoGuesser Quiz

Where in the World Are You? Take our GeoGuesser Quiz

The world is a huge place, yet some GeoGuessr players know locations in mere seconds. Are you one of GeoGuessr's gifted elite? Take our quiz to find out!

8 công dụng tuyệt vời của Baking Soda và Giấm

8 công dụng tuyệt vời của Baking Soda và Giấm

Bạn biết đấy, hai sản phẩm này là nguồn điện để làm sạch, riêng chúng. Nhưng cùng với nhau, chúng có một loạt công dụng hoàn toàn khác.

Hạn hán, biến đổi khí hậu đe dọa tương lai của thủy điện Hoa Kỳ

Hạn hán, biến đổi khí hậu đe dọa tương lai của thủy điện Hoa Kỳ

Thủy điện rất cần thiết cho lưới điện của Hoa Kỳ, nhưng nó chỉ tạo ra năng lượng khi có nước di chuyển. Bao nhiêu nhà máy thủy điện có thể gặp nguy hiểm khi các hồ và sông cạn kiệt?

Quyên góp tóc của bạn để giúp giữ nước sạch của chúng tôi

Quyên góp tóc của bạn để giúp giữ nước sạch của chúng tôi

Tóc tỉa từ các tiệm và các khoản quyên góp cá nhân có thể được tái sử dụng như những tấm thảm thấm dầu và giúp bảo vệ môi trường.

Làm thế nào để xây dựng một quả địa cầu từ Scratch

Làm thế nào để xây dựng một quả địa cầu từ Scratch

Thời gian, sự kiên nhẫn, thời gian, sự cống hiến và thời gian chỉ là một vài trong số những thứ mà Peter Bellerby cần để thành lập và sau đó điều hành Bellerby & Co. Globemakers, một trong những công ty duy nhất trên Trái đất vẫn sản xuất các quả địa cầu bằng tay.

Năm giai đoạn đau buồn sau khi mất việc làm

Năm giai đoạn đau buồn sau khi mất việc làm

Đó là một ngày thứ Bảy, máy bay của tôi hạ cánh, và tôi đã sẵn sàng để thư giãn trong một kỳ nghỉ cuối tuần ngắn ngủi, khi một email đến trên điện thoại của tôi. Tôi đã mất việc.

Giữ an toàn cho danh tính của bạn với một cảnh báo đóng băng hoặc gian lận tín dụng

Giữ an toàn cho danh tính của bạn với một cảnh báo đóng băng hoặc gian lận tín dụng

Nếu bạn đã từng bị đánh cắp danh tính của mình, bạn biết đó là một trải nghiệm đáng sợ và căng thẳng. Một cách không phổ biến để ngăn chặn nó? Tín dụng bị đóng băng.

Buổi ra mắt phần 3 của The Good Place có một học sinh mới: Khán giả

Buổi ra mắt phần 3 của The Good Place có một học sinh mới: Khán giả

Eleanor (Kristen Bell) dường như đã tình nguyện cho chúng tôi làm vật tưởng nhớ. NBC's The Good Place là một chương trình thích thử thách tốt.

Nicky Hilton Forced to Borrow Paris' 'I Love Paris' Sweatshirt After 'Airline Loses All [My] Luggage'

Nicky Hilton Forced to Borrow Paris' 'I Love Paris' Sweatshirt After 'Airline Loses All [My] Luggage'

Nicky Hilton Rothschild's luggage got lost, but luckily she has an incredible closet to shop: Sister Paris Hilton's!

Kate Middleton dành một ngày bên bờ nước ở London, cùng với Jennifer Lopez, Julianne Hough và hơn thế nữa

Kate Middleton dành một ngày bên bờ nước ở London, cùng với Jennifer Lopez, Julianne Hough và hơn thế nữa

Kate Middleton dành một ngày bên bờ nước ở London, cùng với Jennifer Lopez, Julianne Hough và hơn thế nữa. Từ Hollywood đến New York và mọi nơi ở giữa, hãy xem các ngôi sao yêu thích của bạn đang làm gì!

17 tuổi bị đâm chết trong khi 4 người khác bị thương trong một cuộc tấn công bằng dao trên sông Wisconsin

17 tuổi bị đâm chết trong khi 4 người khác bị thương trong một cuộc tấn công bằng dao trên sông Wisconsin

Các nhà điều tra đang xem xét liệu nhóm và nghi phạm có biết nhau trước vụ tấn công hay không

Thanh thiếu niên, Gia đình Florida Hội đồng quản trị trường học về Luật 'Không nói đồng tính': 'Buộc chúng tôi tự kiểm duyệt'

Thanh thiếu niên, Gia đình Florida Hội đồng quản trị trường học về Luật 'Không nói đồng tính': 'Buộc chúng tôi tự kiểm duyệt'

Vụ kiện, nêu tên một số học khu, lập luận rằng dự luật "Không nói đồng tính" được ban hành gần đây của Florida "có hiệu quả im lặng và xóa bỏ học sinh và gia đình LGBTQ +"

Đường băng hạ cánh

Đường băng hạ cánh

Cuối hè đầu thu là mùa hoài niệm. Những chiếc đèn đường chiếu ánh sáng của chúng qua những con đường đẫm mưa, và những chiếc lá dưới chân - màu đỏ cam tắt trong bóng chạng vạng - là lời nhắc nhở về những ngày đã qua.

Hãy tưởng tượng tạo ra một chiến lược nội dung thực sự CHUYỂN ĐỔI. Nó có thể.

Hãy tưởng tượng tạo ra một chiến lược nội dung thực sự CHUYỂN ĐỔI. Nó có thể.

Vào năm 2021, tôi khuyến khích bạn suy nghĩ lại mọi thứ bạn biết về khách hàng mà bạn phục vụ và những câu chuyện bạn kể cho họ. Lùi lại.

Sự mất mát của voi ma mút đã mở ra trái tim tôi để yêu

Sự mất mát của voi ma mút đã mở ra trái tim tôi để yêu

Vào ngày sinh nhật thứ 9 của Felix The Cat, tôi nhớ về một trong những mất mát lớn nhất trong cuộc đời trưởng thành của tôi - Sophie của tôi vào năm 2013. Tôi đã viết bài luận này và chia sẻ nó trên nền tảng này một thời gian ngắn vào năm 2013.

Khi bạn không thể trở thành người mà Internet muốn bạn trở thành

Khi bạn không thể trở thành người mà Internet muốn bạn trở thành

Tôi ghét từ "tàu đắm". Mọi người cảm thấy thoải mái trong la bàn đạo đức của riêng mình, và khi làm như vậy, họ thấy mình vượt qua sự phán xét.

Language