SQL Server 您所不知道的事(一)


  1. You Don’t Know SQL Server
    1. Escape Char
    2. True / False / Null
    3. 查詢效能
      1. 比較不同查詢的執行效率
      2. 關於效能要注意的事情
    4. Order By With Case
    5. 四捨六入五成雙
    6. ANSI 🆚 TSQL
    7. Add Opesrator 🆚 Concat Function
    8. DatePart 🆚 DateName
    9. Sys.SysLanguages
    10. CTE, Common Table Expression
    11. Outer Join 的 On 有事嗎?
    12. Delete With Select
    13. Insert Select 🆚 Select Into 🆚 Insert Exec
    14. IDENT_CURRENT

關於 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')