德瑞克：SQL Server 學習筆記: 2017/10

2017-10-25

Use T-SQL to generate SQL Server scripts for INSERT, UPDATE, DELETE

SSMS 工具內建 [產生和發佈指令碼精靈] Generate and Publish Scripts Wizard 精靈，可以：

為多個物件建立 Transact-SQL 指令碼。此精靈會產生資料庫中所有物件的指令碼，或是您所選取之物件子集的指令碼。
此精靈具有許多適用於指令碼的選項，例如是否要包含權限、定序及條件約束等。

例如：將資料表的資料，產生成逐筆的 INSERT 陳述式。

以下示範使用 T-SQL 將資料表的資料，產生為逐筆的 INSERT, UPDATE, DELETE 陳述式。

範本

SELECT '要執行的 INSERT, UPDATE, DELETE 的指令碼' + [資料行名稱1]... FROM [資料表名稱]

更新

使用 IIF() 邏輯函數或 CASE() 運算式來處理 NULL 值問題。

Use T-SQL to generate SQL Server scripts and NULL value for INSERT, UPDATE, DELETE

http://sharedderrick.blogspot.tw/2018/04/use-t-sql-to-generate-sql-server.html

請參考以下範例：

-- 01_ 檢視資料

產生成逐筆的 UPDATE 陳述式

-- 02_Generate Scripts for UPDATE

-- 03_Error_UPDATE

產生成逐筆的 DELETE 陳述式

-- 04_Generate Scripts for DELETE

-- 05_Error_DELETE

產生成逐筆的 INSERT 陳述式

-- 06_Generate Scripts for INSERT

-- 07_Testing for INSERT

範例程式碼

20171024_Use T-SQL to generate SQL Server scripts for INSERT

https://drive.google.com/drive/folders/0B9PQZW3M2F40Mlhnams5ZUVqZlk?usp=sharing

參考資料

將資料表的資料，產生成 Insert 陳述式：使用 SQL Server Management Studio 2008 指令碼精靈
http://sharedderrick.blogspot.tw/2009/03/insert-sql-server-management-studio.html

產生指令碼 (SQL Server Management Studio)
https://docs.microsoft.com/zh-tw/sql/relational-databases/scripting/generate-scripts-sql-server-management-studio

[產生和發佈指令碼精靈] Generate and Publish Scripts Wizard
https://docs.microsoft.com/zh-tw/sql/relational-databases/scripting/generate-and-publish-scripts-wizard

2017-10-22

Find Foreign keys without index - 找出 FK 資料表缺少的索引

建議：在 FK 資料表上建立索引，可優化效能於：刪除、更新或查詢等作業上。

參考 [SQL Server] Suggestion: create Index on Foreign Keys - 建議在 FK 資料行上建立索引

可使用以下 T-SQL 指令碼，協助找出 FK 資料表缺少的索引。

示範環境：SQL Server 2017

-- 02_Find Foreign keys without index

-- 03_INX_Script

-- 04_INX_Script_2

修正版本 - 範例程式碼

感謝 Satheesh Variath 提供的 Find foreign keys not having supporting index 。
經過測試，目前版本不支援 case sensitive 環境。

修改後請參考：FK_Without_Index_2_For-Case-Sensitive.sql

下載 20171022_Find Foreign keys without index

-- 99_Error_case sensitive

參考資料

Find foreign keys not having supporting index
https://gallery.technet.microsoft.com/scriptcenter/Find-foreign-keys-not-6859c629

[SQL Server] Suggestion: create Index on Foreign Keys - 建議在 FK 資料行上建立索引
http://sharedderrick.blogspot.tw/2017/10/sql-server-suggestion-create-index-on.html

SQLskills SQL101: Indexes on Foreign Keys
https://www.sqlskills.com/blogs/kimberly/sqlskills-sql101-indexes-foreign-keys/

When did SQL Server stop putting indexes on Foreign Key columns?
https://www.sqlskills.com/blogs/kimberly/when-did-sql-server-stop-putting-indexes-on-foreign-key-columns/

2017-10-21

[SQL Server] Suggestion: create Index on Foreign Keys - 建議在 FK 資料行上建立索引

為了確保資料一致性(Data Integrity)，在SQL Server上可以使用：
PRIMARY KEY 條件約束、FOREIGN KEY 條件約束、UNIQUE 條件約束、CHECK 條件約束、DEFAULT 定義、允許 Null 值等。

其中，FOREIGN KEY 條件約束，依據預設值：

在 Primary key 資料行上建立索引。
未在 Foreign Keys 資料行上建立索引。

若在 PK 資料表上，刪除資料列、更新 PK 值時，系統必須額外再到 FK 資料表上做資料查詢比對，或是執行相對應的刪除或更新作業。
例如：
在 PK 資料表上刪除資料列，系統當下必須額外再到 FK 資料表上做查詢比對，執行刪除作業。

建議

在 FK 資料表上建立索引，可優化效能於：刪除、更新或查詢等作業上。

	刪除 PK 資料-子樹成本(Subtree Cost)	索引使用方式
有建立 FK， NO ACTION，沒有索引	28.779146	Clustered Index Scan/Table Scan
有建立 FK， NO ACTION，有建立索引	8.4662016	NonClustered Index Seek
有建立 FK，啟用 CASCADE，沒有索引	45.5311	Clustered Index Scan/Table Scan
有建立 FK，啟用 CASCADE，有建立索引	9.15951	NonClustered Index Seek
沒有 FK，沒有索引	0.0132841

對照起來，在 FK 上建立索引，可節省耗用的子樹成本達 5 倍。

可使用以下指令碼
Find Foreign keys without index - 找出 FK 資料表缺少的索引
http://sharedderrick.blogspot.tw/2017/10/find-foreign-keys-without-index-fk.html

示範：刪除 PK 資料列的效能分析

示範環境：SQL Server 2017

-- 01_ 檢視資料表的資料

(1) DELETE rows on Product_PK table and without FK Constraints

-- 02_刪除資料_沒有FK

(2) DELETE rows on Product_PK table and FK Constraints with NO ACTION

-- 03_建立FK

-- 04_List_All_FK_in_DB

-- 05_資料庫關聯圖

-- 06_資料庫關聯圖

-- 101_DELETE rows on Product_PK table and FK Constraints with NO ACTION and without Index

-- 102_DELETE rows on Product_PK table and FK Constraints with NO ACTION and without Index_2

-- 103_DELETE rows on Product_PK table and FK Constraints with NO ACTION and without Index_3

(3) DELETE rows on Product_PK table and FK Constraints with NO ACTION and with Index

-- 104_0_Create index on FK constraints tables

-- 104_DELETE rows on Product_PK table and FK Constraints with NO ACTION and with Index

-- 105_DELETE rows on Product_PK table and FK Constraints with NO ACTION and with Index_2

-- 106_DELETE rows on Product_PK table and FK Constraints with NO ACTION and with Index_3

(4) DELETE rows on Product_PK table and FK Constraints with DELETE CASCADE and without Index

-- 10_FK_ with DELETE CASCADE

-- 11_List_All_FK_in_DB

-- 12_資料庫關聯圖

-- 13_檢視執行計畫_叢集索引掃描_CASCADE

-- 14_檢視執行計畫_叢集索引掃描_CASCADE_2

-- 15_檢視執行計畫_叢集索引掃描_CASCADE_3

-- 16_檢視執行計畫_叢集索引掃描_CASCADE_4

(5) DELETE rows on Product_PK table and FK constraints tables with index and DELETE CASCADE

-- 17_在FK上建立索引

-- 18_檢視執行計畫_CASCADE_索引搜尋

-- 19_檢視執行計畫_CASCADE_索引搜尋_2

-- 20_檢視執行計畫_CASCADE_索引搜尋_3

-- 21_檢視執行計畫_CASCADE_索引搜尋_4

有建立 FK，啟用 CASCADE，沒有索引

-- 有建立 FK，啟用 CASCADE，沒有索引

-- 99_遺漏索引

檢索 FOREIGN KEY 條件約束

基於下列原因，在外部索引鍵上建立索引通常很有幫助：

(1) 變更 PRIMARY KEY 條件約束將根據關聯資料表內的 FOREIGN KEY 條件約束來做檢查。

(2) 當關聯資料表的資料藉著將資料表的 FOREIGN KEY 條件約束和另一個資料表的主要或唯一索引鍵資料行進行比對，而合併於查詢中時，通常會使用外部索引鍵資料行來聯結準則。
索引可讓 Database Engine 在外部索引鍵資料表中快速尋找相關資料。

不過，建立此索引並非必要。即使於資料表之間沒有定義 PRIMARY KEY 或 FOREIGN KEY 條件約束，仍可合併兩個關聯資料表的資料，不過兩個資料表之間的外部索引鍵關聯性代表這兩個資料表已經過最佳化，可合併於使用該索引鍵作為準則的查詢之中。

範例程式碼

20171021_Suggestion create Index on Foreign Key
https://drive.google.com/drive/folders/0B9PQZW3M2F40TGxfdUVWbVNkVmc?usp=sharing

參考資料

Find Foreign keys without index - 找出 FK 資料表缺少的索引
http://sharedderrick.blogspot.tw/2017/10/find-foreign-keys-without-index-fk.html

FOREIGN KEY 條件約束
https://technet.microsoft.com/zh-tw/library/ms175464(v=sql.105).aspx

SQL Server Performance Tuning Tip - Index Foreign Key Columns
https://www.mssqltips.com/sqlservertip/4305/sql-server-performance-tuning-tip--index-foreign-key-columns/

The Benefits of Indexing Foreign Keys
https://sqlperformance.com/2012/11/t-sql-queries/benefits-indexing-foreign-keys

SQLskills SQL101: Indexes on Foreign Keys
https://www.sqlskills.com/blogs/kimberly/sqlskills-sql101-indexes-foreign-keys/

When did SQL Server stop putting indexes on Foreign Key columns?
https://www.sqlskills.com/blogs/kimberly/when-did-sql-server-stop-putting-indexes-on-foreign-key-columns/

2017-10-15

[SQL Server] WHERE search condition with date: Use a range of dates and don't use CONVERT/CAST or BETWEEN (2)

延續前一篇的討論
[SQL Server] WHERE search condition with date: Use a range of dates and don't use CONVERT/CAST or BETWEEN (1)

建議使用比較運算子 + 邏輯運算子

使用比較運算子 + 邏輯運算子，符合 SARG，使用到索引
無須額外耗用 CONVERT/CAST 轉換資料類型
直觀的語法、語意，通用性高

WHERE TransactionDate >= '20140805' AND TransactionDate < '20140806'

為何不建議使用 BETWEEN ?

因為 BETWEEN 在處理 DATE/TIME 資料類型時，容易造成誤解，導致查詢結果不正確。

問題：以下 WHERE 搜尋條件是找出什麼資料？

WHERE TransactionDate BETWEEN '20140805' AND '20140806'

答案會是?

20140805 當日的全部資料
20140805 ~ 20140806 兩日的全部資料
20140805 全日加上 20140806 00:00:00:000 時間點的資料

用以下範例來討論

-- 01_檢視資料特性_Datetime

WHERE TransactionDate BETWEEN '20140805' AND '20140806'

卻是篩選出 20140805 當日加上 20140806 00:00:00:000 時間點的資料
回傳：4 筆資料列，Cost：0.150749

-- 02_BETWEEN_僅回傳4_rows

預期

應該回傳 5 筆資料列，卻僅回傳 4 筆資料列，缺少 1 筆資料列：2014-08-06 07:00:00.000

以條件式 BETWEEN '20140805' AND '20140806' 為例
多半寫出這類的語句，應該要找的是包含這兩天的全部資料。
預期應該回傳 5 筆資料列。

卻是

但實際上
BETWEEN '20140805 00:00:00.000' AND '20140806 00:00:00.000'

也就是：20140805 全日加上 20140806 00:00:00:000 時間點的資料。並未包含 20140806 當日的全部資料。

解決方案

該如何有效解決問題呢？

改用比較運算子 + 邏輯運算子來篩選出明確的日期時間範圍

例如：>= (大於或等於), <= (小於或等於), < (小於)。

WHERE TransactionDate >= '20140805' AND TransactionDate < '20140807'

-- 03_改用比較運算子_正確回傳5_rows

對照執行計畫

近一步觀察執行計畫，以 BETWEEN 跟比較運算子 +邏輯運算子來對照，BETWEEN 是耗用更多的資源。

-- 10_對照兩者執行計畫

-- 11_BETWEEN_搜尋述詞

觀察

搜尋述詞-起點

[tempdb].[dbo].[Transactions02].TransactionDate >= 純量運算子('2014-08-05 00:00:00.000')

搜尋述詞-終點

[tempdb].[dbo].[Transactions02].TransactionDate <= 純量運算子('2014-08-06 00:00:00.000')

留意

BETWEEN 的終點，卻是被解譯為：
<= 純量運算子('2014-08-06 00:00:00.000')

-- 12_BETWEEN_搜尋述詞_2

-- 13_BETWEEN_搜尋述詞_3

使用比較運算子 + 邏輯運算子

如預期的結果正確回傳。
語意沒有被誤解。

-- 14_比較運算子_搜尋述詞

-- 15_比較運算子_搜尋述詞_2

-- 16_比較運算子_搜尋述詞_3

範例程式碼

20171014_WHERE_DATE_Compare_Operator
https://drive.google.com/drive/folders/0B9PQZW3M2F40MDNoMGF4Wml6M00?usp=sharing

參考資料

[SQL Server] WHERE search condition with date: Use a range of dates and don't use CONVERT/CAST or BETWEEN (1)
http://sharedderrick.blogspot.tw/2017/10/sql-server-where-search-condition-with.html

What do BETWEEN and the devil have in common?
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx

It's Time for Time
http://www.kimballgroup.com/1997/07/its-time-for-time/

2017-10-14

[SQL Server] WHERE search condition with date: Use a range of dates and don't use CONVERT/CAST or BETWEEN (1)

在使用 WHERE 搜尋條件去篩選日期時間資料型態，建議：

使用比較運算子(Comparison Operators) 來篩選出明確的日期時間範圍

例如：>= (大於或等於), <= (小於或等於), < (小於)。

避免使用 CONVERT/CAST 或 BETWEEN。

目的是：

符合搜尋引數(Search Arguments, SARGs) ，效能優化。
資料正確性，沒有因不正確的假設導致不正確的結果。

建議用法：

當 WHERE 搜尋條件去篩選日期時間資料型態時
使用比較運算(Comparison Operators) 來篩選出明確的日期時間範圍

例如：>= (大於或等於), <= (小於或等於), < (小於)。

-- 使用 比較運算子(Comparison Operators) 來篩選出明確的日期時間範圍
-- 例如：>= (大於或等於),  <= (小於或等於), < (小於)
-- 需求：篩選 20140805 當日的資料，回傳 3 rows
SELECT TransactionID, ProductID, Quantity, TransactionDate 
FROM dbo.Transactions02
WHERE TransactionDate >= '20140805' AND TransactionDate<'20140806'

使用比較運算子，符合 SARG，使用到索引
無須額外耗用 CONVERT/CAST 轉型

WHERE TransactionDate >= '20140805' AND TransactionDate < '20140806'

對照 Subtree Cost(子樹成本)

語法	Subtree Cost(子樹成本)
使用比較運算子，符合 SARG，使用到索引	0.104689
額外使用 CONVERT/CAST	0.722386

可以看到明顯相差達 7 倍。

資料表 Transactions01 的 TransactionDate 沒有索引

--01. 檢視資料特性_Datetime

TransactionDate 資料行的資料類型是：Datetime

若需求是 篩選出 20140805 當日 的交易資訊，預期應該有 3 筆資料列符合回傳。

但是 WHERE 條件式寫成：WHERE TransactionDate = '20140805'，省略了時間值，這是篩選出 2014-08-05 00:00:00:000 時間點的資料值。
因此，僅回傳 1 筆資料列。

-- 02_只用等號_卻是僅回傳 1 row

若額外再去使用 CONVERT 或 CAST 將 DateTime 資料轉型為字串資料類型後做比對：

是可以正確搜尋到 3 筆資料列。
但這是違反符合 SARGs，導致無法使用到索引。
SQL Server Query Engine 也因為此資料類型的轉換，進而影響到查詢計劃選擇中的 "CardinalityEstimate" 或 "SeekPlan"。

WHERE CONVERT(varchar(30),TransactionDate, 112) = '20140805'

-- 03_CONVERT 轉換 DateTime 資料型別為字串

觀察 Execution Plan，可以看到特別顯示驚嘆號!

-- 04_Execution_Plan_issue

執行計畫內的警告訊息：
警告：

運算式 (CONVERT(varchar(30),[tempdb].[dbo].[Transactions01].[TransactionDate],112)) 中的類型轉換可能會影響到查詢計劃選擇中的 "CardinalityEstimate",
運算式 (CONVERT(varchar(30),[tempdb].[dbo].[Transactions01].[TransactionDate],112)=[@1]) 中的類型轉換可能會影響到查詢計劃選擇中的 "SeekPlan"

-- 05_Execution_Plan_issue

-- 06_Execution_Plan_issue

使用 SentryOne Plan Explorer，亦可觀察到 Warnings 訊息。

-- 07_Execution_Plan_issue

建議用法

使用比較運算(Comparison Operators) 來篩選出明確的日期時間範圍
例如：>= (大於或等於), <= (小於或等於), < (小於)

WHERE TransactionDate >= '20140805' AND TransactionDate < '20140806'

-- 08_沒有警告訊息的Exection_Plan

-- 09_沒有警告訊息的Exection_Plan

使用「搜尋引數(Search Argument,SARG)」

資料表 Transactions02 的 TransactionDate 有索引

-- 10_資料表 Transactions02 的 TransactionDate 有索引

不符合 SARG 規範，導致無法使用索引。
WHERE CONVERT(varchar(30),TransactionDate, 112) = '20140805'

-- 11_Non SARG 01

-- 12_Non SARG 01_Execution_Plan

不符合 SARG 規範，導致無法使用索引
WHERE DATEPART(yyyy,TransactionDate)=2014 AND DATEPART(mm,TransactionDate)=8 AND DATEPART(dd,TransactionDate) = 5

-- 13_Non SARG 02

-- 14_Non SARG 02_Execution_Plan

建議語法：符合 SARG，可以使用到索引

WHERE TransactionDate >= '20140805' AND TransactionDate < '20140806'

-- 015_SARG_Index

-- 016_SARG_Index_Execution_Plan