前言

上一節(jié)我們講解了數(shù)據(jù)類型以及字符串中幾個需要注意的地方，這節(jié)我們繼續(xù)講講字符串行數(shù)同時也講其他內(nèi)容和穿插的內(nèi)容，簡短的內(nèi)容，深入的講解。(可參看文章《詳解SQL Server中的數(shù)據(jù)類型》)

分頁方式

在SQL 2005或者SQL 2008中我們是利用ROW_NUMBER開窗函數(shù)來進(jìn)行分頁的，關(guān)于開窗函數(shù)，我們在SQL進(jìn)階中會詳細(xì)講講。如下：

USE TSQL2012
GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 31
SET @EndRow = 40
SELECT [address], [city], [region]
FROM (
SELECT [SC].[address], [SC].[city], [SC].[region],
ROW_NUMBER() OVER(
ORDER BY [SC].[address], [SC].[city],[SC].[custid]) AS RowNumber
FROM Sales.Customers SC) SaleCustomer
WHERE RowNumber > @StartRow AND RowNumber < @EndRow
ORDER BY [address], [city], [region];

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

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

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

從這里我們可以看出，二者在性能開銷上并沒有什么不同，大部分情況下應(yīng)該是一樣的。但是在SQL 2011之后版本則出現(xiàn)新的語法來實現(xiàn)分頁，估計我們依然還是利用的ROW_NUMBER，可能是為了能夠兼容SQL版本到2005，利用OFFSET-FETCH來進(jìn)行篩選過濾，它是基于SQL 2011之后才有。上述我們是取從31到40之間的數(shù)據(jù)，如果用OFFSET-FETCH實現(xiàn)，我們看這個函數(shù)字面意思就能知道跳過多少數(shù)據(jù)然后抓取多少數(shù)據(jù)，所以我們需要跳過前面30條數(shù)據(jù)，需要取接下來10條數(shù)據(jù)。

USE TSQL2012
GO
DECLARE @PageSize INT = 10, @PageIndex INT = 3
SELECT *
FROM Sales.Customers
ORDER BY custid
OFFSET @PageIndex * @PageSize ROWS
FETCH NEXT 10 ROWS ONLY
GO

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

COALESCE compare to ISNULL

上一節(jié)我們講了講一些字符串函數(shù)，其中漏掉了一個字符串函數(shù)即COALESCE，這個函數(shù)在SQL 2008+上才有，其中還有其他幾個類似對字符串函數(shù)的處理，我們一起來看下。msdn對其定義為：按順序計算變量并返回最初不等于 NULL 的第一個表達(dá)式的當(dāng)前值。返回數(shù)據(jù)類型優(yōu)先級最高的 expression 的數(shù)據(jù)類型。 如果所有表達(dá)式都不可為 Null，則結(jié)果的類型也不可為 Null。如果所有參數(shù)均為 NULL，則 COALESCE 返回 NULL。至少應(yīng)有一個 Null值為NULL 類型。說白了就是對NULL的處理。我們簡單來看下一個例子。

USE TSQL2012
GO
SELECT custid, country, region, city,
 country + COALESCE(N''+ region, N'') + N',' + city AS location
FROM Sales.Customers

上述我們可以看到，我們通過COALESCE函數(shù)來對NULL用空字符串來代替進(jìn)行處理。SQL 2012也引入了CONCAT函數(shù)來接收一個要連接的輸入列表并自動以空字符串替換NULL，上述同樣可以用CONCAT函數(shù)來代替。

USE TSQL2012
GO
SELECT custid, country, region, city,
country + CONCAT(country,N''+ region, N',' + city) AS location
FROM Sales.Customers

同時我們看到下圖知道，CONCAT函數(shù)參數(shù)至少要有兩個：

msdn對CONCAT函數(shù)解釋為：CONCAT 采用可變數(shù)量的字符串參數(shù)，并將它們串聯(lián)成單個字符串。 它需要至少兩個輸入值；否則將引發(fā)錯誤。 所有參數(shù)都隱式轉(zhuǎn)換為字符串類型，然后串聯(lián)在一起。 Null 值被隱式轉(zhuǎn)換為空字符串。 如果所有參數(shù)都為 Null，則返回 varchar(1) 類型的空字符串。 隱式轉(zhuǎn)換為字符串的過程遵循現(xiàn)有的數(shù)據(jù)類型轉(zhuǎn)換規(guī)則。 

我們繼續(xù)回到COALESCE函數(shù)，主要看看它與ISNULL函數(shù)的區(qū)別。

COALESCE與ISNULL函數(shù)探討

可能有些人認(rèn)為ISNULL比COALESCE函數(shù)更快，或者有人認(rèn)為ISNULL和COALESCE函數(shù)是等同，更有人認(rèn)為應(yīng)該傾向于使用COALESCE函數(shù)，因為它是 ANSI SQL標(biāo)準(zhǔn)函數(shù)。認(rèn)為歸認(rèn)為，那么兩者到底有何不同呢，我們一起來看下。

（1）COALESCE和ISNULL處理數(shù)據(jù)類型優(yōu)先不同

COALESCE函數(shù)決定類型輸出基于數(shù)據(jù)類型優(yōu)先【data type precedence】，所以如下在處理INT時，DATETIME優(yōu)先級高于INT。

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

而對于ISNULL函數(shù)，數(shù)據(jù)類型不受數(shù)據(jù)類型優(yōu)先影響，而是通過函數(shù)參數(shù)列表第一項影響，ISNULL在于交換而COALESCE在于所有參數(shù)查詢的合并。

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

我們看看進(jìn)行如下操作會如何

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

此時會出現(xiàn)無法將DATETIME轉(zhuǎn)換為INT

此時我們需要顯式進(jìn)行如下轉(zhuǎn)換才行

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

（2）ISNULL會造成數(shù)據(jù)丟失

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

DECLARE @c5 VARCHAR(5);
SELECT 'COALESCE', COALESCE(@c5, 'Jeffcky Wang')
UNION ALL
SELECT 'ISNULL', ISNULL(@c5, 'Jeffcky Wang');

上述我們定義字符串變量長度為5，而利用ISNULL字符串卻被截取了，在這里我們可以認(rèn)為ISNULL會導(dǎo)致數(shù)據(jù)的丟失而非出錯。為什么會出現(xiàn)這樣的結(jié)果呢？上述我們已經(jīng)講過ISNULL受第一個參數(shù)影響，其長度定義為5，所以只能為5，這是會進(jìn)行截取，而COALESCE函數(shù)著眼于檢測所有元素，此時為12所以會完全進(jìn)行返回。我們通過運(yùn)行如下就可以看出。

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

我們看到上述COALESCE合并的結(jié)果是可空的而ISNULL不是，有一點(diǎn)點(diǎn)不同。

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

接下來我們看看二者最大的不同，我們通過計算列并且在其上面創(chuàng)建主鍵或者非空約束，看看ISNULL和COALESCE的區(qū)別

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

我們再來看看COALESCE函數(shù)來計算列

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

很明顯我們需要對列進(jìn)行持久化，通過添加PERSISTED關(guān)鍵字，如下即可。

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';

我們到這里其實我們可以稍微概括下二者的區(qū)別：ISNULL著重于替換，而COALESCE著重于合并。COALESCE顯示忽略了NULL并用空字符串填充并壓縮，而ISNULL對NULL會用空字符串填充但不會壓縮。

（4）COALESCE函數(shù)支持超過兩個參數(shù)

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

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;

二者最終執(zhí)行時和利用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二者性能比較

我們來運(yùn)行如下查詢

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 <= 500000
BEGIN
 SET @v = COALESCE(@a, @b); --COALESCE
 SET @x += 1;
END
SELECT DATEDIFF(MILLISECOND, @time, SYSDATETIME());
GO
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 <= 500000
BEGIN
 SET @v = ISNULL(@a, @b); --ISNULL
 SET @x += 1;
END
SELECT DATEDIFF(MILLISECOND, @time, SYSDATETIME());

我們有查詢四個場景：（1）兩個參數(shù)都為NULL（2）第一個參數(shù)為NULL（3）第二個參數(shù)為NULL（4）兩個參數(shù)都為NULL。每個場景測試十次，最終得出如下結(jié)果

從上看出二者性能并未有什么太大差異，我們不需要太擔(dān)心了吧，當(dāng)然上述場景并未完全覆蓋，至少還是能說明一部分。上述我們得到的結(jié)果查看的執(zhí)行時間，現(xiàn)在我們再來看看二者查詢執(zhí)行計劃。

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;

上述可能不太準(zhǔn)確，還和硬件配置有關(guān)，也有可能COALESCE的性能差與ISNULL。二者性能應(yīng)該是沒什么很大差異。

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

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

IF ISNULL(something)
 -- do something

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

IF something IS NULL
 -- do something
-- or
IF ISNULL(something, NULL) IS NULL
 -- do something
-- or
IF ISNULL(something, '') = ''
 -- do something

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

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

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

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

SELECT ISNULL(NEWID(), 'JeffckyWang') AS Col1
INTO dbo.IsNullExample2;
EXEC sp_help 'dbo.IsNullExample2';

表中數(shù)據(jù)確實存在，但是對列的描述是可空的。

總結(jié)

上述重點(diǎn)講述了COALESCE和ISNULL函數(shù)區(qū)別之處，通過本節(jié)的講述二者的場景和區(qū)別，我們是不是應(yīng)該有了一點(diǎn)想法，到底是該用COALESCE還是ISNULL呢？大部分情況下還是利用COALESCE為好，一是此函數(shù)是作為SQL標(biāo)準(zhǔn)函數(shù)，第二個相對于ISNULL它可以支持更多參數(shù)，而ISNULL則需要嵌套，而對于ISNULL難道就沒有應(yīng)用場景了嗎，當(dāng)然有在查詢數(shù)據(jù)時判斷數(shù)據(jù)是否為NULL，這種情況下利用ISNULL，例如，如下

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

本文關(guān)于ISNULL和COALESCE的比較參考文章：Deciding between COALESCE and ISNULL in SQL Server。本節(jié)我們到此結(jié)束，簡短的內(nèi)容，深入的理解，我們下節(jié)再會，good night！

以上就是本文的全部內(nèi)容，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，同時也希望多多支持腳本之家！