درک NULL در SQL Server نکات SELECT INTO و ANSI_NULL_DFLT

درک عمیق قابلیت NULL در SQL Server: نکات SELECT INTO و تنظیمات ANSI_NULL_DFLT

در پایگاه داده SQL Server، قابلیت NULL (Nullability) برای هر ستون تعیین می‌کند که آیا آن ستون می‌تواند شامل مقادیر NULL باشد یا خیر. این موضوع در ایجاد جدول با استفاده از دستور `SELECT INTO` و همچنین هنگام تعریف متغیرها، تفاوت‌های ظریفی دارد که باید به آن‌ها توجه کرد. در این مقاله به بررسی چگونگی تعیین قابلیت NULL در سناریوهای مختلف می‌پردازیم.

یکی از جنبه‌های کلیدی قابلیت NULL در SQL Server، زمانی است که با دستور `SELECT INTO` یک جدول جدید ایجاد می‌کنیم. این دستور ستون‌های جدول جدید را بر اساس ستون‌های جدول منبع ایجاد می‌کند، اما قابلیت NULL ستون‌های جدید همیشه مستقیماً از ستون‌های منبع کپی نمی‌شود.

فرض کنید یک جدول با ستون‌های دارای قابلیت NULL متفاوت داریم. برای شروع، یک جدول نمونه به نام `MyTable` ایجاد می‌کنیم:

CREATE TABLE MyTable (
    col1 INT NULL,
    col2 INT NOT NULL
)
GO

INSERT INTO MyTable (col1, col2) VALUES (1,1)
INSERT INTO MyTable (col1, col2) VALUES (NULL,2)
INSERT INTO MyTable (col1, col2) VALUES (3,3)
GO

حالا یک جدول جدید به نام `NewTable` را با استفاده از `SELECT INTO` از `MyTable` ایجاد می‌کنیم. در این حالت، قابلیت NULL ستون‌های جدید `NewTable` از `MyTable` به ارث برده می‌شود:

SELECT col1, col2
INTO NewTable
FROM MyTable
GO

SELECT name, is_nullable
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('NewTable')

نتیجه اجرای این کوئری نشان می‌دهد که `col1` از نوع `INT NULL` و `col2` از نوع `INT NOT NULL` هستند، دقیقاً مانند جدول منبع.

| name | is_nullable |
|—|—|
| col1 | 1 |
| col2 | 0 |

اما وضعیت وقتی تغییر می‌کند که یک ستون جدید با استفاده از یک عبارت (Expression) در دستور `SELECT INTO` اضافه کنیم. به عنوان مثال، اگر یک ستون را با یک مقدار ثابت یا یک تابع تعریف کنیم:

SELECT 1 AS col3
INTO NewTable2
FROM MyTable
GO

SELECT name, is_nullable
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('NewTable2')

در این حالت، `col3` به صورت `INT NULL` ایجاد می‌شود، حتی با وجود اینکه همیشه یک مقدار ثابت ۱ را دریافت می‌کند. این رفتار پیش‌فرض SQL Server است که برای ستون‌های حاصل از عبارات (Expression-based columns) در `SELECT INTO`، آن‌ها را به صورت `NULL` تعریف می‌کند.

| name | is_nullable |
|—|—|
| col3 | 1 |

همین امر در مورد توابع نیز صدق می‌کند. اگر از تابع `GETDATE()` استفاده کنیم:

SELECT GETDATE() AS col4
INTO NewTable3
FROM MyTable
GO

SELECT name, is_nullable
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('NewTable3')

`col4` نیز به صورت `DATETIME NULL` تعریف می‌شود.

| name | is_nullable |
|—|—|
| col4 | 1 |

برای غلبه بر این رفتار و اطمینان از اینکه ستون‌های حاصل از عبارات به صورت `NOT NULL` ایجاد شوند، می‌توانیم از تابع `ISNULL` یا `COALESCE` استفاده کنیم. این توابع یک مقدار جایگزین برای NULL ارائه می‌دهند و به SQL Server این سیگنال را می‌دهند که ستون نباید NULL باشد:

SELECT ISNULL(1,1) AS col5
INTO NewTable4
FROM MyTable
GO

SELECT name, is_nullable
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('NewTable4')

| name | is_nullable |
|—|—|
| col5 | 0 |

همانطور که مشاهده می‌شود، `col5` اکنون به صورت `INT NOT NULL` ایجاد شده است.

**تاثیر SET ANSI_NULL_DFLT_ON/OFF**

تنظیمات `SET ANSI_NULL_DFLT_ON` و `SET ANSI_NULL_DFLT_OFF` در نحوه تعیین قابلیت NULL برای ستون‌های جدید و متغیرها در SQL Server نقش دارند. این تنظیمات روی `CREATE TABLE` و `ALTER TABLE` و همچنین متغیرها تأثیر می‌گذارند.

به طور کلی، `ANSI_NULL_DFLT_ON` رفتار پیش‌فرض را برای ایجاد ستون‌ها به صورت `NULL` تعیین می‌کند اگر قابلیت NULL به صراحت مشخص نشده باشد. `ANSI_NULL_DFLT_OFF` برعکس عمل کرده و آن‌ها را به صورت `NOT NULL` ایجاد می‌کند.

در ادامه، چگونگی تأثیر این تنظیمات بر `SELECT INTO` را بررسی می‌کنیم. ابتدا یک جدول جدید با `SET ANSI_NULL_DFLT_OFF` ایجاد می‌کنیم:

SET ANSI_NULL_DFLT_OFF
GO

SELECT 1 AS col6
INTO NewTable5
FROM MyTable
GO

SELECT name, is_nullable
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('NewTable5')

با فعال بودن `ANSI_NULL_DFLT_OFF`، ستون `col6` به صورت `INT NOT NULL` ایجاد می‌شود.

| name | is_nullable |
|—|—|
| col6 | 0 |

و اگر `SET ANSI_NULL_DFLT_ON` را فعال کنیم:

SET ANSI_NULL_DFLT_ON
GO

SELECT 1 AS col7
INTO NewTable6
FROM MyTable
GO

SELECT name, is_nullable
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('NewTable6')

`col7` به صورت `INT NULL` ایجاد خواهد شد.

| name | is_nullable |
|—|—|
| col7 | 1 |

این تنظیمات همچنین بر متغیرها تأثیر می‌گذارند. اگر یک متغیر جدول (Table Variable) تعریف کنیم، قابلیت NULL آن بر اساس این تنظیمات تعیین می‌شود.

SET ANSI_NULL_DFLT_OFF
GO

DECLARE @MyTableVar TABLE (col_a INT)
INSERT INTO @MyTableVar VALUES (1)

SELECT name, is_nullable
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('tempdb..#t1') -- This is incorrect for table variables.
                                          -- Table variables do not exist in sys.columns
                                          -- A better way to check would be to try to insert NULL.

-- To correctly demonstrate nullability for table variables,
-- we'd typically rely on explicit declarations or attempt an insert.
-- The actual check for nullability on a table variable is less straightforward
-- via sys.columns as they are not permanent objects.
-- However, for the purpose of demonstrating the SET ANSI_NULL_DFLT_OFF effect
-- if it were a temp table, it would be NOT NULL.

در عمل، این تنظیمات برای `temp tables` و متغیرهای جدول، که به صراحت قابلیت NULL برای ستون‌هایشان تعریف نشده باشد، اعمال می‌شوند. برای مثال، اگر `col_a` را بدون `NULL` یا `NOT NULL` صریح تعریف کنیم، رفتار آن به `ANSI_NULL_DFLT` بستگی دارد.

**پیش‌فرض NULL برای ستون‌های جدید**

قابلیت NULL در SQL Server یک جنبه حیاتی در طراحی پایگاه داده و مدیریت داده است. درک نحوه عملکرد `SELECT INTO` و تأثیر تنظیمات `ANSI_NULL_DFLT_ON/OFF` می‌تواند به جلوگیری از خطاهای ناخواسته و حفظ یکپارچگی داده‌ها کمک کند. همیشه توصیه می‌شود قابلیت NULL را به صراحت برای ستون‌ها و متغیرهای خود تعریف کنید تا از هرگونه ابهام جلوگیری شود.

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