تعیین آخرین مقدار استفاده شده سیکوئنس در SQL Server بدون پیشروی

تعیین آخرین مقدار استفاده شده از یک سیکوئنس در SQL Server: راهنمای کامل

در SQL Server، سیکوئنس‌ها ابزاری قدرتمند برای تولید اعداد منحصر به فرد هستند که اغلب برای شناسه‌های اصلی (Primary Keys) استفاده می‌شوند. برخلاف ستون‌های Identity که به یک جدول خاص گره خورده‌اند، سیکوئنس‌ها مستقل از جدول عمل می‌کنند و می‌توانند توسط چندین جدول یا برای نیازهای مختلف مورد استفاده قرار گیرند. اما یکی از چالش‌های رایج برای توسعه‌دهندگان و مدیران پایگاه داده، چگونگی تعیین “آخرین مقدار” استفاده شده از یک سیکوئنس بدون پیش بردن آن است.

به طور معمول، برای دریافت مقدار بعدی از یک سیکوئنس، از عبارت NEXT VALUE FOR استفاده می‌شود:

SELECT NEXT VALUE FOR MySequence;

این دستور مقدار بعدی را بازگردانده و سیکوئنس را پیش می‌برد، که در سناریوهایی که فقط می‌خواهیم مقدار فعلی را بدانیم، مطلوب نیست.

بررسی متادیتای سیکوئنس‌ها با sys.sequences

SQL Server اطلاعات مربوط به سیکوئنس‌ها را در کاتالوگ ویو sys.sequences ذخیره می‌کند. این ویو اطلاعات مهمی مانند مقدار شروع، مقدار افزایشی، حداقل و حداکثر مقادیر و تعداد مقادیر کش شده را فراهم می‌کند. با این حال، ستونی به نام current_value که به طور مستقیم آخرین مقدار اختصاص یافته را نشان دهد، وجود ندارد. در عوض، ستون last_value وجود دارد که نشان‌دهنده آخرین مقداری است که توسط سیستم برای آن سیکوئنس تولید شده است، اما این لزوماً آخرین مقداری نیست که به کاربر یا برنامه اختصاص داده شده است، به خصوص زمانی که از قابلیت کش (CACHE) استفاده می‌شود.

برای مشاهده اطلاعات یک سیکوئنس خاص:

SELECT
    name,
    start_value,
    increment,
    minimum_value,
    maximum_value,
    is_cycling,
    is_cached,
    cache_size,
    last_value
FROM sys.sequences
WHERE name = 'MySequence';

ستون last_value در sys.sequences تنها در صورتی آخرین مقدار استفاده شده را نشان می‌دهد که سیکوئنس با NO CACHE تعریف شده باشد یا کش آن پر شده و یک محدوده جدید از سرور گرفته شده باشد. اگر سیکوئنس دارای CACHE باشد و هنوز مقادیر کش شده‌ای در دسترس باشند، last_value آخرین مقداری را نشان می‌دهد که از سرور گرفته شده، نه آخرین مقداری که به یک برنامه کاربردی تحویل داده شده است.

استفاده از روال ذخیره شده sp_sequence_get_range

یکی از روش‌های رایج و کارآمد برای تعیین آخرین مقدار استفاده شده از یک سیکوئنس بدون پیش بردن آن، استفاده از روال ذخیره شده سیستمی و مستند نشده sp_sequence_get_range است. این روال به شما امکان می‌دهد تا یک محدوده از مقادیر سیکوئنس را درخواست کنید، اما به جای اینکه مستقیماً مقادیر را به شما بدهد و سیکوئنس را پیش ببرد، اطلاعات مربوط به شروع و پایان یک محدوده احتمالی را باز می‌گرداند. این روال برای زمانی که شما نیاز به تخصیص بلوکی از IDها دارید طراحی شده است، اما می‌توان از آن برای بررسی وضعیت فعلی سیکوئنس نیز استفاده کرد.

این روال چندین پارامتر ورودی و خروجی دارد. شما با ارسال نام سیکوئنس و تعداد مقادیری که می‌خواهید “دریافت کنید” (حتی اگر قصد استفاده از آنها را ندارید)، می‌توانید اطلاعات مهمی را از آن به دست آورید.

مثالی از نحوه استفاده از sp_sequence_get_range:

DECLARE @range_first_value bigint;
DECLARE @range_last_value bigint;
DECLARE @range_size bigint;
DECLARE @sequence_increment bigint;
DECLARE @sequence_first_value bigint;
DECLARE @sequence_last_value bigint;
DECLARE @sequence_name nvarchar(255) = N'MySequence';

EXEC sys.sp_sequence_get_range
    @sequence_name = @sequence_name,
    @range_size = 1, -- Requesting a range of size 1 just to get current info
    @range_first_value = @range_first_value OUTPUT,
    @range_last_value = @range_last_value OUTPUT,
    @sequence_increment = @sequence_increment OUTPUT,
    @sequence_first_value = @sequence_first_value OUTPUT,
    @sequence_last_value = @sequence_last_value OUTPUT;

SELECT
    'Last Value Issued (Approx.)' AS Description,
    @range_first_value - @sequence_increment AS Value;

در این مثال، با درخواست یک @range_size = 1، ما به طور غیرمستقیم از سیکوئنس می‌خواهیم که چه مقداری را به عنوان اولین مقدار از یک محدوده جدید ارائه می‌دهد (اگر واقعاً محدوده را می‌گرفتیم). @range_first_value اولین مقداری را برمی‌گرداند که *می‌توانست* اختصاص یابد. اگر یک سیکوئنس با کش (CACHE) کار کند، این مقدار می‌تواند آخرین مقدار واقعی استفاده شده را به همراه increment نشان دهد. بنابراین، با کسر @sequence_increment از @range_first_value، می‌توانیم مقدار قبلی را که احتمالاً آخرین مقدار استفاده شده بوده است، تخمین بزنیم.

توجه داشته باشید که sp_sequence_get_range یک روال مستند نشده است، به این معنی که ممکن است در نسخه‌های آینده SQL Server تغییر کند یا حذف شود. با این حال، در حال حاضر، این یکی از مطمئن‌ترین روش‌ها برای به دست آوردن اطلاعات دقیق در مورد وضعیت فعلی یک سیکوئنس بدون دستکاری آن است.

محاسبه آخرین مقدار بر اساس Cache Size (روش جایگزین)

در صورتی که نمی‌خواهید از sp_sequence_get_range استفاده کنید و می‌دانید سیکوئنس شما از CACHE استفاده می‌کند، می‌توانید با ترکیب اطلاعات از sys.sequences و NEXT VALUE FOR (در یک تراکنش که در نهایت Rollback می‌شود)، یا با کمی پیچیدگی بیشتر، با استفاده از فرمول، آخرین مقدار را تخمین بزنید. اما این روش معمولاً کمتر دقیق است و نیازمند درک عمیق‌تری از نحوه پر شدن کش است. بهترین راه استفاده از sys.sequences.last_value به علاوه یک محاسبه دستی است که بر اساس cache_size انجام می‌شود.

برای سیکوئنس‌هایی که با CACHE تعریف شده‌اند، last_value در sys.sequences نشان‌دهنده آخرین مقداری است که SQL Server از یک “دسته” بزرگتر برای تخصیص به کش خود گرفته است، نه آخرین مقداری که به طور فعال توسط یک فراخوانی NEXT VALUE FOR استفاده شده است.

مقدار واقعی که ممکن است در حال حاضر در کش موجود باشد و هنوز استفاده نشده است، را می‌توان با این فرمول محاسبه کرد:

(last_value - start_value) % cache_size

یا برای یافتن آخرین مقدار “استفاده شده” واقعی از کش:

SELECT
    s.name AS SequenceName,
    s.last_value,
    s.cache_size,
    s.increment,
    s.last_value - (s.cache_size - ((s.last_value - s.start_value) % s.cache_size)) * s.increment AS ActualLastUsedValue
FROM sys.sequences AS s
WHERE s.name = 'MySequence';

این فرمول سعی می‌کند با در نظر گرفتن سایز کش و مقدار آغازین، “آخرین مقدار استفاده شده واقعی” را از درون بلوک کش محاسبه کند. این یک تخمین است و دقت آن به نحوه تعامل SQL Server با کش سیکوئنس بستگی دارد.

نتیجه‌گیری و بهترین روش‌ها

تعیین آخرین مقدار استفاده شده از یک سیکوئنس در SQL Server بدون پیش بردن آن، به دلیل وجود کش‌ها و نحوه عملکرد NEXT VALUE FOR، می‌تواند چالش‌برانگیز باشد. برای سیکوئنس‌های NO CACHE، ستون last_value در sys.sequences معمولاً دقیق‌ترین منبع است. اما برای سیکوئنس‌های دارای CACHE، روال ذخیره شده sp_sequence_get_range (با وجود مستند نبودن) اغلب بهترین و دقیق‌ترین راه برای دریافت اطلاعات در مورد آخرین مقدار صادر شده یا مقدار فعلی در دسترس است. همیشه قبل از استفاده از روال‌های مستند نشده در محیط تولید، خطرات احتمالی را ارزیابی کنید و آن را به طور کامل آزمایش کنید.