آموزش یافتن آخرین مقدار Sequence در SQL Server

آموزش جامع: چگونه آخرین مقدار Sequence استفاده شده در SQL Server را بیابیم؟

معرفی Sequenceها در SQL Server 2012 بالاخره یک نوع داده خودافزایشی مناسب را ارائه داد که به یک جدول خاص وابسته نیست. اغلب با ستون Identity مقایسه می‌شود، اما Sequence بسیار انعطاف‌پذیرتر است و به شما امکان می‌دهد یک Sequence را یک بار ایجاد کرده و آن را در چندین جدول استفاده کنید، یا حتی در صورت نیاز مقدار Sequence را مجدداً استفاده کنید. می‌توانید یک Sequence را با هر مقداری افزایش دهید و می‌تواند صعودی یا نزولی شمارش کند.

چالش زمانی پیش می‌آید که می‌خواهید آخرین مقداری را که توسط یک Sequence استفاده شده است، تعیین کنید. با یک ستون Identity، گزینه‌های مختلفی مانند SCOPE_IDENTITY()، @@IDENTITY یا IDENT_CURRENT() برای بازیابی آخرین مقدار تولید شده در یک حوزه، جلسه یا برای یک جدول خاص دارید. با این حال، این توابع منحصراً برای ستون‌های Identity هستند و با Sequenceها کار نمی‌کنند. پس، چگونه آخرین مقدار استفاده شده توسط یک Sequence را پیدا می‌کنید؟

تابع NEXT VALUE FOR برای بازیابی مقدار بعدی در دسترس از یک Sequence استفاده می‌شود. به عنوان مثال:

SELECT NEXT VALUE FOR MySequence;

هنگامی که از NEXT VALUE FOR استفاده می‌کنید، SQL Server همچنین راهی برای دریافت مقدار فعلی برای یک Sequence در همان حوزه و جلسه ارائه می‌دهد. این کار با استفاده از تابع CURRENT VALUE FOR انجام می‌شود:

SELECT CURRENT VALUE FOR MySequence;

این تابع اگر بخواهید بلافاصله پس از تولید مقدار Sequence، آن را در یک جدول جداگانه یا برای اهداف حسابرسی استفاده کنید، حیاتی است. با این حال، یک محدودیت مهم وجود دارد: CURRENT VALUE FOR تنها در صورتی یک مقدار را برمی‌گرداند که NEXT VALUE FOR در حوزه و جلسه فعلی فراخوانی شده باشد. اگر سعی کنید CURRENT VALUE FOR را در یک جلسه جدید یا بدون فراخوانی قبلی NEXT VALUE FOR فراخوانی کنید، منجر به خطا خواهد شد.

این محدودیت به این معنی است که CURRENT VALUE FOR اغلب برای سناریوهایی که نیاز به یافتن آخرین مقدار جهانی استفاده شده توسط یک Sequence دارید، مناسب نیست؛ به عنوان مثال، اگر در حال ساخت یک ابزار گزارش‌دهی هستید یا نیاز به مقداردهی اولیه یک سیستم دیگر با آخرین مقدار Sequence شناخته شده دارید.

پس، چگونه آخرین مقداری را که واقعاً توسط یک Sequence به معنای کلی‌تر استفاده شده است، بدون اینکه به جلسه فعلی وابسته باشد، پیدا می‌کنید؟

پاسخ در نمای سیستمی sys.sequences نهفته است. این نما شامل فراداده (metadata) درباره تمام Sequenceهای تعریف شده در پایگاه داده فعلی است، از جمله ستونی به نام last_value.

بیایید با ایجاد یک Sequence ساده، این موضوع را نشان دهیم:

CREATE SEQUENCE MySequence
    START WITH 1
    INCREMENT BY 1;

اکنون، بیایید چند مقدار از آن دریافت کنیم:

SELECT NEXT VALUE FOR MySequence;
SELECT NEXT VALUE FOR MySequence;
SELECT NEXT VALUE FOR MySequence;

اگر اکنون sys.sequences را پرس و جو کنید، ستون last_value را به‌روز شده خواهید دید:

SELECT name, last_value, current_value
FROM sys.sequences
WHERE name = 'MySequence';

ستون last_value آخرین مقداری را که توسط Sequence تولید شده است، نشان می‌دهد.

ستون current_value مقداری را نشان می‌دهد که CURRENT VALUE FOR در صورت فراخوانی در همان حوزه، برمی‌گرداند. اگر NEXT VALUE FOR در حوزه فعلی فراخوانی نشده باشد، current_value مقدار NULL خواهد بود.

برای دسترسی آسان به این قابلیت، می‌توانیم این پرس و جو را در یک تابع اسکالر (Scalar Function) قرار دهیم. این تابع نام Sequence را به عنوان ورودی می‌گیرد و last_value آن را برمی‌گرداند.

CREATE FUNCTION ufn_GetCurrentSequenceValue (@SequenceName NVARCHAR(128))
RETURNS BIGINT
AS
BEGIN
    DECLARE @LastValue BIGINT;

    SELECT @LastValue = CONVERT(BIGINT, last_value)
    FROM sys.sequences
    WHERE name = @SequenceName;

    RETURN @LastValue;
END;
GO

اکنون می‌توانید به سادگی این تابع را فراخوانی کنید تا آخرین مقدار استفاده شده توسط هر Sequence را دریافت کنید:

SELECT dbo.ufn_GetCurrentSequenceValue('MySequence');

این تابع یک راه قابل اعتماد برای دریافت آخرین مقدار پایدار (persisted) یک Sequence را فراهم می‌کند، بدون در نظر گرفتن جلسه یا حوزه‌ای که در آن NEXT VALUE FOR آخرین بار فراخوانی شده است.

نکته مهم در مورد sys.sequences.last_value:

در حالی که sys.sequences.last_value آخرین مقداری را که واقعاً تولید و پایدار شده است به شما می‌دهد، مهم است که درک کنید که در یک محیط با همزمانی بالا (high-concurrency)، ممکن است یک پنجره زمانی بسیار کوچک وجود داشته باشد که در آن یک جلسه دیگر، یک NEXT VALUE FOR را بعد از پرس و جوی شما از sys.sequences اما قبل از استفاده شما از آن مقدار در تراکنش خود، تولید کند. این یک ویژگی رایج مقادیر خودافزایشی است. برای اکثر اهداف عملی، به ویژه برای گزارش‌دهی یا مقداردهی اولیه، sys.sequences.last_value به اندازه کافی قوی است. اگر یکپارچگی تراکنشی مطلق برای مقدار بعدی حیاتی است، پس NEXT VALUE FOR درون تراکنش شما تنها رویکرد ایمن است. این تابع برای دریافت آخرین مقدار استفاده شده به صورت جهانی کمک می‌کند، نه لزوماً مقدار بعدی در دسترس به صورت امن در تراکنش شما در صورتی که چندین تراکنش همزمان درخواست مقدار می‌کنند.