程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> 淺析SQL Server的分頁方式 ISNULL與COALESCE性能比較,isnullcoalesce

淺析SQL Server的分頁方式 ISNULL與COALESCE性能比較,isnullcoalesce

編輯:更多數據庫知識

淺析SQL Server的分頁方式 ISNULL與COALESCE性能比較,isnullcoalesce


前言

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

分頁方式

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

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

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

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查詢語句和視圖有沒有性能上的差異呢?來,檢驗就是。

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

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來實現簡直爽爆了。

COALESCE compare to ISNULL

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

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

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

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

同時我們看到下圖知道,CONCAT函數參數至少要有兩個:

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

我們繼續回到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

此時我們需要顯式進行如下轉換才行

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

(2)ISNULL會造成數據丟失

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

DECLARE @c5 VARCHAR(5);
SELECT 'COALESCE', COALESCE(@c5, 'Jeffcky Wang')
UNION ALL
SELECT '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_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合並的結果是可空的而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並用空字符串填充並壓縮,而ISNULL對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 <= 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)兩個參數都為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
-- or
IF ISNULL(something, NULL) IS NULL
 -- do something
-- or
IF ISNULL(something, '') = ''
 -- do something

  (7)利用GUID看看奇葩的ISNULL

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

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

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

SELECT ISNULL(NEWID(), 'JeffckyWang') AS Col1
INTO 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!

以上就是本文的全部內容,希望本文的內容對大家的學習或者工作能帶來一定的幫助,如果有疑問大家可以留言交流,同時也希望多多支持幫客之家!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved