نحوه استفاده از SQL Server Coalesce برای کار با مقادیر NULL

Coalesce در SQL Server، هر زمان که از T-SQL برای توسعه کوئری‌ها استفاده می‌کنید، با موقعیت‌هایی مواجه می‌شوید که باید با مقادیر NULL سر و کار داشته باشید. مهم نیست چقدر تلاش می‌کنید، نمی‌توانید NULL ها را حذف کنید. شما می‌توانید بهترین شیوه‌ها را هنگام توسعه شِمای پایگاه داده SQL Server خود دنبال کنید، اما همچنان نمی‌توانید تمام مقادیر NULL را حذف کنید. واقعیت ساده ‌این است که شما باید برای برخی از ستون‌ها به NULL اجازه ذخیره شدن دهید زیرا برخی از کوئری‌ها مقادیر NULL را برمی‌گردانند. برای درک بهتر مفاهیم آموزش جامع SQL Server را مطالعه کنید.

مشکل مقادیر NULL این است که در نظر نگرفتن آن‌ها در هنگامی که کوئری NULL برمی‌گرداند، منجر به مشکلاتی می‌شود. چگونه می‌توانیم با تشخیص مقادیر NULL و جایگزینی آن‌ها با یک مقدار غیر NULL مشکل را حل کنیم؟

Coalesce در SQL Server

COALESCE یکی از ابزارهایی است که در SQL Server برای کار با مقادیر NULL دارید. شاید اولین موردی نباشد که به آن فکر می‌کنید، اما می‌تواند انتخاب بسیار خوبی باشد. در این مقاله مثال‌هایی از نحوه استفاده از COALESCE برای کار با مقادیر NULL ارائه خواهیم داد.

قبل از اینکه به جزئیات COALESCE بپردازیم، اجازه دهید ابتدا در مورد NULL در SQL Server و رویکرد اتخاذ شده در این مقاله صحبت کنیم.

راه‌حل‌های مدیریت مقادیر NULL در SQL Server

چندین گزینه در SQL Server وجود دارد که آن‌ها را در سطح پایگاه داده برای تعیین رفتار مربوط به NULL می‌توانید تنظیم کنید. به عنوان مثال:

۱.مقدار ANSI_NULLS باید روی ON تنظیم شود. در این حالت، اگر دستور SELECT زیر اجرا شود:

SELECT * WHERE columnname = NULL or columnname <> NULL

همیشه هیچ سطری را برنمی‌گرداند، حتی اگر ردیف‌هایی با مقدار غیر NULL وجود داشته باشد. شما نمی‌توانید برای حالت مساوی یا غیر برابر با NULL کوئری اجرا کنید. شما باید columnname را برای IS NULL یا columnname را برای IS NOT NULL اجرا کنید.

SET ANSI_NULLS {ON | OFF}

۲. مقدار ANSI_PADDING باید روی ON تنظیم شود. وقتی روی ON تنظیم می‌شود، رفتار به صورت زیر است:

• ستون‌های با نوع Char با جاهای خالی پر می‌شوند.
• ستون‌های binary با صفرها پر می‌شوند.
• ستون‌های varchar جاهای خالی را اصلاح نمی‌کنند (trim trailing blanks).
• ستون‌های varbinary صفرها را اصلاح نمی‌کنند (trim trailing zeros).

SET ANSI_PADDING {ON | OFF}

۳. مقدار CONCAT_NULL_YIELDS_NULL باید روی ON تنظیم شود.

این باعث می‌شود که هر مقداری با NULL با هم concat شود، یک مقدار NULL خروجی دهد.

SET CONCAT_NULL_YIELDS_NULL {ON | OFF}

۴. مقدار ANSI_WARNINGS رفتار استاندارد ISO را برای چندین حالت خطا مشخص می‌کند.

SET ANSI_WARNINGS {ON | OFF}

به عنوان یک قاعده کلی، درایور SQL Server Native ODBC و SQL Server Native Client OLE DB گزینه‌های بالا را روی ON قرار می‌دهند. مهمتر از آن، برای افزودن یک ایندکس در یک ستون محاسبه شده یا یک View، باید گزینه‌های بالا روشن باشند به علاوه گزینه‌های ARITHABORT=ON، QUOTED_IDENTIFIER=ON و NUMERIC_ROUNDABORT=OFF به این صورت باید تنظیم شوند. این تنظیمات، برای کد مورد آزمایش در این مقاله روی حالت ON قرار دارد.

مقادیر NULL اجتناب‌ناپذیر هستند

در هنگام تعریف جدول در عبارت CREATE TABLE، این امکان برای ما وجود دارد که یک ستون را NULL یا NOT NULL تعیین کنیم. اگر در تعریف ستون NULL را مشخص کنیم، ستون می‌تواند مقدار NULL داشته باشد. هنگامی که یک ردیف را وارد می‌کنیم، لازم نیست برای ستونی که NULL را مجاز کرده‌ایم، مقداری تعیین کنیم یا می‌توانیم NULL را به عنوان مقدار ستون، اختصاص دهیم. از طرف دیگر، می‌توانیم NOT NULL را در تعریف ستون مشخص کنیم، و باید یک مقدار غیر NULL به ستون را در INSERT اختصاص دهیم و همچنین نمی‌توانیم یک مقدار NULL را در UPDATE اختصاص دهیم.

با این‌ حال، حتی اگر ستون‌ها را در جداول به‌عنوان NOT NULL تعیین کنیم، باز هم مواردی وجود خواهد داشت که هنگام نوشتن کد T-SQL باید با مقادیر NULL سر و کار داشته باشیم.

در بخش‌های بعدی، مثال‌هایی را بیان می‌کنم که در آن‌ها باید مقادیر NULL را در نظر بگیریم تا به نتیجه صحیح برسیم و نشان دهیم که چگونه می‌توانیم از COALESCE برای تبدیل مقادیر NULL به مقادیر غیر NULL استفاده کنیم.

شِمای نمونه

شِمایی که در کد آزمایشی این مقاله برای هر بخش استفاده خواهد شد به صورت زیر است:

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

تعریف Coalesce در SQL Server

نقطه شروع ما در این مقاله ارائه یک تعریف دقیق از SQL COALESCE است. تعریف: هنگامی که COALESCE را در SQL Docs مشاهده می‌کنید، آن را در مسیر زیر خواهید یافت:

Transact-SQL (T-SQL) Reference / Language elements / Expressions

به طور کلی، شما از عبارت COALESCE در لیست ستونی دستور SELECT استفاده می‌کنید، اگرچه استفاده از آن به عبارت SELECT محدود نمی‌شود. COALESCE خود لیستی از ۱ تا N عبارت را به عنوان آرگومان می‌گیرد و مقدار اولین عبارت را که NULL نیست برمی‌گرداند.

چرا از COALESCE استفاده می‌کنیم؟

COALESCE یک راه ساده برای ارزیابی عبارات متعدد و برگرداندن عبارت غیر NULL است. شما می‌توانید مقداری را مشخص کنید تا در صورتی که تمام پارامترهای عبارت شما NULL ارزیابی شوند از آن استفاده کنید. شرایطی وجود دارد که ما به یک مقدار غیر NULL نیاز داریم و COALESCE راهی برای این جایگزینی فراهم می‌کند.

چه نسخه‌هایی از SQL Server از COALESCE پشتیبانی می‌کنند؟

۲۰۰۵, ۲۰۰۸, 2008R2, 2012, 2104, 2016, 2017, 2019, Azure

موارد کاربرد COALESCE در SQL Server

موارد زیر بر اساس داکیومنت COALESCE آورده شده است:

COALESCE ( expression [ ,...n ] )

یک کاربرد ساده از COALESCE برای برگرداندن یک مقدار جایگزین برای ستونی که NULL است استفاده می‌شود. کوئری T-SQL زیر، ستون [Tier] را برای یک مشتری برمی‌گرداند و اگر برای یک مشتری مقدار ستون [Tier] برابر NULL باشد، NONE را برمی‌گرداند:

SELECT [Name], COALESCE([Tier], 'NONE') AS [Tier]
FROM [dbo].[Customer]

خروجی به صورت زیر خواهد بود:

استفاده از Coalesce برای concat رشته‌ها

concat رشته‌های ساده می‌تواند نتایج غیرمنتظره‌ای ایجاد کند زمانی که یک یا چند ستونی که باید به هم متصل شوند دارای مقدار NULL باشند. در مثال زیر، [FirstName] و[LastName] به صورت NOT NULL تعریف شده‌اند اما[MiddleName] امکان دریافت مقدار NULL را دارد.

SELECT [FirstName] + ' ' + [MiddleName] + ' ' + [LastName] AS [FullName]
FROM [dbo].[Contact];

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

مشکل ایجاد شده به دلیل مقایر NULL در ستون[MiddleName] است. با این فرض که تنظیمات پایگاه داده برای CONCAT_NULL_YIELDS_NULL در حالت ON قرار دارد، ما می‌توانیم از COALESCE برای دریافت نتیجه صحیح، از کوئری زیر استفاده کنیم:

SELECT [FirstName] + ' ' + COALESCE([MiddleName], '') + ' ' + [LastName]
FROM [dbo].[Contact];

نتیجه کوئری بالا به صورت زیر است:در کوئری بالا اگر مقدار ستون [MiddleName] برابر NULL باشد در این شرایط COALESCE مقدار رشته خالی را برمی‌گرداند و در غیر این صورت خود مقدار ستون [MiddleName] را برمی‌گرداند.

مقایسه SQL Server Coalesce با Case

بر اساس کاربرد COALESCE، می‌توانید همین کار را با عبارت CASE انجام دهید. در واقع در داکیومنت COALESCE اشاره شده که بهینه‌ساز کوئری، COALESCE را به عنوان یک عبارت CASE بازنویسی می‌کند. این یک اثر جانبی مهم دارد که پس از بررسی کد نمونه زیر در مورد آن صحبت خواهم کرد.

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

در اینجا مشتریان و ستون‌های آخرین تاریخ مربوط به آن‌ها برای رویدادهای مختلف آمده است:کوئری T-SQL زیر از COALESCE برای تعیین آخرین تاریخ فعالیت برای هر مشتری استفاده می‌کند:

SELECT
[Name],
COALESCE([LastOrderDate],
[LastWebSearchDate],
[LastInquiryDate],
[LastPaymentDate],
[Created]) AS [LastActivityDate]
FROM [dbo].[Customer]

نتیجه کوئری بالا به صورت زیر است:به یاد داشته باشید که COALESCE اولین مقدار غیر NULL را در لیست آرگومان برمی‌گرداند. تمام ستون‌های آخرین تاریخ رویداد مقدار NULL را ذخیره می‌کنند زیرا زمانی که مشتری ایجاد می‌شود و برای مدت زمانی پس از ایجاد، این رویدادها واقعاً اتفاق نیفتاده‌اند. ستون Created اجازه NULL را نمی‌دهد، بنابراین آخرین آرگومان است. اگر هیچ یک از رویدادهای دیگر رخ نداده باشد، ستون LastActivityDate بازگردانده شده همان مقدار Created خواهد بود. با استفاده از COALESCE ترتیب ستون‌ها را برای بررسی اولین مقدار غیر NULL مشخص کرده‌ام.

در اینجا کوئری معادل T-SQL با استفاده از CASE آمده است:

SELECT
[Name]
,CASE WHEN [LastOrderDate] IS NOT NULL THEN [LastOrderDate]
WHEN [LastWebSearchDate] IS NOT NULL THEN [LastWebSearchDate]
WHEN [LastInquiryDate] IS NOT NULL THEN [LastInquiryDate]
WHEN [LastPaymentDate] IS NOT NULL THEN [LastPaymentDate]
ELSE [Created]
END AS [LastActivityDate]
FROM [dbo].[Customer];

خروجی کوئری بالا مشابه استفاده از COALESCE است:من COALESCE را به CASE ترجیح می‌دهم زیرا ساده‌تر است و به کد کمتری نیاز دارد. بااین‌حال، CASE امکان اجرای منطق بیشتری را برای شرایط فراهم می‌کند. در هر صورت شما همان نتیجه را می‌گیرید.

CASE هر کدام از عبارت‌های WHEN ارزیابی می‌کند و اولین عبارت را که TRUE است برمی‌گرداند، در غیر این صورت عبارت ELSE را برمی‌گرداند. عارضه جانبی CASE که قبلاً به آن اشاره کردم این است که عبارت موجود در CASE که TRUE را برمی‌گرداند برای بار دوم پس از بازگرداندن مورد ارزیابی قرار می‌گیرد. اگر عبارت مورد بحث یک کوئری باشد، تحت شرایط خاصی می‌تواند نتیجه متفاوتی را هنگام ارزیابی بار دوم نشان دهد.

مقایسه SQL Server Coalesce و ISNULL

هنگامی که برای اولین بار متوجه شدید که باید با مقادیر NULL سر و کار داشته باشید، احتمالاً تابع ISNULL را خواهید یافت. تعریف ساده‌ای دارد: NULL را با مقدار جایگزین مشخص شده، جایگزین می‌کند.

SELECT [Name], ISNULL([Tier], 'NONE') AS [Tier]
FROM dbo.Customer

نتایج کوئری بالا در زیر آمده است (همان طور که قبلاً از COALESCE استفاده کردید):

در تصمیم گیری بین COALESCE و ISNULL در SQL Server، من نکاتی را که به نظرم مهم‌ترین نکات است را در این قسمت ارائه می‌دهم تا به شما کمک می‌کند که بر اساس موقعیت‌های خاص از کدام یک استفاده کنید.

نکته اول: تعداد آرگومان

ISNULL دو آرگومان را می‌پذیرد: عبارتی برای بررسی NULL و عبارتی که در صورتی که عبارت مورد بررسی NULL باشد عبارت بازگشتی خواهد بود. COALESCE چندین آرگومان را می‌پذیرد و اگر هر عبارت NULL ارزیابی شود، اولین آرگومانی را که غیر NULL باشد را برمی‌گرداند.

نکته دوم: اولویت نوع داده

اولویت نوع داده هنگام ترکیب عبارات با انواع داده‌های مختلف به کار می‌رود. نوع داده با اولویت کمتر به نوع داده با اولویت بالاتر تبدیل می‌شود. ISNULL از نوع داده اولین عبارت (first expression) استفاده می‌کند در حالی که COALESCE از اولویت (precedence) نوع داده استفاده می‌کند. برای برجسته کردن این تفاوت مهم، به مثال زیر دقت کنید:

DECLARE @datetime DATETIME
SELECT COALESCE (@datetime, 0);

ممکن است فکر کنید از آنجایی که متغیر datetime@ به وضوح NULL است، SELECT مقدار صحیح صفر را برمی‌گرداند. با این‌ حال، از آنجا که COALESCE از اولویت نوع داده استفاده می‌کند و DATETIME دارای اولویت بالاتر از INTEGER است، مقدار بازگشتی ۱۹۰۰-۰۱-۰۱ ۰۰:۰۰:۰۰.۰۰۰ است.

سایر نکات

در اینجا چند نکته اضافی وجود دارد که می‌توان به آن‌ها اشاره کرد:

• تفاوت عملکرد بین استفاده از ISNULL در مقابل COALESCE در بیشتر موارد ناچیز است.
• COALESCE در استاندارد ANSI SQL است
• ISNULL با محصولات مایکروسافت سازگار نیست. به عنوان مثال، ISNULL در Access تابعی است که اگر آرگومان‌ها NULL و FALSE باشد، TRUE را برمی‌گرداند.

مفهوم SQL Coalesce با ستون‌های محاسبه شده

ستون محاسبه شده ستونی در جدول است که در آن مقدار ستون به عنوان نتیجه یک عبارت تعیین می‌شود. عبارت می‌تواند شامل ستون‌های دیگر در جدول و همچنین یک منطق باشد. به طور پیش‌فرض، یک ستون محاسبه شده در جدول ذخیره نمی‌شود و مقدار آن زمانی که از آن استفاده می‌کنید تعیین می‌شود. به عنوان مثال، می‌توانید آن را در یک عبارت SELECT قرار دهید. می‌توانید با اضافه کردن PERSISTED به تعریف، رفتار پیش‌فرض را بازنویسی کنید و مقدار را در جدول ذخیره کنید. اگر می‌خواهید ستون را ایندکس کنید، PERSISTED را اضافه کنید.

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

در یک سناریوی انبار داده، کاربران تجاری معمولاً زمان زیادی را صرف تجزیه و تحلیل مشتریان می‌کنند. به عنوان مثال، من یک ستون محاسبه‌ شده به نام Status را با منطق زیر برای تعیین مقدار پیاده‌سازی می‌کنم:

• اگر روزهای پس از آخرین سفارش NULL باشد، Status در حالت NEW قرار دارد.
• اگر روزهای پس از آخرین سفارش از ۱۸۰ روز بیشتر باشد ، Status در حالت CLOSED قرار دارد.
• اگر روزهای پس از آخرین سفارش از ۹۰ روز بیشتر باشد، Status در حالت INACTIVE قرار دارد.
• اگر هیچ یک از موارد بالا درست نباشد، Status در حالت ACTIVE قرار دارد.

اولین کاری که انجام می‌دهم این است که یک ستون برای DaysSinceLastOrder اضافه کنم تا منطق را ساده کنم. T-SQL زیر را می‌توان برای اضافه کردن یک ستون به یک جدول موجود به صورت مشروط استفاده کرد (یعنی اگر ستون وجود ندارد اضافه کنید، در غیر این صورت هیچ کاری انجام ندهید):

IF NOT EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Customer'
AND COLUMN_NAME = 'DaysSinceLastOrder')
ALTER TABLE [dbo].[Customer] ADD [DaysSinceLastOrder] INT;

در مرحله‌ای از فرآیند ETL ، روزهای پس از آخرین سفارش را دوباره محاسبه می‌کنیم. در اینجا یک عبارت UPDATE ساده برای انجام این کار وجود دارد:

UPDATE [dbo].[Customer]
SET [DaysSinceLastOrder] = DATEDIFF(Day, [LastOrderDate], GETDATE());

اکنون جدول Customer مقادیر زیر را برای DaysSinceLastOrder دارد:اکنون یک ستون محاسبه شده به جدول Customer اضافه می‌کنم تا Status را پیاده‌سازی کنم:

IF NOT EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Customer'
AND COLUMN_NAME = 'Status')
ALTER TABLE [dbo].[Customer]
ADD [Status] AS CASE
WHEN COALESCE ([DaysSinceLastOrder], -1) = -1 THEN 'NEW' -- customer has not placed an order
WHEN [DaysSinceLastOrder] > 180 THEN 'CLOSED'
WHEN [DaysSinceLastOrder] > 90 THEN 'INACTIVE'
ELSE 'ACTIVE'
END;

ستون محاسبه شده Status ابتدا از COALESCE در ستون DaysSinceLastOrder استفاده می‌کند تا اگر ستون NULL باشد مقدار ۱- را به عنوان جایگزین بر‌گرداند. به یاد داشته باشید که اگر مشتری هنوز سفارشی ثبت نکرده باشد، DaysSinceLastOrder برابر NULL خواهد بود.

افزودن یک ستون DaysSinceLastOrder و یک ستون محاسبه شده Status نمونه‌هایی از گام‌های اضافی هستند که می‌خواهم برای آسان کردن روال برای کاربران تجاری و در نهایت خودم انجام دهم. من سعی می‌کنم نتایج محاسبات را ارائه دهم و همچنین نیاز کاربران تجاری را برای مقابله با مقادیر NULL برطرف کنم. من نمی‌توانم هر موردی را که NULL برگردانده می‌شود حذف کنم، اما می‌توانم در این مورد حداقل تلاش کنم.

استفاده از Coalesce برای Pivot Data

گاهی اوقات شما می‌خواهید چندین ردیف از داده‌ها را بگیرید و آن‌ها را در یک لیست محدود که در یک ستون قرار دارد، Pivot کنید. برای آسان کردن این کار می‌توانید از COALESCE استفاده کنید. این مورد از جمله مهمترین کاربردهای COALESCE است. T-SQL زیر را می‌توان برای تنظیم برخی از داده‌های نمونه استفاده کرد:

CREATE TABLE [dbo].[Region] (
[Region] VARCHAR(20) NOT NULL
,[State] CHAR(2) NOT NULL)
INSERT [dbo].[Region] ([Region], [State])
VALUES
('RED', 'MD')
, ('RED', 'DE')
, ('WHITE', 'NJ')
, ('WHITE', 'NY')
, ('BLUE', 'VA')
, ('BLUE', 'WV')
, ('BLUE', 'NC');

از T-SQL زیر برای انتخاب حالت‌های یک region و قرار دادن آن‌ها در یک ستون منفرد که با سمیکالن مشخص شده است استفاده کنید:

DECLARE @REGION_LIST VARCHAR(MAX);
SELECT @REGION_LIST = COALESCE(@REGION_LIST, '') + [State] + '; '
FROM [dbo].[Region]
WHERE [Region] = 'BLUE';
SELECT @REGION_LIST AS [RegionList];

نتایج حاصل از کوئری به شرح زیر است:توجه داشته باشید که کوئری بالا، status را از هر سطر برگشتی به هم concat می‌کند. از COALESCE برای جلوگیری از concat میان status و متغیر REGION_LIST@ زمانی که NULL است استفاده می‌کند که با ردیف اول نتایج اتفاق می‌افتد.

استفاده از Coalesce برای به روز رسانی

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

SELECT
[Name]
,[LastOrderDate]
,[LastWebSearchDate]
,[LastInquiryDate]
,[LastPaymentDate]
FROM [dbo].[Customer];

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

در فرآیند ETL ما می‌خواهیم این تاریخ‌ها را بر اساس آخرین تراکنش‌ها به روز کنیم. فرآیند ETL جدول CustomerLastActivity را با یک ردیف برای مشتری، نوع فعالیت و آخرین تاریخ فعالیت حذف می‌کند و دوباره ایجاد می‌کند.می‌خواهیم داده‌ها را به شکلی pivot کنیم که بتوانیم از آن برای به‌ روز رسانی جدول Customer استفاده کنیم. در زیر نتایج کوئری به شکل مورد نظر ما آمده است:در اینجا عبارت T-SQL وجود دارد که می‌توانیم از آن برای pivot داده‌ها و انجام به روز رسانی جدول Customer استفاده کنیم:

;WITH CTE_ACTIVITY AS (
SELECT
[CustomerKey]
,[ORDER] AS [LastOrderDate]
,[SEARCH] AS [LastWebSearchDate]
,[INQUIRY] AS [LastInquiryDate]
,[PAYMENT] AS [LastPaymentDate]
FROM (
SELECT
[CustomerKey]
,[ActivityType]
,[ActivityDate]
FROM [dbo].[CustomerLastActivity]
) a
PIVOT (
MAX([ActivityDate])
FOR [ActivityType] IN (
[ORDER]
, [SEARCH]
, [INQUIRY]
, [PAYMENT]
)
) pvt
)
UPDATE c
SET[LastOrderDate] = COALESCE(a.[LastOrderDate], c.[LastOrderDate])
, [LastWebSearchDate] = COALESCE(a.[LastWebSearchDate], c.[LastWebSearchDate])
, [LastInquiryDate] = COALESCE(a.[LastInquiryDate], c.[LastInquiryDate])
, [LastPaymentDate] = COALESCE(a.[LastPaymentDate], c.[LastPaymentDate])
FROM [dbo].[Customer] c
JOIN [CTE_ACTIVITY] a
ON a.[CustomerKey] = c.[CustomerKey];

در زیر نکات اصلی برای عبارت T-SQL بالا آمده است:

• دستور SELECT در عبارت جدول مشترکCTE_ACTIVITY نتایج بالا را تولید می‌کند که در آن CustomerKey و چهار تاریخ آخرین فعالیت را داریم.
• عبارت UPDATE با جدول Customer و CTE_ACTIVITY جوین می‌شود و آخرین تاریخ فعالیت در جدول Customer را با استفاده از نتایج CTE_ACTIVITY به روز می‌کند.
• نتایج CTE_ACTIVITY ممکن است مقادیر NULL برای یک یا چند مورد از آخرین تاریخ‌های فعالیت داشته باشد.
• من از COALESCE برای بررسی آخرین تاریخ فعالیت در نتایج CTE_ACTIVITY استفاده می‌کنم و اگر مقدار NULL در ستون CTE_RESULTS نباشد، ستون را در جدول Customer به روز می‌کنم. در غیر این صورت مقدار موجود در جدول Customer را به مقدار فعلی آن به روز می‌کنم.

پس از اجرای T-SQL بالا، ردیف‌های جدول Customer دارای مقادیر زیر برای آخرین تاریخ‌های فعالیت هستند:

استفاده از Coalesce به همراه Incremental Update

Incremental Update یک الگوی کلاسیک است که در آن می‌خواهید داده‌ها را در یک جدول هدف با داده‌های یک جدول منبع که تغییر کرده است به روز کنید. SQL Server برخی از قابلیت‌های داخلی را برای انجام این کار فراهم می‌کند، مانند Change Tracking، Change Data Capture و حتی Replication. سناریوهایی وجود دارد که در آن شما قادر به استفاده از این راه‌حل‌های داخلی نیستید و باید راه‌حل‌های خود را به کار بگیرید. ممکن است پایگاه داده منبع یک حالت موقت پایگاه داده باشد یا پایگاه داده منبع توسط یک برنامه خارجی استفاده شود و اگر از یکی از مکانیسم‌های تغییر داخلی استفاده می‌کنید، vendor هیچ پشتیبانی ارائه نمی‌دهد.

در Incremental Update سفارشی، حداکثر آخرین تاریخ اصلاحی را که از منبع در هدف بارگذاری کرده‌اید، پیگیری می‌کنید. به عنوان مثال، آن را در یک جدول می‌توانید ذخیره کنید و هر بار که Incremental Update را انجام می‌دهید، ردیف‌هایی را از منبعی بازیابی می‌کنید که آخرین تاریخ اصلاح بیشتر از آخرین تاریخ بارگذاری شده است. روش دیگری که ممکن است کارساز باشد این است که حداکثر آخرین تاریخ اصلاح شده را در جدول هدف جستجو کنید و ردیف‌هایی را در جدول منبع که در آن آخرین تاریخ اصلاح شده بزرگتر از آنچه در جدول هدف است را بازیابی کنید. این ساده و واضح به نظر می‌رسد، اما مشکلی وجود دارد؟

مشکل، یکی از مواردی است که اغلب اتفاق نمی‌افتد، اما زمانی که اتفاق می‌افتد و شما برای آن برنامه‌ریزی نکرده‌اید، مشکل بزرگی را ایجاد می‌کند. شبه کد زیر برای Incremental Update با چیزی شبیه به این شروع می‌شود:

DECLARE @LAST_MODIFIED DATETIME;
SELECT @LAST_MODIFIED = MAX(LastModified)
FROM <target_tablename>;
INSERT <target_tablename> (<columnlist>)
SELECT <column_list>
FROM <source_tablename>
WHERE LastModified > @LAST_MODIFIED;

سخن پایانی

این بسیار ساده است اما مشکل مورد اشاره را نشان می‌دهد. شما مقدار مناسب برای متغیر LAST_MODIFIED@ را از target_table بازیابی می‌کنید و ردیف‌های اصلاح‌شده در منبع را از آخرین باری که این را اجرا کرده‌اید در هدف قرار می‌دهید. اگر target_tablename خالی باشد چه اتفاقی می‌افتد؟ پاسخ این است که LAST_MODIFIED @ مقدار NULL خواهد داشت و شما هیچ ردیفی را از source_tablename نمی‌توانید انتخاب کنید زیرا هیچ ردیفی وجود ندارد که LastModified > NULL گزاره‌ای درست باشد. اگر چه این حالت همیشه اتفاق نمی‌افتد اما احتمالاً حداقل یک بار خالی خواهد بود. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.

راه‌حل ساده ‌این است که باید بررسی کنید که LAST_MODIFIED @ برابر مقدار NULL است یا خیر. بیایید یک مورد واقعا ساده را در نظر بگیریم. شما نمی‌خواهید هیچ داده‌ای را از source_tablename که بیش از ۵ سال قدمت دارد بازیابی کنید. بعد از اینکه مقدار LAST_MODIFIED@ را به دست آوردید، T-SQL زیر را با استفاده از COALESCE اضافه کنید:

SET @LAST_MODIFIED = COALESCE(@LAST_MODIFIED, DATEADD(year, -5, GETDATE()))