SQL Server 您所不知道的事（一）

2021-10-15

關於 SQL Server 那些您所不知事情，第一篇的內容主要來自 T-SQL 語法，例如 Escape Char、CTE 以及更有效率的 Query 撰寫方式。

SQL Server Logo

You Don't Know SQL Server

Escape Char

SQL Server 如何使用 Escape Char (脫逸字元)，範例中要尋找所有客戶名稱包含 % 的資料列。

SELECT * FROM Customers WHERE Name LIKE '%\%%' {escape '\'}

True / False / Null

dataset = { A, A, B, B, C, N }

State Beloning
Not Null A A B B C
Null N
Equal A A A
Not Equal A B B C

查詢效能

比較不同查詢的執行效率

勾選「顯示評估執行計畫」(Ctrl + M)，上方紅框標住處。

同時選取不同的查詢語法後執行，執行時間佔比少者為效率較佳的查詢，第二個查詢故意加上排序，所以相對時間比 62 % ，費時較多，效率也較差。

關於效能要注意的事情

Merge Join > Hash Join > Nested Loop Join

Join > SubQuery (通常而言)

Index Seek > Index Scan

藉由將 STATISTICS IO 設定為 ON 以顯示查詢的 IO 統計資訊

SET statistics io ON

邏輯讀取 從資料快取中讀取的頁數
實體讀取 從磁碟中讀取的頁數

Order By With Case

使用 CASE 來進行排序，以達到客製化排序的方式，非常實用。

SELECT *
FROM   dbo.customers
ORDER  BY CASE
            WHEN country = 'USA' THEN 1
            WHEN country = 'UK' THEN 2
            ELSE 5
          END,
          city

MSDocs - CASE (Transact-SQL)

四捨六入五成雙

ISO 處理 float 的進位方法，下列為進位範例：

數值 進位
5.3 5
5.6 6
5.5 6
6.5 6
7.5 8

但在 SQL Server 中並非採用「四捨六入五成雙」，而是使用四捨五入的方式，有趣。

ANSI 🆚 TSQL

SQL 有 ANSI 標準，而 TSQL 除實踐 ANSI 標準外也會再加上專有的特色，但在語法的撰寫上仍應該優先以 ANSI 的方式，以求跨不同廠牌的資料庫管理系統都能使用，且提升易讀性。

  1. Convert_Time vs GetDate()
  2. Coalesce vs IsNull
  3. Cast vs Convert

Add Opesrator 🆚 Concat Function

當相加的項目存在 Null 的時候，Add Operator 會將結果以 Null 回應，Concat Function 則會忽略 Null 當作空白。

DatePart 🆚 DateName

DatePart 回傳的是 Int
DateName 回傳的是 VarChar 且顯示結果未受 Login User Language 影響其顯示

Sys.SysLanguages

-- 取得目前使用者的語系
SELECT @@language

-- 查詢系統中所有的語系資訊
SELECT * from sys.syslanguages

-- 設定目前使用者的語系以及查詢 localize 日期
SET Language N'日本語'
SELECT DateName(Month, Getdate()),
       DateName(WeekDay, Getdate())

CTE, Common Table Expression

Common Table Expression 解決 Outer Join 查詢上語法設計思考不易的痛點，改善語法的易讀性，而不影響效能 😀

Outer Join 的 On 有事嗎？

思考用 On 來限制 Outer Join 單方面資料表的作用 😶

Delete With Select

可以組合複雜的刪除條件，例如刪除符合特定 Join 結果的資料。

DELETE FROM A
FROM   dbo.customers A
       LEFT OUTER JOIN dbo.orders B
                    ON A.customerid = B.customerid
WHERE  B.orderid IS NULL

Insert Select 🆚 Select Into 🆚 Insert Exec

Insert Select
將查詢資料新增至已存在的資料表
Insert Exec
將執行 Stored Procedures 的結果新增至已存在的資料表
Select Into
將查詢資料建立至新資料表

IDENT_CURRENT

取得資料列自動編號的最新資料值，可以用於查詢最新的資料而不必 Order By。

Select IDENT_CURRENT('dbo.Employees')
#Database #SQLServer