淺析SQL Server的分頁(yè)方式 ISNULL與COALESCE性能比較
上一節(jié)我們講解了數(shù)據(jù)類型以及字符串中幾個(gè)需要注意的地方,這節(jié)我們繼續(xù)講講字符串行數(shù)同時(shí)也講其他內(nèi)容和穿插的內(nèi)容,簡(jiǎn)短的內(nèi)容,深入的講解。(可參看文章《詳解SQL Server中的數(shù)據(jù)類型》)
分頁(yè)方式
在SQL 2005或者SQL 2008中我們是利用ROW_NUMBER開窗函數(shù)來(lái)進(jìn)行分頁(yè)的,關(guān)于開窗函數(shù),我們?cè)赟QL進(jìn)階中會(huì)詳細(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];
上面代碼想必就不需要我解釋了,同時(shí)利用視圖也可以進(jìn)行分頁(yè)
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
下面我們來(lái)看看這二者利用SQL查詢語(yǔ)句和視圖有沒有性能上的差異呢?來(lái),檢驗(yàn)就是。
從這里我們可以看出,二者在性能開銷上并沒有什么不同,大部分情況下應(yīng)該是一樣的。但是在SQL 2011之后版本則出現(xiàn)新的語(yǔ)法來(lái)實(shí)現(xiàn)分頁(yè),估計(jì)我們依然還是利用的ROW_NUMBER,可能是為了能夠兼容SQL版本到2005,利用OFFSET-FETCH來(lái)進(jìn)行篩選過濾,它是基于SQL 2011之后才有。上述我們是取從31到40之間的數(shù)據(jù),如果用OFFSET-FETCH實(shí)現(xiàn),我們看這個(gè)函數(shù)字面意思就能知道跳過多少數(shù)據(jù)然后抓取多少數(shù)據(jù),所以我們需要跳過前面30條數(shù)據(jù),需要取接下來(lái)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
如果對(duì)SQL版本要求不低的話,利用OFFSET-FETCH來(lái)實(shí)現(xiàn)簡(jiǎn)直爽爆了。
COALESCE compare to ISNULL
上一節(jié)我們講了講一些字符串函數(shù),其中漏掉了一個(gè)字符串函數(shù)即COALESCE,這個(gè)函數(shù)在SQL 2008+上才有,其中還有其他幾個(gè)類似對(duì)字符串函數(shù)的處理,我們一起來(lái)看下。msdn對(duì)其定義為:按順序計(jì)算變量并返回最初不等于 NULL 的第一個(gè)表達(dá)式的當(dāng)前值。返回?cái)?shù)據(jù)類型優(yōu)先級(jí)最高的 expression 的數(shù)據(jù)類型。 如果所有表達(dá)式都不可為 Null,則結(jié)果的類型也不可為 Null。如果所有參數(shù)均為 NULL,則 COALESCE 返回 NULL。至少應(yīng)有一個(gè) Null值為NULL 類型。說白了就是對(duì)NULL的處理。我們簡(jiǎn)單來(lái)看下一個(gè)例子。
USE TSQL2012 GO SELECT custid, country, region, city, country + COALESCE(N''+ region, N'') + N',' + city AS location FROM Sales.Customers
上述我們可以看到,我們通過COALESCE函數(shù)來(lái)對(duì)NULL用空字符串來(lái)代替進(jìn)行處理。SQL 2012也引入了CONCAT函數(shù)來(lái)接收一個(gè)要連接的輸入列表并自動(dòng)以空字符串替換NULL,上述同樣可以用CONCAT函數(shù)來(lái)代替。
USE TSQL2012 GO SELECT custid, country, region, city, country + CONCAT(country,N''+ region, N',' + city) AS location FROM Sales.Customers
同時(shí)我們看到下圖知道,CONCAT函數(shù)參數(shù)至少要有兩個(gè):
msdn對(duì)CONCAT函數(shù)解釋為:CONCAT 采用可變數(shù)量的字符串參數(shù),并將它們串聯(lián)成單個(gè)字符串。 它需要至少兩個(gè)輸入值;否則將引發(fā)錯(cuò)誤。 所有參數(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ù),因?yàn)樗?ANSI SQL標(biāo)準(zhǔn)函數(shù)。認(rèn)為歸認(rèn)為,那么兩者到底有何不同呢,我們一起來(lái)看下。
(1)COALESCE和ISNULL處理數(shù)據(jù)類型優(yōu)先不同
COALESCE函數(shù)決定類型輸出基于數(shù)據(jù)類型優(yōu)先【data type precedence】,所以如下在處理INT時(shí),DATETIME優(yōu)先級(jí)高于INT。
DECLARE @int INT, @datetime DATETIME; SELECT COALESCE(@datetime, 0); SELECT COALESCE(@int, CURRENT_TIMESTAMP);
而對(duì)于ISNULL函數(shù),數(shù)據(jù)類型不受數(shù)據(jù)類型優(yōu)先影響,而是通過函數(shù)參數(shù)列表第一項(xiàng)影響,ISNULL在于交換而COALESCE在于所有參數(shù)查詢的合并。
DECLARE @int INT, @datetime DATETIME; SELECT ISNULL(@datetime, 0);
我們看看進(jìn)行如下操作會(huì)如何
DECLARE @int INT, @datetime DATETIME; SELECT ISNULL(@int, CURRENT_TIMESTAMP);
此時(shí)會(huì)出現(xiàn)無(wú)法將DATETIME轉(zhuǎn)換為INT
此時(shí)我們需要顯式進(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會(huì)造成數(shù)據(jù)丟失
我們?cè)賮?lái)看二者的對(duì)比的例子
DECLARE @c5 VARCHAR(5); SELECT 'COALESCE', COALESCE(@c5, 'Jeffcky Wang') UNION ALL SELECT 'ISNULL', ISNULL(@c5, 'Jeffcky Wang');
上述我們定義字符串變量長(zhǎng)度為5,而利用ISNULL字符串卻被截取了,在這里我們可以認(rèn)為ISNULL會(huì)導(dǎo)致數(shù)據(jù)的丟失而非出錯(cuò)。為什么會(huì)出現(xiàn)這樣的結(jié)果呢?上述我們已經(jīng)講過ISNULL受第一個(gè)參數(shù)影響,其長(zhǎng)度定義為5,所以只能為5,這是會(huì)進(jìn)行截取,而COALESCE函數(shù)著眼于檢測(cè)所有元素,此時(shí)為12所以會(huì)完全進(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對(duì)列計(jì)算時(shí)需要持久化
接下來(lái)我們看看二者最大的不同,我們通過計(jì)算列并且在其上面創(chuàng)建主鍵或者非空約束,看看ISNULL和COALESCE的區(qū)別
CREATE TABLE dbo.CreateISNULL ( a INT, b AS ISNULL(a, 15) PRIMARY KEY );
我們?cè)賮?lái)看看COALESCE函數(shù)來(lái)計(jì)算列
CREATE TABLE dbo.CreateCOALESCE ( a INT, b AS COALESCE(a, 15) PRIMARY KEY );
很明顯我們需要對(duì)列進(jìn)行持久化,通過添加PERSISTED關(guān)鍵字,如下即可。
CREATE TABLE dbo.CreateCOALESCE ( a INT, b AS COALESCE(a, 15) PERSISTED PRIMARY KEY );
我們?cè)賮?lái)看看一個(gè)二者的不同
DECLARE @c CHAR(10); SELECT 'x' + COALESCE(@c, '') + 'y'; SELECT 'x' + ISNULL(@c, '') + 'y';
我們到這里其實(shí)我們可以稍微概括下二者的區(qū)別:ISNULL著重于替換,而COALESCE著重于合并。COALESCE顯示忽略了NULL并用空字符串填充并壓縮,而ISNULL對(duì)NULL會(huì)用空字符串填充但不會(huì)壓縮。
(4)COALESCE函數(shù)支持超過兩個(gè)參數(shù)
對(duì)于多個(gè)參數(shù)輸入,ISNULL函數(shù)需要嵌套調(diào)用,而COALESCE能夠處理任何數(shù)量,至于上限不知,所以對(duì)于多個(gè)參數(shù)使用COALESCE更加,如下使用多個(gè)參數(shù)輸入。
SELECT COALESCE(a, b, c, d, e, f, g) FROM dbo.table;
而對(duì)于ISNULL,我們需要這樣做
SELECT ISNULL(a, ISNULL(b, ISNULL(c, ISNULL(d, ISNULL(e, ISNULL(f, g)))))) FROM dbo.table;
二者最終執(zhí)行時(shí)和利用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二者性能比較
我們來(lái)運(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());
我們有查詢四個(gè)場(chǎng)景:(1)兩個(gè)參數(shù)都為NULL(2)第一個(gè)參數(shù)為NULL(3)第二個(gè)參數(shù)為NULL(4)兩個(gè)參數(shù)都為NULL。每個(gè)場(chǎng)景測(cè)試十次,最終得出如下結(jié)果
從上看出二者性能并未有什么太大差異,我們不需要太擔(dān)心了吧,當(dāng)然上述場(chǎng)景并未完全覆蓋,至少還是能說明一部分。上述我們得到的結(jié)果查看的執(zhí)行時(shí)間,現(xiàn)在我們?cè)賮?lái)看看二者查詢執(zhí)行計(jì)劃。
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和自然語(yǔ)言描述不一致
為何是和自然語(yǔ)言描述不一致呢?也就是說我們當(dāng)判斷某個(gè)值為NULL會(huì)做什么,不為NULL再做什么,這時(shí)用查詢語(yǔ)言SQL描述如下:
IF ISNULL(something) -- do something
我們用自然語(yǔ)言角度來(lái)看,翻譯為如果something為NULL我們做什么,這個(gè)時(shí)候是不一致的。因?yàn)樵赟QL 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é)介紹之前我們?cè)賮?lái)看看一個(gè)例子,從而顛覆你的想法,讓你發(fā)狂。
SELECT ISNULL(NEWID(), 'JeffckyWang') AS Col1
這樣看是沒問題,我們將其插入到表中,再看對(duì)其列的描述
SELECT ISNULL(NEWID(), 'JeffckyWang') AS Col1 INTO dbo.IsNullExample2; EXEC sp_help 'dbo.IsNullExample2';
表中數(shù)據(jù)確實(shí)存在,但是對(duì)列的描述是可空的。
總結(jié)
上述重點(diǎn)講述了COALESCE和ISNULL函數(shù)區(qū)別之處,通過本節(jié)的講述二者的場(chǎng)景和區(qū)別,我們是不是應(yīng)該有了一點(diǎn)想法,到底是該用COALESCE還是ISNULL呢?大部分情況下還是利用COALESCE為好,一是此函數(shù)是作為SQL標(biāo)準(zhǔn)函數(shù),第二個(gè)相對(duì)于ISNULL它可以支持更多參數(shù),而ISNULL則需要嵌套,而對(duì)于ISNULL難道就沒有應(yīng)用場(chǎng)景了嗎,當(dāng)然有在查詢數(shù)據(jù)時(shí)判斷數(shù)據(jù)是否為NULL,這種情況下利用ISNULL,例如,如下
SELECT ISNULL(argument, '') INTO dbo.IsNullExample;
本文關(guān)于ISNULL和COALESCE的比較參考文章:Deciding between COALESCE and ISNULL in SQL Server。本節(jié)我們到此結(jié)束,簡(jiǎn)短的內(nèi)容,深入的理解,我們下節(jié)再會(huì),good night!
以上就是本文的全部?jī)?nèi)容,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助,如果有疑問大家可以留言交流,同時(shí)也希望多多支持本站!
版權(quán)聲明:本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有,歡迎引用、轉(zhuǎn)載,請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像,否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái),僅供學(xué)習(xí)參考,不代表本站立場(chǎng),如有內(nèi)容涉嫌侵權(quán),請(qǐng)聯(lián)系alex-e#qq.com處理。