Я вставил записи в таблицу базы данных SQL Server. В таблице был определен первичный ключ, а для параметра auto increment identity seed установлено значение "Yes". Это сделано в первую очередь потому, что в SQL Azure каждая таблица должна иметь первичный ключ и идентификатор.
Но поскольку мне нужно удалить некоторые записи из таблицы, семя идентичности для этих таблиц будет нарушено, и столбец индекса (который автоматически генерируется с инкрементом 1) будет нарушен.
Как я могу сбросить столбец identity после удаления записей, чтобы столбец имел последовательность в возрастающем числовом порядке?.
Столбец identity не используется в качестве внешнего ключа нигде в базе данных.
Команда управления DBCC CHECKIDENT
используется для сброса счетчика идентификации. Синтаксис команды следующий:
DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])
[ WITH NO_INFOMSGS ]
Пример:
DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO
Он не поддерживался в предыдущих версиях Azure SQL Database, но теперь поддерживается.
Обратите внимание, что аргумент new_reseed_value
различается в разных версиях SQL Server согласно документации:
Если в таблице присутствуют строки, то следующая строка вставляется со значением new_reseed_value. В версии SQL Server 2008 R2 и более ранних, для вставки следующей строки используется new_reseed_value + текущее значение инкремента.
Однако я считаю эту информацию вводящей в заблуждение (на самом деле просто неверной), поскольку наблюдаемое поведение показывает, что, по крайней мере, SQL Server 2012 все еще использует логику new_reseed_value + текущее значение инкремента. Microsoft даже противоречит собственному примеру C
, расположенному на той же странице:
C. Принуждение текущего значения идентификации к новому значению
Следующий пример заставляет текущее значение идентификатора в файле столбца AddressTypeID в таблице AddressType к значению 10. Поскольку таблица имеет существующие строки, следующая вставленная строка будет использовать 11 в качестве значения, то есть новое текущее значение инкремента, определенное для столбца > AddressTypeID плюс 1. значения столбца плюс 1.
USE AdventureWorks2012;
GO
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);
GO
Тем не менее, все это оставляет возможность иного поведения на более новых версиях SQL Server. Я полагаю, что единственный способ быть уверенным, пока Microsoft не прояснит ситуацию в своей документации, - это провести реальные тесты перед использованием.
Следует отметить, что если все данные будут удалены из таблицы через "удалить" (т. е. не " где "оговорка), то а) если полномочия позволяют, и Б) нет никаких ФКС ссылается на таблицу (которая, как представляется, в данном случае), с помощью усечения таблицы будет предпочтительным, поскольку оно делает более эффективной "удалить" и сбрасывает "личность" семя одновременно. Следующие данные взяты из страницы MSDN для усечения таблицы:
По сравнению с Delete, инструкция truncate table обладает следующими преимуществами:
использованный * меньший объем журнала транзакций.
оператор delete удаляет строки по одному и заносит запись в журнал транзакций для каждой удаляемой строки. Инструкция truncate table удаляет данные, освобождая страницы данных, используемые для хранения данных, таблицы и записи только освобождение страниц в журнале транзакций.
- меньшее количество блокировок обычно используются.
если инструкция delete выполняется с блокировкой строк, каждая строка в таблице заблокированы для удаления. Инструкция truncate table всегда блокирует таблицу (включая схемы (SCH-M) и блокировка) и страницы, но не каждую строку.
- без исключения, ноль страниц в таблице.
после выполнения инструкции delete, таблица может содержать пустые страницы. Например, пустые страницы в кучу не могут быть освобождены, по крайней мере без исключительной (LCK_M_X) блокировку таблицы. Если после операции удаления не использовать блокировку таблицы, таблица (куча) будет содержать множество пустых страниц. Для индексов, операция удаления может оставлять пустые страницы, хотя эти страницы будут быстро освобождены процесс очистки фона.
если таблица содержит столбец идентификаторов, счетчик этого столбца сбрасывается до начального значения, определенного для данного столбца. Если нет семени, было определено значение по умолчанию 1 используется. Сохранить счетчик личности, использовать удалить.
Таким образом, следующий:
DELETE FROM [MyTable];
DBCC CHECKIDENT ('[MyTable]', RESEED, 0);
Становится просто:
TRUNCATE TABLE [MyTable];
Пожалуйста, ознакомьтесь с документацией усечь таблицу
(ссылка выше) для получения дополнительной информации об ограничениях и т. д.
Хотя большинство ответов предполагают заполнение к 0, но много раз нужно просто заполнить в следующий идентификационный номер
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)
Это позволит проверить таблицу и сбросьте на следующий ИД.
Я попробовал @Анил Шахам ответа и сброса личность. Но при вставке новой строки он есть личность = 2
. Так что вместо этого я изменил синтаксис:
DELETE FROM [TestTable]
DBCC CHECKIDENT ('[TestTable]', RESEED, 0)
GO
Тогда первый ряд будет тож = 1.
Хотя большинство ответов предполагая, что заполнение
на 0
, и в то время как некоторые видят в этом недостаток для усе
таблиц, Microsoft имеет решение, которое исключает идентификатор
DBCC CHECKIDENT ('[TestTable]', RESEED)
Это позволит проверить таблицу и сбросьте на следующий идентификатор
. Это было доступно начиная с MS SQL 2005, на ток.
@Иаков
DBCC CHECKIDENT ('[TestTable]', RESEED,0)
DBCC CHECKIDENT ('[TestTable]', RESEED)
Работал для меня, я просто должен был очистить все записи из таблицы, потом добавил выше в триггерную после удаления. Теперь, когда я удалить запись взята именно оттуда.
Сброс столбец Identity с новым ID...
DECLARE @MAX INT
SELECT @MAX=ISNULL(MAX(Id),0) FROM [TestTable]
DBCC CHECKIDENT ('[TestTable]', RESEED,@MAX)
Усечение
таблица является предпочтительным, поскольку он очищает записи, сбрасывает счетчик и восстанавливает дисковое пространство.
Удалить
и `checkident, будет должны использоваться только там, где внешние ключи помешать вам усек.
Запустите этот скрипт, чтобы сбросить столбец Identity. Вам нужно внести два изменения. Заменить tableXYZ с какой бы стол вам нужно обновить. Кроме того, имя тож нужен столбец исключен из временной таблицы. Это был мгновенный на столе с 35000 строк & 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
Использовать эту хранимую процедуру:
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'
Просто пересмотреть мой ответ. Я наткнулся на странное поведение в SQL сервер 2008 R2, которые вы должны знать.
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, пункт 1
.
Второй производит 1, пункт 1
. Если вы выполните сброс сразу после таблица создается следующим значением является 0. Честно говоря, я не удивлен, корпорация Майкрософт не может сделать это прямо. Я обнаружил, что это потому что у меня есть файл скрипта, который заполняет справочные таблицы, что я иногда бегаю после того, как я повторно создавать таблицы и иногда, когда таблицы уже созданы.
Подсев к 0-это не очень практично, если вы очистка всей таблицы.
другой мудрый ответ дал Энтони Раймонд идеально подходит. Получите максимум из столбца идентификаторов сначала, потом семя его с Максом.
Для полного удаления строк и сбросить счетчик тож, я использую этот (SQL сервер 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
Я использую следующий скрипт, чтобы сделать это. Там's только один сценарий, в котором он будет производить "по ошибке", что если вы удалили все строки из таблицы, и функция ident_current в настоящее время установлен в 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, будет('tablenem',заполнение,0)
тогда первая запись будет тож = 0
как указано в документации MSDN
В вашем случае только перестроение и Дон'т беспокоиться о потере серия идентичности-это обычный сценарий.
Во-первых : спецификация тож просто : "Нет" >> сохранить проект базы данных выполнить
После то : спецификация тож просто : "Да" и >> сохранить проект базы данных выполнить
Идентификатор базы данных, ПК "старт" с 1 >>