Команда DBCC не работает внутри процедуры

У меня есть запрос ниже. По логике, процедура usp_mytran должна ПЕРЕЗАГРУЗИТЬ идентификатор на 1 для таблицы dbo.Sales. Но последний запрос возвращает разные значения для Max_ID_Value и Current_Seed_Value. Кто-нибудь может объяснить, почему команда DBCC не работает внутри процедуры?

USE tempdb

--  Create table 
CREATE  TABLE dbo.Sales
(ID INT IDENTITY(1,1), Address VARCHAR(200))
GO

--  Procedure to Populate data into dbo.Sales
CREATE PROCEDURE usp_mytran 
AS
BEGIN
BEGIN TRANSACTION
    INSERT dbo.Sales
            ( Address )
    VALUES  ( 'Dwarka, Delhi' );

    --  Delete it for some reason
    DELETE FROM dbo.Sales;

    --  Code to check max ID value, and verify it again IDENTITY SEED
    DECLARE @MaxValue INT = (SELECT ISNULL(MAX(ID),1) FROM dbo.Sales)
    IF @MaxValue IS NOT NULL AND @MaxValue <> IDENT_CURRENT('dbo.Sales')
        DBCC CHECKIDENT ( 'tempdb.dbo.Sales', RESEED, @MaxValue );
ROLLBACK TRANSACTION
END

--  Ideally, this should RESEED the Identity of dbo.Sales table.
EXEC usp_mytran

--  Max_ID_Value & Current_Seed_Value should be same
SELECT ISNULL(MAX(ID),1) AS Max_ID_Value, IDENT_CURRENT('dbo.Sales') AS Current_Seed_Value FROM dbo.Sales

sql-server stored-procedures tsql dbcc
person Ajay Dwivedi    schedule 15.10.2016    source источник
comment
Выполните процедуру usp_mytran несколько раз, чтобы отметить разницу в значениях Max_ID_Value и Current_Seed_Value.   -  person Ajay Dwivedi    schedule 15.10.2016

Ответы (2)


arrow_upward
1
arrow_downward

На самом деле внутри хранимой процедуры все работает так, как ожидалось: Rollback tran откатит значение checkident — именно это и происходит в коде.

--  Procedure to Populate data into dbo.Sales
alter PROCEDURE usp_mytran 
AS
BEGIN
    BEGIN TRANSACTION
    INSERT dbo.Sales
            ( Address )
    VALUES  ( 'Dwarka, Delhi' );

    --  Delete it for some reason
    DELETE FROM dbo.Sales;

    --  Code to check max ID value, and verify it again IDENTITY SEED
    DECLARE @MaxValue INT = (SELECT ISNULL(MAX(ID),1) FROM dbo.Sales)
    IF @MaxValue IS NOT NULL AND @MaxValue <> IDENT_CURRENT('test.dbo.Sales')
        DBCC CHECKIDENT ( 'test.dbo.Sales', RESEED, @MaxValue );
    ROLLBACK TRANSACTION
END

Если вы проверите ident_current сейчас, он показывает 2, а при следующем запуске показывает 4 и т. д. из-за ROLLBACK TRANSACTION

Теперь проверьте после многократного выполнения "EXEC usp_mytran"

select IDENT_CURRENT('test.dbo.Sales')

Вы увидите, что checkident не будет сброшен.

Если мы удалим эту транзакцию, то значение CHECKIDENT будет изменено на 1.

Прокомментированная транзакция ниже

--  Procedure to Populate data into dbo.Sales
alter PROCEDURE usp_mytran 
AS
BEGIN
    --BEGIN TRANSACTION
    INSERT dbo.Sales
            ( Address )
    VALUES  ( 'Dwarka, Delhi' );

    --  Delete it for some reason
    DELETE FROM dbo.Sales;

    --  Code to check max ID value, and verify it again IDENTITY SEED
    DECLARE @MaxValue INT = (SELECT ISNULL(MAX(ID),1) FROM dbo.Sales)
    IF @MaxValue IS NOT NULL AND @MaxValue <> IDENT_CURRENT('test.dbo.Sales')
        DBCC CHECKIDENT ( 'test.dbo.Sales', RESEED, @MaxValue );
    --ROLLBACK TRANSACTION
END

Теперь проверьте после многократного выполнения "EXEC usp_mytran"

select IDENT_CURRENT('test.dbo.Sales')

Вы увидите значение повторного заполнения как «1».

Проверьте также приведенный ниже пример...

Можем ли мы вернуться к исходное состояние после того, как мы использовали DBCC CHECKIDENT для перезапуска счетчика столбцов идентификаторов?

person Kannan Kandasamy    schedule 15.10.2016

arrow_upward
3
arrow_downward

Извините за ответ на мой собственный вопрос. Как указал @Kannan Kandasamy, это код ROLLBACK TRANSACTION, который возвращает работу, проделанную DBCC CHECKIDENT. Поэтому, чтобы заставить его работать, я создал задание с именем Reseed_Sales, содержащее код для RESEED Identity для таблицы dbo.Sales. Ниже приведен окончательный запрос для процедуры usp_mytran.

--  Procedure to Populate data into dbo.Sales
ALTER PROCEDURE usp_mytran 
AS
BEGIN
BEGIN TRANSACTION
    INSERT dbo.Sales
            ( Address )
    VALUES  ( 'Dwarka, Delhi' );

    --  Delete it for some reason
    DELETE FROM dbo.Sales;

    --  Code to check max ID value, and verify it again IDENTITY SEED
    DECLARE @MaxValue INT = (SELECT ISNULL(MAX(ID),1) FROM dbo.Sales)
    IF @MaxValue IS NOT NULL AND @MaxValue <> IDENT_CURRENT('dbo.Sales')
        EXEC msdb..sp_start_job @job_name = 'Reseed_Sales'
ROLLBACK TRANSACTION
END
person Ajay Dwivedi    schedule 15.10.2016
comment
Я не знаю, чего вы пытаетесь достичь, но использование идентичности не похоже на правильное решение. В вашем коде есть различные проблемы с параллелизмом. - person Martin Smith; 15.10.2016
comment
Привет @MartinSmith, пожалуйста, проверьте вопрос transactio/40014054#40014054">Счетчик ключа идентификации увеличивается на единицу, хотя он находится в TRY Catch, а транзакция откатывается? СУСУ 2008. Требование заключалось в следующем: если блок TRY завершается сбоем, то RESEED значение Identity в блоке CATCH и откат транзакции. Итак, это то, чего я пытаюсь достичь, сохраняя ROLLBACK TRANSACTION и внутри него привязывая RESEED к идентификатору, используя задание агента sql из процедуры. - person Ajay Dwivedi; 15.10.2016
comment
Если вам нужен столбец без пробелов, IDENTITY это не подходящий инструмент для этой работы. Вы должны понимать это, когда вам приходится делать сумасшедшие вещи, такие как создание заданий агента SQL внутри простой процедуры CRUD. При параллелизме вы, безусловно, обнаружите, что ваш подход все равно не работает. - person Martin Smith; 15.10.2016