SQL Server分頁方式、ISNULL與COALESCE效能比較（八）

標籤：persist   ted   require   部分   --   char   create   alt   ansi   

前言

上一節我們講解了資料類型以及字串中幾個需要注意的地方，這節我們繼續講講字串行數同時也講其他內容和穿插的內容，簡短的內容，深入的講解。

 分頁方式

在SQL 2005或者SQL 2008中我們是利用ROW_NUMBER開窗函數來進行分頁的，關於開窗函數，我們在SQL進階中會詳細講講。如下：

USE TSQL2012GODECLARE @StartRow INTDECLARE @EndRow INTSET @StartRow = 31SET @EndRow = 40SELECT  [address], [city], [region]FROM (SELECT [SC].[address], [SC].[city], [SC].[region],ROW_NUMBER() OVER(ORDER BY [SC].[address], [SC].[city],[SC].[custid]) AS RowNumberFROM Sales.Customers SC) SaleCustomerWHERE RowNumber > @StartRow AND RowNumber < @EndRowORDER BY [address], [city], [region];

上面代碼想必就不需要我解釋了，同時利用視圖也可以進行分頁

WITH SaleCustomer AS(SELECT [SC].[address], [SC].[city], [SC].[region],ROW_NUMBER() OVER(ORDER BY [SC].[address], [SC].[city],[SC].[custid]) AS RowNumberFROM Sales.Customers SC)SELECT [address], [city], [region]FROM SaleCustomerWHERE RowNumber > @StartRow AND RowNumber < @EndRowORDER BY [address], [city], [region]GO

下面我們來看看這二者利用SQL查詢語句和視圖有沒有效能上的差異呢？來，檢驗就是。

從這裡我們可以看出，二者在效能開銷上並沒有什麼不同，大部分情況下應該是一樣的。但是在SQL 2011之後版本則出現新的文法來實現分頁，估計我們依然還是利用的ROW_NUMBER，可能是為了能夠相容SQL版本到2005，利用OFFSET-FETCH來進行篩選過濾，它是基於SQL 2011之後才有。上述我們是取從31到40之間的資料，如果用OFFSET-FETCH實現，我們看這個函數字面意思就能知道跳過多少資料然後抓取多少資料，所以我們需要跳過前面30條資料，需要取接下來10條資料。

USE TSQL2012GODECLARE @PageSize INT = 10, @PageIndex INT = 3SELECT *FROM Sales.CustomersORDER BY custidOFFSET @PageIndex * @PageSize ROWSFETCH NEXT 10 ROWS ONLYGO

如果對SQL版本要求不低的話，利用OFFSET-FETCH來實現簡直爽爆了。

COALESCE compare to ISNULL

上一節我們講了講一些字串函數，其中漏掉了一個字串函數即COALESCE，這個函數在SQL 2008+上才有，其中還有其他幾個類似對字串函數的處理，我們一起來看下。msdn對其定義為：按順序計算變數並返回最初不等於 NULL 的第一個運算式的當前值。返回資料類型優先順序最高的 expression 的資料類型。 如果所有運算式都不可為 Null，則結果的類型也不可為 Null。如果所有參數均為 NULL，則 COALESCE 返回 NULL。至少應有一個 Null值為NULL 類型。說白了就是對NULL的處理。我們簡單來看下一個例子。

USE TSQL2012GOSELECT custid, country, region, city, country + COALESCE(N‘‘+ region, N‘‘) + N‘,‘ + city AS locationFROM Sales.Customers

上述我們可以看到，我們通過COALESCE函數來對NULL用Null 字元串來代替進行處理。SQL 2012也引入了CONCAT函數來接收一個要串連的輸入列表並自動以Null 字元串替換NULL，上述同樣可以用CONCAT函數來代替。

USE TSQL2012GOSELECT custid, country, region, city, country + CONCAT(country,N‘‘+ region, N‘,‘ + city) AS locationFROM Sales.Customers

同時我們看到知道，CONCAT函數參數至少要有兩個：

msdn對CONCAT函數解釋為：CONCAT 採用可變數量的字串參數，並將它們串聯成單個字串。 它需要至少兩個輸入值；否則將引發錯誤。 所有參數都隱式轉換為字串類型，然後串聯在一起。 Null 值被隱式轉換為空白字串。 如果所有參數都為 Null，則返回 varchar(1) 類型的Null 字元串。 隱式轉換為字串的過程遵循現有的資料類型轉換規則。  

我們繼續回到COALESCE函數，主要看看它與ISNULL函數的區別。

COALESCE與ISNULL函數探討

可能有些人認為ISNULL比COALESCE函數更快，或者有人認為ISNULL和COALESCE函數是等同，更有人認為應該傾向於使用COALESCE函數，因為它是 ANSI SQL標準函數。認為歸認為，那麼兩者到底有何不同呢，我們一起來看下。

（1）COALESCE和ISNULL處理資料類型優先不同

COALESCE函數決定類型輸出基於資料類型優先【data type precedence】，所以如下在處理INT時，DATETIME優先順序高於INT。

DECLARE @int INT, @datetime DATETIME;SELECT COALESCE(@datetime, 0);SELECT COALESCE(@int, CURRENT_TIMESTAMP);

而對於ISNULL函數，資料類型不受資料類型優先影響，而是通過函數參數列表第一項影響，ISNULL在於交換而COALESCE在於所有參數查詢的合并。

DECLARE @int INT, @datetime DATETIME;SELECT ISNULL(@datetime, 0);

我們看看進行如下操作會如何

DECLARE @int INT, @datetime DATETIME;SELECT ISNULL(@int, CURRENT_TIMESTAMP);

此時會出現無法將DATETIME轉換為INT

（2）ISNULL會造成資料丟失

我們再來看二者的對比的例子

DECLARE @c5 VARCHAR(5);SELECT ‘COALESCE‘, COALESCE(@c5, ‘Jeffcky Wang‘)UNION ALLSELECT ‘ISNULL‘,   ISNULL(@c5,   ‘Jeffcky Wang‘);

上述我們定義字串變數長度為5，而利用ISNULL字串卻被截取了，在這裡我們可以認為ISNULL會導致資料的丟失而非出錯。為什麼會出現這樣的結果呢？上述我們已經講過ISNULL受第一個參數影響，其長度定義為5，所以只能為5，這是會進行截取，而COALESCE函數著眼於檢測所有元素，此時為12所以會完全進行返回。我們通過運行如下就可以看出。

DECLARE @c5 VARCHAR(5);SELECT   c = COALESCE(@c5, ‘Jeffcky Wang‘),   i = ISNULL(@c5, ‘Jeffcky Wang‘)INTO dbo.TestISNULL_COALESCESELECT name, t = TYPE_NAME(system_type_id), max_length, is_nullable  FROM sys.columns  WHERE [object_id] = OBJECT_ID(‘dbo.TestISNULL_COALESCE‘);

我們看到上述COALESCE合并的結果是可空的而ISNULL不是，有一點點不同。

（3）COALESCE對列計算時需要持久化

接下來我們看看二者最大的不同，我們通過計算資料行並且在其上面建立主鍵或者非空約束，看看ISNULL和COALESCE的區別

CREATE TABLE dbo.CreateISNULL(  a INT,  b AS ISNULL(a, 15) PRIMARY KEY);

我們再來看看COALESCE函數來計算資料行

CREATE TABLE dbo.CreateCOALESCE(  a INT,  b AS COALESCE(a, 15) PRIMARY KEY);

很明顯我們需要對列進行持久化，通過添加PERSISTED關鍵字，如何即可。

CREATE TABLE dbo.CreateCOALESCE(  a INT,  b AS COALESCE(a, 15) PERSISTED PRIMARY KEY);

我們再來看看一個二者的不同

DECLARE @c CHAR(10);SELECT ‘x‘ + COALESCE(@c, ‘‘) + ‘y‘;SELECT ‘x‘ + ISNULL(@c, ‘‘) + ‘y‘;

我們到這裡其實我們可以稍微概括下二者的區別：ISNULL著重於替換，而COALESCE著重於合并。COALESCE顯示忽略了NULL並用Null 字元串填充並壓縮，而ISNULL對NULL會用Null 字元串填充但不會壓縮。

（4）COALESCE函數支援超過兩個參數 

對於多個參數輸入，ISNULL函數需要嵌套調用，而COALESCE能夠處理任何數量，至於上限不知，所以對於多個參數使用COALESCE更加，如下使用多個參數輸入。

SELECT COALESCE(a, b, c, d, e, f, g) FROM dbo.table;

而對於ISNULL，我們需要這樣做

SELECT ISNULL(a, ISNULL(b, ISNULL(c, ISNULL(d, ISNULL(e, ISNULL(f, g)))))) FROM dbo.table;

二者最終執行時和利用CASE一樣

CASE         WHEN [tempdb].[dbo].[table].[a] IS NOT NULL THEN [tempdb].[dbo].[table].[a]    ELSE CASE WHEN [tempdb].[dbo].[table].[b] IS NOT NULL THEN [tempdb].[dbo].[table].[b]    ELSE CASE WHEN [tempdb].[dbo].[table].[c] IS NOT NULL THEN [tempdb].[dbo].[table].[c]    ELSE CASE WHEN [tempdb].[dbo].[table].[d] IS NOT NULL THEN [tempdb].[dbo].[table].[d]    ELSE CASE WHEN [tempdb].[dbo].[table].[e] IS NOT NULL THEN [tempdb].[dbo].[table].[e]    ELSE CASE WHEN [tempdb].[dbo].[table].[f] IS NOT NULL THEN [tempdb].[dbo].[table].[f]    ELSE [tempdb].[dbo].[table].[g] END END END END END END

（5）COALESCE和ISNULL二者效能比較

我們來運行如下查詢

DBCC DROPCLEANBUFFERS;DECLARE  @a    VARCHAR(5),  -- = ‘str_a‘, -- this line changed per test @b    VARCHAR(5),  -- = ‘str_b‘, -- this line changed per test @v    VARCHAR(5),  @x    INT          = 0, @time DATETIME2(7) = SYSDATETIME();WHILE @x <= 500000BEGIN SET @v = COALESCE(@a, @b); --COALESCE SET @x += 1;ENDSELECT DATEDIFF(MILLISECOND, @time, SYSDATETIME());GODBCC DROPCLEANBUFFERS;DECLARE  @a    VARCHAR(5),  -- = ‘str_a‘, -- this line changed per test @b    VARCHAR(5),  -- = ‘str_b‘, -- this line changed per test @v    VARCHAR(5),  @x    INT          = 0, @time DATETIME2(7) = SYSDATETIME();WHILE @x <= 500000BEGIN SET @v = ISNULL(@a, @b); --ISNULL SET @x += 1;ENDSELECT DATEDIFF(MILLISECOND, @time, SYSDATETIME());

我們有查詢四個情境：（1）兩個參數都為NULL（2）第一個參數為NULL（3）第二個參數為NULL（4）兩個參數都為NULL。每個情境測試十次，最終得出如下結果

從上看出二者效能並未有什麼太大差異，我們不需要太擔心了吧，當然上述情境並未完全覆蓋，至少還是能說明一部分。上述我們得到的結果查看的執行時間，現在我們再來看看二者查詢執行計畫。

SELECT COALESCE((SELECT MAX(index_id) FROM sys.indexes WHERE [object_id] = t.[object_id]), 0)  FROM sys.tables AS t;  SELECT ISNULL((SELECT MAX(index_id) FROM sys.indexes WHERE [object_id] = t.[object_id]), 0)  FROM sys.tables AS t;

上述可能不太準確，還和硬體設定有關，也有可能COALESCE的效能差與ISNULL。二者效能應該是沒什麼很大差異。

（6）ISNULL和自然語言描述不一致

為何是和自然語言描述不一致呢？也就是說我們當判斷某個值為NULL會做什麼，不為NULL再做什麼，這時用查詢語言SQL描述如下：

IF ISNULL(something)  -- do something

我們用自然語言角度來看，翻譯為如果something為NULL我們做什麼，這個時候是不一致的。因為在SQL Server中沒有布爾實值型別，上述我們只能進行如下轉換

IF something IS NULL  -- do something  -- orIF ISNULL(something, NULL) IS NULL  -- do something  -- orIF ISNULL(something, ‘‘) = ‘‘  -- do something

（7）利用GUID看看奇葩的ISNULL

在本節介紹之前我們再來看看一個例子，從而顛覆你的想法，讓你發狂。

SELECT ISNULL(NEWID(), ‘JeffckyWang‘) AS Col1

這樣看是沒問題，我們將其插入到表中，再看對其列的描述

SELECT ISNULL(NEWID(), ‘JeffckyWang‘) AS Col1INTO dbo.IsNullExample2;EXEC sp_help ‘dbo.IsNullExample2‘;

表中資料確實存在，但是對列的描述是可空的。

總結 

上述重點講述了COALESCE和ISNULL函數區別之處，通過本節的講述二者的情境和區別，我們是不是應該有了一點想法，到底是該用COALESCE還是ISNULL呢？大部分情況下還是利用COALESCE還是為好，一是此函數是作為SQL標準函數，第二個相對於ISNULL它可以支援更多參數，而ISNULL則需要嵌套，而對於ISNULL難道就沒有應用情境了嗎，當然有在查詢資料時判斷資料是否為NULL，這種情況下利用ISNULL，例如，如下

SELECT      ISNULL(argument, ‘‘) INTO dbo.IsNullExample;

本文關於ISNULL和COALESCE的比較參考文章：Deciding between COALESCE and ISNULL in SQL Server。本節我們到此結束，簡短的內容，深入的理解，我們下節再會，good night！

SQL Server分頁方式、ISNULL與COALESCE效能比較（八）