[Challenge] 計算 T-SQL 挑戰贏家的 SQL Stars 點數

Beyond Relational TSQL Challenge 38

This challenge involves parsing a delimited string and applying some logic to perform some calculations based on the position of tokens within the string. This challenge has got a fake (or self?) reference to the TSQL Challenges itself. Your job is to calculate the SQL Stars awarded to each of the winners by parsing a delimited string.

• Sample Data

Challenge         Winners
----------------- ---------------------------------------------------
TSQL Challenge 25 leszek,lmu92/xaloc,,karinloos,Erick,,,dishdy,jobacr
TSQL Challenge 24 ,Dalibor,,lwkwok,,,,lmu92,dishdy
TSQL Challenge 23 Mark,Dalibor,Beakdan,,Parth,Ramireddy,lmu92,Ruby

Note that the data given above is for representational purpose only and it may not match with the actual SQL Stars awarded to the people mentioned in the sample data.

• Expected Results

Winner    SQL Stars
--------- ---------
Dalibor        18
lmu92          16
leszek         10
Mark           10
xaloc           9
Beakdan         8
karinloos       7
lwkwok          7
Erick           6
Parth           6
dishdy          5
Ramireddy       5
Ruby            3
jobacr          2

• Rules
1. Each winner gets 1 to 10 SQL Stars. The highest is 10 and lowest is 1.
2.  The name on the left most position will get 10 SQL Stars and the next gets 9 and so on. In the third row Mark gets 10 SQL Stars and Dalibor gets 9 etc.
3. You should process only people who come in the top 10 positions. The input column may have more names, but they should be ignored.
4. There may be cases where more than one user is awarded the same number of stars. In such a case, those usernames will be separated by a “/”. In the first example, “lmu92” and “xaloc” are given 9 SQL Stars each.
5. It is quite possible that there is no one to claim the SQL Stars at a given position. In the second row, no one claims 10 SQL Stars. Dalibor gets 9 stars, no one to claim 8th position and lwkwok gets 7 SQL Stars etc.
6. Output should be ordered by the number of SQL Stars each user gets. People with highest number of SQL Stars should come on top. If more than one user gets the same number of total stars, secondary order should be done by username (case insensitive).
    

[SSMS] 不允許儲存變更

利用 SSMS 2012 來更改 Table Schema，儲存變更時發生的警告訊息。

• ［工具］ => ［選項］ => ［設計師］ => 取消 ［防止儲存需要資料表重建的變更］，就可以避免這個問題。

參考資料
• 當您嘗試在 SQL Server 中儲存的資料表時，出現錯誤訊息： 「 儲存變更並不允許
• 選項 (設計師/資料表和資料庫設計工具頁面)

[SQL] 統計資訊老舊

進行 TOP 10 高成本語法校正時，發現這段 T-SQL 語法有異常高的 Logical Read，特地把它抓出來了解一下。

SELECT 
	.......
FROM 人事考核 AS C 
    JOIN 人事考核明細 AS D ON 
    JOIN 考核項目 AS I ON .....
    JOIN 員工基本資料表 AS T1 ON C.檢核人員工編號 = T1.員工編號
    JOIN 員工基本資料表 AS T2 ON D.員工編號 = T2.員工編號
    JOIN 組織表 AS O ON T2.部門編號 = O.部門編號
WHERE LEFT(C.考核月份,3) = '101'

執行上述語法並搭配 SET STATISTICS IO NO 、SET STATISTICS TIME ON 和 包含實際執行計畫功能，可以抓出每個 Table Logical Read 和 Query Optimizer（簡稱 QO）產生執行計畫的過程。

Logical Read 分析

發現 [組織表]、[員工基本資料表] 和 [考核項目] 產生大量的 Logical Read。

SQL Server 剖析與編譯時間:
CPU 時間 = 0 ms，經過時間 = 94 ms。

(10300 個資料列受到影響)
資料表 '組織鰾'。掃描計數 0，邏輯讀取 20600，實體讀取 0，讀取前讀取 0。
資料表 '員工基本資料表'。掃描計數 0，邏輯讀取 20762，實體讀取 0，讀取前讀取 0。
資料表 '考核項目'。掃描計數 0，邏輯讀取 20600，實體讀取 0，讀取前讀取 0。
資料表 '人事考核明細'。掃描計數 81，邏輯讀取 614，實體讀取 0，讀取前讀取 0。
資料表 '人事考核'。掃描計數 1，邏輯讀取 41，實體讀取 0，讀取前讀取 0。

SQL Server 執行次數:
CPU 時間 = 93 ms，經過時間 = 313 ms。

執行計畫圖一

發現 [人事考核] 是利用 Index Scan 抓取資料，可能是因為 WHERE 條件內有下 LEFT() 轉換，造成 QO 無法正確使用 Index。

執行計畫圖二

檢視［執行計畫圖一］中標示數字 operator 明細（和［執行計畫圖二］標示數字相對應），發現 Estimated Rows（估計資料列）和 Actual Rows（實際資料列），有很大的差異，之所以會特別注意這三個明細，主因是 1、2 是高成本 operator，而 3 [人事考核明細] 是資料筆數最多的 Table（21744 筆）。

 

從 Estimated Rows 和 Actual Rows 差距過大這點推測，大概是因為統計資訊老舊導致 QO 沒有建立最佳執行計畫來執行，利用下述語法來查詢統計資訊更新時間。

SELECT 
    T.Name AS TableName , 
    S.Name AS StatName ,
    STATS_DATE(S.[object_id],S.stats_id) AS stats_update_date
FROM sys.tables AS T
    JOIN sys.stats AS S ON T.[object_id] = S.[object_id]
WHERE S.auto_created = 0
   AND T.Name IN ('員工基本資料表','人事考核','人事考核明細') -- 輸入要查詢的 TableName

從下圖查詢結果就可以看出 [員工基本資料表] 和 [人事考核] 這兩個 Table 的統計資訊都還是 2011-09-28，現在都已經是 2012-11-07。

更新統計資料

UPDATE STATISTICS 人事考核 WITH FULLSCAN
UPDATE STATISTICS 員工基本資料表 WITH FULLSCAN

更新後執行 T-SQL 的 Logical Read 分析

SQL Server 剖析與編譯時間:
CPU 時間 = 0 ms，經過時間 = 0 ms。

(10300 個資料列受到影響)
資料表 'Worktable'。掃描計數 0，邏輯讀取 0，實體讀取 0，讀取前讀取 0。
資料表 '人事考核明細'。掃描計數 1，邏輯讀取 153，實體讀取 0，讀取前讀取 0。
資料表 '考核項目'。掃描計數 1，邏輯讀取 2，實體讀取 0，讀取前讀取 0。
資料表 '人事考核'。掃描計數 1，邏輯讀取 41，實體讀取 0，讀取前讀取 0。
資料表 '員工基本資料表'。掃描計數 2，邏輯讀取 34，實體讀取 0，讀取前讀取 0。
資料表 '組織圖'。掃描計數 1，邏輯讀取 2，實體讀取 0，讀取前讀取 0。

SQL Server 執行次數:
CPU 時間 = 47 ms，經過時間 = 219 ms。

更新後執行 T-SQL 的執行計畫

自動更新統計資訊的觸發是有其條件，從本例來看 [人事考核] 和 [員工基本資料表] 這兩個 Table 的資料筆數分別是 147 和 201，根本無法觸發自動更新統計資訊，也因此導致 QO 使用錯誤統計資訊來產生執行計畫，難怪兩者是高成本的 operator，可見自動搭配手動更新統計資訊是有其必要性的；另從舊新執行計畫中可以發現，原本都是 Index Seek，更新後全部都變成 Index Scan，剛好可以打破 Index Seek 效能一定會比 Index Scan 好的迷思，本例的三個關鍵 Table 總資料量算是極少，但 T-SQL 要抓取大量資料（[人事考核明細] 一半資料量 10300 / 21744），因此 Index Scan 效能反而比 Index Seek 更好。

這個問題可以在 Client 端 SQL Server 重現，紀錄一下這次 Turning 經驗，雖然說新執行計畫看起來還不是很好（SSMS 還有建議的 missing index），實務上這 T-SQL 使用的機會，也是少得可憐，在先天不良（[人事考核明細] PK 是由 5 個 column 組成，實在是太寬）且後天失調（T-SQL 不符合 SARG 原則）的情況下算是不錯。

[SQL] sp_procoption

sp_procoption 可以設定當啟動 SQL Server instance 時，會執行已經設定為自動執行的預存程序。

語法和應用

-- 語法
sp_procoption 
      [ @ProcName ]    -- 預存程序名稱
    , [ @OptionName ]  -- 只有一個設定值-startup
    , [ @OptionValue ] -- 設定值為 on（開啟）或 off（關閉）

-- 應用
-- 設定預存程序為自動執行
EXEC sp_procoption N'SPName' , 'startup' , 'on'
-- 取消預存程序的自動執行
EXEC sp_procoption N'SPName' , 'startup' , 'off'

-- 其他注意事項
-- 1. 自動啟動的預存程序必須在 master 資料庫內
-- 2. 預存程序不能包含 INPUT 或 OUTPUT 參數

scan for startup procs 伺服器組態

理論上要使用 sp_procoption 要先開啟 scan for startup procs 伺服器組態設定，但設定動作 sp_procoption 會自動進行，當將第一個預存程序設定自動執行時，scan for startup procs 會自動設定為值 1，當最後一個預存程序取消自動執行時，scan for startup procs 會自動設定為值 0，當然也可以在 SSMS 或利用 T-SQL 語法來設定。

EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE
GO
EXEC sp_configure 'scan for startup procs', 1 ; -- 啟動設為 1，取消設為 0
GO
RECONFIGURE
GO

[Challenge] 反轉字串

Beyond Relational TSQL Challenge 3

挑戰不利用 REVERSE() 函數來反轉字串，REVERSE() 函數本身沒有任何問題，實務上仍是利用它來反轉字串，在此只是來考驗對 T-SQL 結果集（set based operations）的操作。

不單獨反轉單一字串，反轉 Table 欄位中的全部資料。

• Sample Data

ID          data
----------- --------------------
1           Jacob
2           Sebastian

• Expected Result

id          data
----------- --------------------
2           naitsabeS
1           bocaJ

• 規則
1. 不能利用使用者自訂函數，且只能用單一個 T-SQL語法
2. 請確實利用用提供的 Smaple Data 來作答，方便檢查結果是否正確
3. 沒有 SQL Server 的版本限制