⭐️ آموزش فیلد IDENTITY در SQL؛ ساخت شناسه خودکار و کاربردها 💻

Q: تفاوت DELETE و TRUNCATE در شمارنده identity چیست؟

DELETE معمولا شمارنده identity را به Seed بازنمی گرداند. TRUNCATE TABLE در شرایط مجاز شمارنده را به Seed بازمی گرداند، اما محدودیت هایی دارد.

فهرست مطالب ▼

فیلد identity در SQL چیست و چگونه از آن استفاده کنیم؟

⭐ فیلد identity در SQL

فیلد identity در SQL قابلیتی در SQL Server است که برای ساخت خودکار مقدارهای عددی یک ستون به کار می رود. این ویژگی معمولا برای شناسه رکوردها استفاده می شود و کمک می کند هنگام درج اطلاعات، لازم نباشد مقدار شناسه را برای هر ردیف به صورت دستی تعیین کنید. با این حال، identity به تنهایی تضمین نمی کند که مقدارها بدون فاصله و همیشه یکتا باشند؛ برای یکتایی باید از کلید اصلی یا محدودیت مناسب نیز استفاده شود.

✨

🔢

ساخت خودکار شناسه عددی

⚙️

تنظیم مقدار شروع و افزایش

🔐

نیاز به کلید برای یکتایی

🧹

تفاوت رفتار DELETE و TRUNCATE

۵ نکته مهم درباره فیلد identity در SQL

💡 تولید شناسه خودکار

فیلد identity در SQL Server مشخص می کند که موتور پایگاه داده هنگام درج یک ردیف، برای ستون مورد نظر مقداری عددی تولید کند. این قابلیت در جدول هایی که هر رکورد به شناسه مستقل نیاز دارد بسیار کاربردی است؛ برای نمونه، جدول مشتریان می تواند ستونی به نام CustomerId داشته باشد که برای هر مشتری مقدار متفاوتی دریافت کند. در حالت معمول، برنامه نویس این شناسه را در دستور INSERT نمی فرستد و SQL Server آن را تعیین می کند.

۱. Identity یک ویژگی SQL Server است

⭐ تفاوت پایگاه های داده

واژه SQL نام یک زبان و خانواده ای از فناوری های پایگاه داده است، اما عبارت IDENTITY در این راهنما به ویژگی SQL Server اشاره دارد. سامانه های دیگر ممکن است برای تولید خودکار شناسه از سازوکارهایی مانند sequence یا ستون های auto increment استفاده کنند. بنابراین، اگر قرار است ساختار پایگاه داده از SQL Server به محصول دیگری منتقل شود، نباید فرض کرد که تعریف identity بدون تغییر کار خواهد کرد.

📝 نمونه کاربرد

فرض کنید یک سامانه فروشگاهی در SQL Server ساخته اید و برای هر سفارش به شناسه نیاز دارید. ستون OrderId می تواند identity باشد تا با ثبت سفارش جدید، مقدار بعدی را پایگاه داده بسازد. اگر همین طرح را به پایگاه داده دیگری منتقل کنید، باید روش معادل تولید شناسه را در آن سامانه بررسی کنید. انتخاب روش درست به فناوری مورد استفاده و نیازهای برنامه وابسته است.

۲. مقدار شروع و مقدار افزایش را بشناسید

💡 Seed و Increment

تعریف identity دو بخش اصلی دارد: مقدار آغازین یا Seed و مقدار افزایش یا Increment. Seed تعیین می کند اولین مقدار تولیدشده چه عددی باشد و Increment مشخص می کند مقدارهای بعدی با چه فاصله ای افزایش یا کاهش پیدا کنند. اگر این دو مقدار را مشخص نکنید، SQL Server به طور معمول از مقدار آغازین ۱ و افزایش ۱ استفاده می کند؛ یعنی مقدارهای متوالی مورد انتظار ۱، ۲، ۳ و به همین ترتیب خواهند بود.

⭐ تنظیم مقدارهای دلخواه

برای مثال، اگر بخواهید مقدارها از ۱۰۰۰ آغاز شوند و هر بار ۱۰ واحد افزایش یابند، می توانید از IDENTITY(1000,10) استفاده کنید. مقدار افزایش می تواند منفی هم باشد؛ در چنین حالتی اعداد به سمت پایین حرکت می کنند. انتخاب Seed و Increment باید با نوع داده ستون و ظرفیت آن هماهنگ باشد، چون رسیدن به حد مجاز نوع داده باعث خطای درج خواهد شد.

۳. تعریف ستون identity در دستور ساخت جدول

⌨️ ساخت جدول Persons

ویژگی identity هنگام تعریف ستون در دستور CREATE TABLE نوشته می شود. نمونه زیر جدولی به نام Persons می سازد که ستون P_Id آن به صورت خودکار مقدار می گیرد. ستون های دیگر اطلاعات نام و نشانی را نگهداری می کنند. برای اجرای این نمونه، نوع داده هر ستون و نام جدول را با ساختار واقعی برنامه خود هماهنگ کنید.

CREATE TABLE Persons
(
    P_Id int PRIMARY KEY IDENTITY(1,1),
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

⭐ درج رکورد جدید

در دستور INSERT معمولی، ستون P_Id را از فهرست ستون ها حذف می کنید تا SQL Server مقدار آن را بسازد. برای نمونه، می توانید LastName و FirstName را ارسال کنید و شناسه به شکل خودکار تعیین می شود. استفاده از PRIMARY KEY در نمونه بالا نیز مهم است: identity مقدار تولید می کند، اما کلید اصلی قاعده یکتایی و شناسایی ردیف را در ساختار جدول اعمال می کند.

INSERT INTO Persons (LastName, FirstName, Address, City)
VALUES ('Ahmadi', 'Sara', 'Example Street', 'Tehran');

۴. Identity به تنهایی تضمین کننده یکتایی نیست

⚠️ شناسه با کلید اصلی فرق دارد

ستون identity برای تولید مقدار طراحی شده است، اما ویژگی identity به تنهایی جایگزین PRIMARY KEY یا UNIQUE نیست. اگر یکتایی شناسه برای صحت داده ها اهمیت دارد، روی ستون محدودیت مناسب تعریف کنید. همچنین نباید روی پیوسته بودن مقدارها حساب کنید؛ حذف ردیف، بازگشت تراکنش، خطای درج یا برخی رفتارهای داخلی SQL Server ممکن است فاصله هایی در شماره ها ایجاد کند. این فاصله ها لزوما نشانه خرابی پایگاه داده نیستند.

💡 سناریوی فروش

در یک فروشگاه، ممکن است سفارش شماره ۲۰۵ پس از ثبت حذف شود و سفارش بعدی شماره ۲۰۶ بگیرد. این رفتار طبیعی است و شماره سفارش نباید به عنوان شمارنده ای بدون فاصله برای گزارش های مالی در نظر گرفته شود. اگر کسب و کار به شماره گذاری پیوسته با قواعد خاص نیاز دارد، باید آن نیاز را جداگانه طراحی و کنترل کند؛ identity ابزار ساده تولید شناسه است، نه سامانه کامل شماره گذاری قانونی.

۵. پس از حذف ردیف ها، رفتار جدول را بررسی کنید

⭐ تفاوت DELETE و TRUNCATE

دستور DELETE ردیف ها را حذف می کند، اما معمولا شمارنده identity را به مقدار Seed بازنمی گرداند. بنابراین درج بعدی بر اساس وضعیت فعلی شمارنده انجام می شود و ممکن است پس از آخرین مقدار استفاده شده، فاصله ای هم دیده شود. در مقابل، TRUNCATE TABLE در شرایط مجاز شمارنده identity را به مقدار Seed تعریف شده بازمی گرداند. انتخاب این دستور فقط به موضوع شمارنده محدود نیست و به محدودیت های جدول و نیازهای تراکنشی نیز بستگی دارد.

🔎 بررسی پیش از پاک سازی

TRUNCATE TABLE محدودیت هایی دارد؛ برای نمونه، در برخی شرایط وجود رابطه کلید خارجی اجازه اجرای آن را نمی دهد. این دستور مانند DELETE امکان انتخاب ردیف ها با شرط WHERE را ندارد و رفتار آن از نظر ثبت عملیات نیز با DELETE یکسان نیست. پس انتخاب TRUNCATE صرفا برای سریع تر کردن حذف یا صفر کردن شمارنده توصیه نمی شود. ابتدا وابستگی های جدول، نیاز به بازیابی داده و سیاست پشتیبان گیری را بررسی کنید.

چطور مقدار identity را در SQL Server مدیریت کنیم؟

🛠️ درج مقدار صریح

در حالت معمول، مقدار ستون identity را در INSERT وارد نمی کنید. اگر برای انتقال داده یا سناریوی مدیریتی لازم باشد مقدار مشخصی درج شود، SQL Server امکان استفاده از SET IDENTITY_INSERT را فراهم می کند. این گزینه را فقط در مواقع ضروری و با دقت به کار ببرید؛ در یک نشست، برای یک جدول در هر لحظه می توان آن را فعال کرد. پس از پایان درج های مورد نیاز نیز آن را غیرفعال کنید.

SET IDENTITY_INSERT Persons ON;

INSERT INTO Persons (P_Id, LastName, FirstName)
VALUES (100, 'Karimi', 'Nima');

SET IDENTITY_INSERT Persons OFF;

⭐ احتیاط در درج دستی

درج مقدار دستی ممکن است با مقداری که SQL Server بعدا تولید می کند تداخل داشته باشد، به ویژه اگر مقدار واردشده از محدوده معمول شمارنده جلوتر باشد. پس از مهاجرت داده، وضعیت identity را بررسی کنید و در صورت نیاز آن را با ابزارهای مدیریتی مناسب هماهنگ کنید. این کار را در محیط عملیاتی بدون نسخه پشتیبان و بررسی اثر آن بر کلیدهای وابسته انجام ندهید.

دریافت شناسه رکوردی که همین حالا درج شده است

📥 استفاده از SCOPE_IDENTITY

گاهی برنامه پس از درج یک ردیف به شناسه تولیدشده نیاز دارد؛ برای مثال، شناسه سفارش را می گیرد تا ردیف های جزئیات سفارش را به آن مرتبط کند. در SQL Server، تابع SCOPE_IDENTITY مقدار identity تولیدشده در همان محدوده اجرایی را برمی گرداند. به کار بردن آن در همان اتصال و محدوده درج، معمولا انتخاب مطمئن تری از توابعی است که ممکن است مقدار تولیدشده در محدوده های دیگر را برگردانند.

INSERT INTO Persons (LastName, FirstName)
VALUES ('Karimi', 'Nima');

SELECT SCOPE_IDENTITY() AS NewPersonId;

راهنمای انتخاب نوع داده و طراحی ستون identity

📏

ظرفیت نوع داده

نوع داده را با بیشترین تعداد شناسه مورد انتظار هماهنگ کنید.

🔒

کلید اصلی

برای تضمین یکتایی، محدودیت کلید اصلی یا UNIQUE تعریف کنید.

🧭

نیازهای برنامه

شناسه داخلی را از شماره نمایشی یا تجاری جدا نگه دارید.

🔄

انتقال داده

هنگام وارد کردن شناسه های قدیمی، شمارنده را بررسی کنید.

📊 مقایسه نوع های رایج

انتخاب نوع داده باید بر اساس مقیاس واقعی سامانه انجام شود. نوع های کوچک تر فضای کمتری مصرف می کنند، اما سقف مقدار پایین تری دارند. اگر احتمال می دهید تعداد ردیف ها در آینده زیاد شود، ظرفیت نوع داده را از ابتدا بسنجید تا مجبور به تغییر ساختار جدول نشوید. اعداد ظرفیت در جدول زیر حدود نوع های صحیح متداول SQL Server را نشان می دهند.

نوع داده	محدوده تقریبی	کاربرد معمول
smallint	از ۳۲۷۶۸- تا ۳۲۷۶۷	جدول های کوچک با ظرفیت محدود
int	از ۲۱۴۷۴۸۳۶۴۸- تا ۲۱۴۷۴۸۳۶۴۷	بسیاری از جدول های عمومی
bigint	از ۹۲۲۳۳۷۲۰۳۶۸۵۴۷۷۵۸۰۸- تا ۹۲۲۳۳۷۲۰۳۶۸۵۴۷۷۵۸۰۷	جدول های بسیار بزرگ یا شناسه های بلندمدت

اشتباهات رایج هنگام استفاده از identity

فرض کردن اینکه شماره ها همیشه پیوسته هستند

⚠️ فاصله در شماره ها

مقدارهای identity برای شناسایی رکورد مناسب اند، اما نباید آن ها را شمارنده بدون فاصله در نظر گرفت. حذف ردیف یا لغو تراکنش لزوما مقدار مصرف شده را به چرخه بازنمی گرداند. گزارش هایی که نیازمند شماره پیوسته هستند باید بر اساس قواعد مستقل کسب و کار طراحی شوند و نباید به رفتار پیش فرض identity تکیه کنند.

نادیده گرفتن ظرفیت ستون

📐 انتخاب اندازه مناسب

اگر نوع داده ظرفیت کافی نداشته باشد، با رسیدن شمارنده به حد مجاز، درج رکورد جدید با خطا روبه رو می شود. پیش از انتخاب، تعداد فعلی ردیف ها، نرخ رشد و عمر مورد انتظار سامانه را در نظر بگیرید. انتخاب bigint برای همه جدول ها نیز همیشه لازم نیست؛ تصمیم مناسب میان ظرفیت مورد نیاز و هزینه نگهداری تعادل ایجاد می کند.

حذف داده برای بازگرداندن شمارنده بدون بررسی پیامدها

🧯 پاک سازی ایمن جدول

برای صفر کردن شمارنده، تمام داده های جدول را بدون بررسی وابستگی ها حذف نکنید. TRUNCATE TABLE می تواند روی رابطه های کلید خارجی، مجوزها و نیازهای بازیابی اثر بگذارد. اگر هدف فقط حذف چند ردیف است، DELETE همراه شرط مناسب را بررسی کنید؛ اگر هدف خالی کردن کامل جدول است، ابتدا محدودیت ها و نسخه پشتیبان را ارزیابی کنید.

جمع بندی فیلد identity در SQL

⭐ انتخاب درست و مطمئن

فیلد identity در SQL Server راهی ساده برای تولید خودکار شناسه عددی است. با تعیین Seed و Increment می توانید نقطه شروع و گام افزایش را مشخص کنید، اما برای یکتایی باید از کلید اصلی یا محدودیت UNIQUE کمک بگیرید. همچنین رفتار DELETE و TRUNCATE، ظرفیت نوع داده، امکان فاصله افتادن میان شماره ها و روش دریافت شناسه درج شده را در طراحی لحاظ کنید. اگر پروژه شما به پایگاه داده، سامانه مدیریتی یا طراحی سایت نیاز دارد، انتخاب درست ساختار داده از ابتدا می تواند توسعه و نگهداری را ساده تر کند.

سوالات متداول فیلد identity در SQL

فیلد identity در SQL چه کاری انجام می دهد؟

در SQL Server، identity برای تولید خودکار مقدار عددی یک ستون هنگام درج ردیف استفاده می شود. این ویژگی معمولا برای شناسه رکوردها به کار می رود و مقدار آغازین و گام افزایش آن قابل تنظیم است. برای تضمین یکتایی، ستون را به عنوان کلید اصلی یا با محدودیت UNIQUE تعریف کنید.

آیا می توان مقدار ستون identity را دستی وارد کرد؟

در درج معمولی، مقدار ستون identity را ارسال نمی کنید. برای برخی کارهای مدیریتی یا انتقال داده می توان SET IDENTITY_INSERT را برای جدول فعال کرد و مقدار مشخصی وارد کرد. این کار باید با دقت انجام شود تا مقدارهای دستی با مقدارهایی که SQL Server تولید می کند تداخل نداشته باشند.

آیا مقدارهای identity همیشه پشت سر هم هستند؟

خیر. حذف ردیف ها، بازگشت تراکنش، خطاهای درج و رفتارهای داخلی SQL Server ممکن است باعث ایجاد فاصله میان شناسه ها شوند. identity برای تولید شناسه مناسب است، اما ابزار تضمین شماره گذاری پیوسته نیست. اگر شماره پیوسته برای کسب و کار ضروری است، آن نیاز را با سازوکار جداگانه پیاده سازی کنید.

تفاوت DELETE و TRUNCATE در شمارنده identity چیست؟

DELETE ردیف ها را حذف می کند و معمولا شمارنده identity را به Seed بازنمی گرداند. TRUNCATE TABLE در شرایط مجاز همه ردیف های جدول را حذف می کند و شمارنده را به Seed بازمی گرداند. با این حال، TRUNCATE محدودیت هایی دارد و باید پیش از اجرا وابستگی های جدول و نیازهای بازیابی داده بررسی شوند.

چگونه شناسه رکورد تازه درج شده را دریافت کنم؟

در SQL Server می توانید پس از INSERT از تابع SCOPE_IDENTITY استفاده کنید تا مقدار identity تولیدشده در همان محدوده اجرایی را دریافت کنید. این مقدار برای مرتبط کردن رکورد تازه با داده های دیگر کاربرد دارد؛ برای نمونه، دریافت شناسه سفارش و استفاده از آن در جدول جزئیات سفارش.

فیلد identity در Sql