Agenda

前文

前文

本篇會分享在撰寫SQL時建議和比較分享

永遠先考慮T-SQL改寫

符合SARG Statement進行撰寫
- <、>、=、<=、>=、LIKE(視%所在位置，前面有%讓DB engine選擇不走INDEX)
不要在Where欄位做運算
使用ANSI 92相容的Join方式連接資料庫(避免使用舊式Join)
避免row by row操作

符合SARG格式的撰寫 + 適當Index設計可以解決大部分的效能問題

使用Like查詢建議

盡量別把%放在前面
如果查詢條件是CNAME LIKE '%范'想讓讓查詢走索引(seek 查詢),在後面加一個條件AND CNAME > ''讓查詢走Seek.

SELECT [MID]
      ,[NickName]
      ,[CName]
FROM [Member_Basic] WITH (NOLOCK)
WHERE CNAME LIKE '%范' AND CNAME > ''

使用Count函數建議

如果要取得筆數數量使用COUNT(*)比Count(c1)效能好.

Count(c1)會忽略c1 IS NULL數量.

另外如果[資料筆數]>2^15-1(大於INT最大值)筆數量可使用count_big(*)方法

如果需要COUNT資料很大造成效能影響可以透過DMV取得當前資料表數量(資料會不準確,因為並非及時更新)

SELECT SUM(p.rows)
FROM sys.partitions p 
WHERE p.[object_id] = object_id('dbo.Person') AND p.index_id < 2

SELECT SUM(p.row_count)
FROM sys.dm_db_partition_stats p 
WHERE p.[object_id] = object_id('dbo.Person')  AND p.index_id < 2

使用if exists (select 1 from dbo.table)取代count函式判斷資料是否存在

NOT IN vs NOT EXISTS

在查詢時避免使用NOT IN，因為會被QO改寫成 <> NULL，在SQL中NULL代表不知道(Unknow)，所以會什麼都查不到

因為此欄位是可空(NULL)時會造成非預期結果(因為NULL會造成判斷失誤NULL不是一個值他代表未知)
使用NOT EXISTS替代NOT IN
NOT EXISTS在可空欄位效能比NOT IN還要好(如果有建立Index兩個產生執行計畫理論上是一樣)

下面有一個範例來解說為什麼避免在可空欄位使用NOT IN

CREATE TABLE T(
    ID INT
)

INSERT INTO T VALUES (1)
INSERT INTO T VALUES (2)

SELECT *
FROM dbo.T
WHERE ID NOT IN (
    SELECT ID
    FROM (SELECT NULL v UNION ALL SELECT 1) t1
)

SELECT *
FROM dbo.T 
WHERE NOT EXISTS (
    SELECT ID
    FROM (SELECT NULL v UNION ALL SELECT 1) t1
    WHERE ID = v
)

--DROP TABLE T

上圖可以看到在T資料表中有兩筆資料，如果我們使用NOT IN和NOT EXISTS結果會不一樣，原因是使用NOT IN在判斷NULL時會造成NULL<>任何值,所以就撈不出任何資料，相反使用NOT EXISTS取得的結果就符合我們預期.

避免在Where條件中對欄位進行操作運算

@col > ‘’ 替代 @col NOT NULL AND <> ‘’

我們會有一種需求須要判斷此

CREATE TABLE T(
    Col VARCHAR(50)
)

INSERT INTO T
SELECT TOP 100000 NULL
FROM sys.all_columns c1
CROSS JOIN sys.all_columns c2


INSERT INTO T VALUES ('DANIEL')
INSERT INTO T VALUES ('DANIEL2')
INSERT INTO T VALUES ('')
INSERT INTO T VALUES ('')

--CREATE INDEX
CREATE INDEX IX_Col on dbo.T(
    Col
)

SELECT *
FROM dbo.T
WHERE col > ''

SELECT *
FROM dbo.T
WHERE col IS NOT NULL AND col <> ''
--DROP TABLE T

此文作者：Daniel Shih(石頭)
此文地址： https://isdaniel.github.io/dbnote/
版權聲明：本博客所有文章除特別聲明外，均採用 CC BY-NC-SA 3.0 TW 許可協議。轉載請註明出處！

Hello World

撰寫SQL的建議