بهینه سازی مدیریت IDENTITY در SQL Server راهنمای بازیابی و پر کردن شکاف ها

بهینه‌سازی مدیریت IDENTITY در SQL Server: راهنمای بازیابی و استفاده مجدد

در محیط‌های توسعه و تست SQL Server، اغلب با نیاز به استفاده مجدد از مقادیر IDENTITY (شناسه‌های خودکار) پس از حذف ردیف‌ها مواجه می‌شویم. این امر به ویژه زمانی اهمیت پیدا می‌کند که ما می‌خواهیم شمارنده‌های IDENTITY را بازنشانی کنیم یا ردیف‌های حذف شده را با شناسه‌های قبلی‌شان پر کنیم. در ادامه، به بررسی چگونگی مدیریت و بازیابی IDENTITY در سناریوهای مختلف می‌پردازیم.

یکی از رایج‌ترین عملیات حذف داده، دستور `DELETE` است. هنگامی که شما ردیف‌هایی را با استفاده از این دستور حذف می‌کنید، شمارنده IDENTITY برای جدول به طور پیش‌فرض به جلو حرکت می‌کند و مقادیر حذف شده قابل استفاده مجدد نخواهند بود، مگر اینکه به صورت دستی تنظیم شوند.

مثال زیر نحوه عملکرد `DELETE` را نشان می‌دهد. ابتدا جدولی ایجاد کرده، سپس چند ردیف اضافه کرده و تعدادی را حذف می‌کنیم.

IF OBJECT_ID('tempdb..#identityTest') IS NOT NULL DROP TABLE #identityTest
GO
CREATE TABLE #identityTest (id INT IDENTITY(1,1), someValue VARCHAR(50))
GO
INSERT INTO #identityTest VALUES ('Value 1')
INSERT INTO #identityTest VALUES ('Value 2')
INSERT INTO #identityTest VALUES ('Value 3')
INSERT INTO #identityTest VALUES ('Value 4')
INSERT INTO #identityTest VALUES ('Value 5')
SELECT * FROM #identityTest
GO
DELETE FROM #identityTest WHERE id = 3
GO
INSERT INTO #identityTest VALUES ('Value 6')
SELECT * FROM #identityTest
GO

همانطور که می‌بینید، پس از حذف ردیف با `id = 3` و درج مجدد، `id` بعدی از 6 شروع می‌شود و `id = 3` خالی می‌ماند.

بررسی وضعیت IDENTITY

برای بررسی مقدار جاری IDENTITY در یک جدول، از دستور `DBCC CHECKIDENT` استفاده می‌کنیم. این دستور به شما نشان می‌دهد که شمارنده IDENTITY در حال حاضر روی چه عددی تنظیم شده است.

DBCC CHECKIDENT('#identityTest')

خروجی این دستور شامل `current identity value` (آخرین مقدار استفاده شده) و `current column value` (مقدار فعلی که برای درج بعدی استفاده خواهد شد) است.

بازنشانی IDENTITY با DBCC CHECKIDENT

در صورتی که بخواهید شمارنده IDENTITY را به مقدار خاصی بازنشانی کنید، می‌توانید از `DBCC CHECKIDENT` به همراه گزینه `RESEED` استفاده کنید. این کار به شما امکان می‌دهد تا مقادیر قبلی حذف شده را بازیابی کنید.

برای مثال، اگر می‌خواهید مقدار IDENTITY را به 2 بازنشانی کنید تا ردیف بعدی با `id = 3` درج شود:

DBCC CHECKIDENT('#identityTest', RESEED, 2)
GO
INSERT INTO #identityTest VALUES ('Value 3 Again')
SELECT * FROM #identityTest
GO

این کار باعث می‌شود که IDENTITY بعدی از 3 شروع شود. دقت کنید که این مقدار باید بزرگتر از بالاترین `id` موجود در جدول نباشد، مگر اینکه قصد داشته باشید مقادیر تکراری ایجاد کنید که معمولاً مطلوب نیست.

استفاده از TRUNCATE TABLE

دستور `TRUNCATE TABLE` برای حذف تمام ردیف‌ها از یک جدول استفاده می‌شود و برخلاف `DELETE`، شمارنده IDENTITY را به مقدار اولیه خود (معمولاً 1) بازنشانی می‌کند. این روش بسیار سریع‌تر از `DELETE` است، به خصوص برای جداول بزرگ.

مثال زیر نحوه عملکرد `TRUNCATE TABLE` را نشان می‌دهد:

TRUNCATE TABLE #identityTest
GO
INSERT INTO #identityTest VALUES ('Value 1 after truncate')
SELECT * FROM #identityTest
GO

پس از `TRUNCATE`، ردیف‌های جدید از `id = 1` شروع می‌شوند.

فعال کردن IDENTITY_INSERT برای درج دستی شناسه‌ها

اگر نیاز دارید که مقادیر خاصی از IDENTITY را به صورت دستی درج کنید، باید گزینه `SET IDENTITY_INSERT` را برای جدول مورد نظر `ON` کنید. این گزینه به شما امکان می‌دهد تا مقادیر دلخواه را در ستون IDENTITY وارد کنید، حتی اگر آن ستون به صورت خودکار افزایش یابد.

این قابلیت زمانی مفید است که شما بخواهید یک ردیف حذف شده را دقیقاً با همان `id` قبلی‌اش بازیابی کنید یا داده‌ها را از یک منبع دیگر با حفظ شناسه‌های اصلی‌شان وارد کنید.

SET IDENTITY_INSERT #identityTest ON
GO
INSERT INTO #identityTest (id, someValue) VALUES (3, 'Value 3 Reinserted')
GO
SET IDENTITY_INSERT #identityTest OFF
GO
SELECT * FROM #identityTest
GO
```

مهم: پس از اتمام درج دستی، حتماً `SET IDENTITY_INSERT` را `OFF` کنید تا از بروز خطاهای احتمالی یا درج ناخواسته مقادیر تکراری جلوگیری شود.

رویه ذخیره شده برای پر کردن شکاف‌های IDENTITY

یکی از راه‌حل‌های پیشرفته‌تر برای مدیریت IDENTITY، ایجاد یک رویه ذخیره شده (Stored Procedure) است که به طور خودکار شکاف‌های موجود در ستون IDENTITY را شناسایی کرده و پر می‌کند. این رویه می‌تواند برای جداول مشخصی پیکربندی شود و به حفظ پیوستگی داده‌ها کمک کند.

مثال زیر یک رویه ذخیره شده را نشان می‌دهد که برای پر کردن شکاف‌های IDENTITY در جدول `#identityTest` طراحی شده است:

IF OBJECT_ID('tempdb..#identityTest') IS NOT NULL DROP TABLE #identityTest
GO
CREATE TABLE #identityTest (id INT IDENTITY(1,1), someValue VARCHAR(50))
GO
INSERT INTO #identityTest VALUES ('Value 1')
INSERT INTO #identityTest VALUES ('Value 2')
INSERT INTO #identityTest VALUES ('Value 3')
INSERT INTO #identityTest VALUES ('Value 4')
INSERT INTO #identityTest VALUES ('Value 5')
GO
DELETE FROM #identityTest WHERE id IN (1,3,5)
GO
SELECT * FROM #identityTest ORDER BY id
GO
CREATE PROCEDURE dbo.sp_ReseedIdentity
    @TableName VARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @MinGapId INT;
    DECLARE @SchemaName VARCHAR(128);
    DECLARE @IdentityColumnName VARCHAR(128);

    SELECT
        @SchemaName = OBJECT_SCHEMA_NAME(OBJECT_ID(@TableName)),
        @IdentityColumnName = c.name
    FROM sys.tables t
    JOIN sys.columns c ON t.object_id = c.object_id
    WHERE t.object_id = OBJECT_ID(@TableName) AND c.is_identity = 1;

    IF @IdentityColumnName IS NULL
    BEGIN
        PRINT 'Table does not have an IDENTITY column or does not exist.';
        RETURN;
    END

    SET @SQL = N'
        SELECT TOP 1 @MinGapId = t1.' + @IdentityColumnName + ' + 1
        FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' AS t1
        WHERE NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' AS t2 WHERE t2.' + @IdentityColumnName + ' = t1.' + @IdentityColumnName + ' + 1)
        ORDER BY t1.' + @IdentityColumnName + ';';

    EXEC sp_executesql @SQL, N'@MinGapId INT OUTPUT', @MinGapId OUTPUT;

    IF @MinGapId IS NOT NULL
    BEGIN
        SET @SQL = N'DBCC CHECKIDENT(' + QUOTENAME(@TableName, '''') + ', RESEED, ' + CAST(@MinGapId - 1 AS VARCHAR(20)) + ');';
        EXEC sp_executesql @SQL;
        PRINT 'IDENTITY reseeded to ' + CAST(@MinGapId - 1 AS VARCHAR(20)) + '. Next IDENTITY value will be ' + CAST(@MinGapId AS VARCHAR(20)) + '.';
    END
    ELSE
    BEGIN
        PRINT 'No gaps found or table is empty.';
    END
END;
GO
EXEC dbo.sp_ReseedIdentity '#identityTest'
GO
INSERT INTO #identityTest VALUES ('Reseeding 1')
INSERT INTO #identityTest VALUES ('Reseeding 2')
INSERT INTO #identityTest VALUES ('Reseeding 3')
GO
SELECT * FROM #identityTest ORDER BY id
GO

این رویه `sp_ReseedIdentity` کوچکترین شکاف موجود در ستون IDENTITY را پیدا کرده و شمارنده را به گونه‌ای بازنشانی می‌کند که مقدار بعدی، آن شکاف را پر کند. این یک راهکار منعطف برای مدیریت دقیق‌تر شناسه‌ها است، اما باید با احتیاط و آگاهی از ساختار داده‌های خود استفاده شود.

نتیجه‌گیری

مدیریت و استفاده مجدد از مقادیر IDENTITY در SQL Server ابزارهای قدرتمندی را برای توسعه‌دهندگان و مدیران پایگاه داده فراهم می‌کند. از دستورات ساده `DELETE` و `TRUNCATE TABLE` گرفته تا `DBCC CHECKIDENT` و حتی رویه‌های ذخیره شده سفارشی برای پر کردن شکاف‌ها، هر یک کاربرد خاص خود را دارند. انتخاب روش مناسب بستگی به نیازهای خاص برنامه و محیط شما دارد، اما با درک صحیح این ابزارها می‌توانید بهینه‌ترین راهکار را برای مدیریت شناسه در جداول خود پیاده‌سازی کنید. همواره به یاد داشته باشید که پشتیبان‌گیری از داده‌ها قبل از انجام عملیات حساس بر روی IDENTITY از اهمیت بالایی برخوردار است.