راهنمای جامع انواع داده‌های تعریف‌شده توسط کاربر (UDDT) در SQL Server

انواع داده‌های تعریف‌شده توسط کاربر (User-Defined Data Types یا UDDT) در SQL Server ابزاری قدرتمند برای تعریف نوع داده‌های سفارشی بر اساس انواع داده‌های سیستمی موجود هستند. این قابلیت به توسعه‌دهندگان کمک می‌کند تا با ایجاد یک لایه انتزاعی، سازگاری و یکپارچگی داده‌ها را در پایگاه داده خود بهبود بخشند. با استفاده از UDDTها، می‌توانید اطمینان حاصل کنید که ستون‌ها یا متغیرهایی که هدف مشابهی دارند، از نظر نوع داده، طول و امکان پذیرش مقادیر NULL به طور یکسان رفتار می‌کنند. این کار خوانایی کد را افزایش داده، احتمال خطا را کاهش می‌دهد و مدیریت داده‌ها را در یک محیط پیچیده آسان‌تر می‌سازد.

برای ایجاد یک نوع داده تعریف‌شده توسط کاربر (UDDT)، از دستور `CREATE TYPE` استفاده می‌شود. این دستور به شما امکان می‌دهد تا یک نوع داده جدید را بر اساس یک نوع داده سیستمی موجود، همراه با ویژگی‌های خاص آن مانند قابلیت NULL بودن یا طول، تعریف کنید.

سینتکس پایه برای ایجاد UDDT به شرح زیر است:

CREATE TYPE type_name [ FROM base_type ] [ NULL | NOT NULL ]

در این سینتکس:
* `type_name` نام منحصر به فردی است که برای نوع داده جدید خود انتخاب می‌کنید.
* `base_type` نوع داده سیستمی است که UDDT شما بر پایه آن ساخته می‌شود (مانند `INT`، `VARCHAR(50)`، `DATETIME` و غیره).
* `NULL` یا `NOT NULL` تعیین می‌کند که آیا متغیرها یا ستون‌های تعریف‌شده با این UDDT می‌توانند مقادیر NULL را بپذیرند یا خیر. اگر هیچ یک از این گزینه‌ها مشخص نشود، پیش‌فرض قابلیت NULL بودن نوع داده اصلی اعمال می‌شود.

به عنوان مثال، فرض کنید می‌خواهید یک نوع داده برای شماره تأمین اجتماعی (SSN) ایجاد کنید که همواره یک رشته 9 کاراکتری و `NOT NULL` باشد. می‌توانید آن را به این صورت تعریف کنید:

CREATE TYPE SSN FROM VARCHAR(9) NOT NULL;

پس از اجرای این دستور، یک UDDT جدید به نام `SSN` در پایگاه داده شما ایجاد می‌شود.

برای تأیید ایجاد موفقیت‌آمیز UDDT و مشاهده جزئیات آن، می‌توانید از رویه ذخیره‌شده `sp_help` یا کوئری بر روی نمای `sys.types` استفاده کنید.

برای مثال با استفاده از `sp_help` برای UDDT به نام `SSN`:

EXEC sp_help SSN;

یا با کوئری گرفتن از `sys.types`:

SELECT name, system_type_id, user_type_id, is_user_defined
FROM sys.types
WHERE name = 'SSN';

این کوئری‌ها اطلاعاتی در مورد UDDT تازه ایجاد شده، از جمله نوع داده سیستمی مبنای آن و اینکه آیا یک نوع داده تعریف‌شده توسط کاربر است، به شما نمایش می‌دهند.

استفاده از نوع داده تعریف‌شده توسط کاربر

پس از ایجاد یک UDDT، می‌توانید از آن درست مانند یک نوع داده سیستمی برای تعریف متغیرها، پارامترهای روال‌های ذخیره‌شده یا ستون‌های جدول استفاده کنید.

برای اعلام یک متغیر با استفاده از UDDT ایجاد شده، ساختار مشابهی با انواع داده‌های سیستمی دارد:

DECLARE @employeeSSN SSN;
SET @employeeSSN = '123456789';
SELECT @employeeSSN AS EmployeeSSN;

این کد یک متغیر به نام `@employeeSSN` را با نوع `SSN` تعریف می‌کند و یک مقدار به آن اختصاص می‌دهد.

می‌توانید UDDTها را به عنوان نوع داده برای ستون‌های جدول نیز به کار ببرید. این روش به ویژه برای تضمین یکپارچگی داده‌ها در چندین جدول مفید است.

مثالی از ایجاد یک جدول با ستونی از نوع `SSN`:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    SSN SSN
);

در این جدول، ستون `SSN` از نوع داده تعریف‌شده توسط کاربر `SSN` خواهد بود که تضمین می‌کند همیشه `VARCHAR(9)` و `NOT NULL` باشد، همانطور که در تعریف `SSN` مشخص شده است.

هنگام درج داده در ستونی که از UDDT استفاده می‌کند، SQL Server به طور خودکار تلاش می‌کند تا مقدار ورودی را به نوع داده اصلی UDDT تبدیل کند.

مثالی از درج داده در جدول `Employees`:

INSERT INTO Employees (EmployeeID, FirstName, LastName, SSN)
VALUES (1, 'John', 'Doe', '987654321');

اگر نوع داده وارد شده با نوع داده اصلی UDDT سازگار باشد، SQL Server عملیات تبدیل ضمنی را انجام می‌دهد.

مثالی دیگر از درج داده، جایی که SQL Server تبدیل ضمنی را انجام می‌دهد:

INSERT INTO Employees (EmployeeID, FirstName, LastName, SSN)
VALUES (2, 'Jane', 'Smith', CAST(999887766 AS VARCHAR(9)));

در این مثال، مقدار عددی به `VARCHAR(9)` تبدیل شده و سپس در ستون `SSN` درج می‌شود.

تغییر نوع داده تعریف‌شده توسط کاربر

یک جنبه مهم UDDTها این است که پس از ایجاد و استفاده، نمی‌توان آن‌ها را مستقیماً تغییر داد. به این معنی که نمی‌توانید ویژگی‌هایی مانند `base_type` یا `NULL | NOT NULL` را با یک دستور `ALTER TYPE` تغییر دهید. اگر نیاز به اصلاح یک UDDT دارید، باید رویکردی چند مرحله‌ای را دنبال کنید:

1. حذف تمامی ارجاعات به UDDT: این شامل حذف ستون‌ها، متغیرها یا پارامترهایی است که از این UDDT استفاده می‌کنند.
2. حذف UDDT اصلی: پس از حذف تمامی ارجاعات، می‌توانید UDDT را حذف کنید.
3. ایجاد مجدد UDDT با تغییرات مورد نظر: UDDT را با تعریف جدید خود دوباره ایجاد کنید.
4. بازسازی اشیاء وابسته: ستون‌ها، متغیرها و پارامترهایی که قبلاً از این UDDT استفاده می‌کردند را با استفاده از تعریف جدید، بازسازی کنید.

به عنوان مثال، اگر می‌خواهید `SSN` را از `VARCHAR(9)` به `NVARCHAR(10)` تغییر دهید (فرضا برای پشتیبانی از فرمت‌های جدید SSN)، ابتدا باید ستون `SSN` را از جدول `Employees` حذف کنید یا نوع آن را تغییر دهید:

ALTER TABLE Employees
ALTER COLUMN SSN VARCHAR(10) NULL; -- موقتاً به نوع سیستمی تغییر دهید یا ستون را حذف کنید

سپس UDDT `SSN` را حذف کنید:

DROP TYPE SSN;

سپس UDDT را با تعریف جدید ایجاد کنید:

CREATE TYPE SSN FROM NVARCHAR(10) NOT NULL;

و در نهایت، ستون `SSN` در جدول `Employees` را به نوع جدید `SSN` تغییر دهید:

ALTER TABLE Employees
ALTER COLUMN SSN SSN NOT NULL;

این فرایند دقیق و با در نظر گرفتن وابستگی‌ها انجام می‌شود تا از بروز خطا جلوگیری شود.

حذف نوع داده تعریف‌شده توسط کاربر

برای حذف یک UDDT، از دستور `DROP TYPE` استفاده می‌کنید. با این حال، تنها زمانی می‌توانید یک UDDT را حذف کنید که هیچ ستون، متغیر یا پارامتر دیگری در پایگاه داده از آن استفاده نکند. اگر وابستگی‌ها وجود داشته باشد، SQL Server یک خطا ایجاد می‌کند و عملیات حذف را لغو می‌کند.

سینتکس برای حذف یک UDDT به شرح زیر است:

DROP TYPE type_name;

برای مثال، برای حذف UDDT به نام `SSN`:

DROP TYPE SSN;

اگر بخواهید UDDT `SSN` را حذف کنید در حالی که ستون `SSN` در جدول `Employees` هنوز از آن استفاده می‌کند، با خطا مواجه خواهید شد. قبل از حذف، باید تمامی ارجاعات را حذف کنید، همانطور که در بخش تغییر توضیح داده شد.

برای شناسایی ستون‌هایی که از یک UDDT خاص استفاده می‌کنند، می‌توانید از کوئری زیر استفاده کنید:

SELECT
    OBJECT_NAME(c.object_id) AS TableName,
    c.name AS ColumnName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id = t.user_type_id
WHERE t.name = 'SSN';

این کوئری تمام جداول و ستون‌هایی را که از UDDT به نام ‘SSN’ استفاده می‌کنند، به شما نشان می‌دهد و به شما کمک می‌کند تا وابستگی‌ها را قبل از حذف UDDT شناسایی و مدیریت کنید.

نتیجه‌گیری

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