[SQL] DAC 應用

練習 LOGON Trigger 時搞烏龍，害自己沒有辦法登錄 SQL Server Instance，最後是利用 DAC 連進去刪除 LOGON Trigger 才搞定，這篇紀錄處理過程。

• 建立一個 LOGON Trigger 讓自己進不去 SQL Server Instance

CREATE TRIGGER Connection_Limit_Trigger
ON ALL SERVER
FOR LOGON
AS
    ROLLBACK -- 不管三七二十一，就是不准進來

• 故意連進去看看

• 使用 sqlcmd 來進行 DAC 連線，並刪除 LOGON Trigger

• 再次利用 SSMS 連進 SQL Server Instance，從 2 可以看見連進 WIN7-JengTing\SQL2012 內， 從 3 可以發現觸發程序內已經沒有 Connection_Limit_Trigger LOGON Trigger 了。

延伸閱讀
• [SQL] Logon Triggers
• [SQL] Dedicated Administrator Connection（DAC）

參考資料
• SQL Server: Disable Logon Trigger Using DAC to Resolve Login Problem
• SQL SERVER – sqlcmd – Using a Dedicated Administrator Connection to Kill Currently Running Query
• SQL Server: Disable Logon Trigger Using DAC to Resolve Login Problem

[SQL] Dedicated Administrator Connection（DAC）

SQL Server 正常（標準）連線失效時，可以利用 DAC 進行連線來執行診斷查詢和排解疑難問題。

各版本的 DAC 預設是開啟，但 Express 為節省資源，除非追蹤旗標 7806 啟動，否則無法使用 DAC。

• Express 未開啟追蹤旗標 7806 時，SSMS 要使用 DAC 連線時的錯誤訊息。

• SQL Server Configuration Manager => SQL Server 服務 => SQL Server instance 右鍵內容 => 啟動參數 Tag => 加入啟動參數 '-T 7806'。

• 設定追蹤旗標後必須重新啟動，才會產生作用。

[SQL] Logon Trigger

Logon Trigger 是透過 LOGON 事件觸發，可以追蹤登入活動或限制登入 SQL Server。

Logon Trigger 語法基本上跟 DDL Trigger 是一樣的，但 Logon Trigger 只有一個觸發事件，就是 LOGON 事件，同樣地擷取 Logon Trigger 的相關資訊，也必須使用 EVENTDATA()，但其 XML 內容有些許差異而已。

EVENTDATA() 函數

• ClientHost：來源用戶端之主機名稱。如果用戶端和伺服器名稱相同，這個值會是'<local_machine>'。 否則會是用戶端的 IP 位址。

限制登入 SQL Server
• 禁止 Client 端利用 SSMS 連入 SQL Server。
• 禁止特定帳戶（ServerName\LogonTest）、特定 IP （192.168.0.20）或特定時間連入（2000 之後） SQL Server。

CREATE TRIGGER Connection_Limit_Trigger
ON ALL SERVER 
FOR LOGON
AS
    BEGIN
      -- 禁止 SSMS 連入
      IF APP_NAME() LIKE '%Microsoft SQL Server Management Studio%'
          ROLLBACK
        
      -- 禁止特定帳號或特定 IP 連入
      DECLARE @data xml
      DECLARE @ClientHost varchar(50)
      DECLARE @LoginName varchar(50)
      DECLARE @PostTime datetime

      SET @data = EVENTDATA()
      SET @ClientHost = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
      SET @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
      SET @PostTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')

      -- 特定 IP
      IF @ClientHost = '192.168.0.20'
          ROLLBACK

      -- 特定帳號
      IF @LoginName = 'ServerName\LogonTest'
          ROLLBACK

      -- 特定時間
      IF DATEPART(hh,@PostTime) >= 20
          ROLLBACK

    END

• 2013 DBA 天團試題

延伸閱讀
• [SQL] DDLTrigger

MS 參考資料
• 登入觸發程序 => 內有範例：禁止單一帳戶建立三個以上的 session
• DDL Triggers and LOGON Triggers
• APP_NAME()

參考資料
• 停用(disable) IntelliSense；以使用「登入觸發程序(Logon Trigger)」，或是設定 SSMS 管理工具為例
• Logon trigger in SQL Server
• SQL SERVER – Interesting Observation of Logon Trigger On All Servers
• SQL SERVER – Interesting Observation of Logon Trigger On All Servers – Solution
• SQL SERVER – Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.
• Super SQL Server 討論

[SQL] DDL Trigger

DDL Trigger 可以用來追蹤與監控資料庫結構的變化，EX：紀錄誰何時對 Table schema 進行變化、新增登入者等，DDL 事件會觸發 DDL Trigger，DDL 事件則是指 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 或 UPDATE STATISTICS 關鍵字開頭的 T-SQL 語法。

語法

CREATE TRIGGER trigger_name     -- DDL Trigger 名稱 
ON  { ALL SERVER | DATABASE }   -- 指定伺服器或資料庫層級
    [ WITH 
          ENCRYPTION ,          -- 是否加密
          EXECUTE AS User       -- 使用某 User 的權限來執行 DDL Trigger
    ]
FOR { event_type | event_group }-- 指定觸發的 DDL 事件或 DDL 群組事件
AS
    BEGIN

        -- 執行的 T-SQL 語法

    END

EVENTDATA()函數

DDL Trigger 執行時，使用 EVENTDATA() 擷取 DDL 相關資訊，回傳值為 XML，可以利用 XQuery 來解析其內容，內容可以儲存在 Table 中供後續分析追蹤或利用 DataBase Mail 發信通知相關人員。

• LoginName：登入 SQL Server 來執行 DDL 語法的帳號
• ObjectName：對哪個帳號進行加入、修改或刪除動，以上述圖型為例，是要新增 DDLTest 帳號

[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 的版本限制

[SQL] 利用 T-SQL 語法更改欄位資料型態

同事利用 ALTER TABLE 語法更改欄位資料型態時，發現欄位本身有預設值，導致更改資料型態時產生 Error，SQL Server 提供的錯誤訊息明確，也很輕鬆的解決這個問題。

USE Tempdb
GO

IF OBJECT_ID('Demo') IS NOT NULL
  DROP TABLE Demo

CREATE TABLE Demo
  (
    ID INT IDENTITY(1,1) ,
    Col1 char(10) NOT NULL DEFAULT('') -- 預設為空值
  )

ALTER TABLE Demo ALTER COLUMN Col1 INT -- 更改資料型態為 INT

• 錯誤訊息

訊息 5074，層級 16，狀態 1，行 11

物件 'DF__Demo__Col1__35BCFEOA' 與資料行 'Col1' 相依。

查詢些資料來了解 ALTER TABLE 更改欄位資料型態時，要注意的事項

1. 欄位資料型態是 ROWGUIDCOL（GUID），只能進行刪除欄位，不允許更改欄位資料型態。
2. 計算欄位（computed column）或是複寫欄位（replicated column）無法變更資料型態。
3. 定義為 Primary Key 或 Foreign Key 的欄位，無法更改資料型態。
4. 更改欄位不可以參考計算爛位（computed column）。
5. 任何資料型態無法變更為 timestamp。
6. 欄位有設定 UNIQUE 或 CHECK 條件約束，只允許增加資料型態長度。
7. 欄位有設定 DEFAULT 值，只允許增加或減少資料型態長度、是否允許 NULL 值或改變有效位數（precision）或小數位值（scale）。
8. 舊資料型態必須允許隱含轉換（implicit conversion）至新資料型態。
9. 如果資料型態轉換過程中發生 overflow，轉換動作會被終止。
10. 轉換成新資料型態後，ANSI_PADDING 的設定一定是 ON。

下述兩點是實務上測試後有發現的資料，附上原文說明和自己的說明

1. The modified column can't be a text, image, ntext, or rowversion（timestamp）column.   ［text,image,ntext 或 rowversion（timestamp）無法變更資料型態］，可以針對 text、ntext 直接轉為 varchar(MAX)
2. If the modified column participates in an index, the only type changes that are allowed are increasing the length of a variable-length type (for example, varchar(10) to varchar(20), changing nullability of the column, or both) ［欄位是索引欄位，只允許增加資料型態長度或是否允許 NULL 的改變。］，測試 char、varchar 和 int 三種資料型態，只有 varchar 能直接增加長度，char 和 int 還是必須先卸除 Index，才能變化資料型態，另外 NULL 值只能從 NOT NULL 變成 NULL，無法從 NULL 改成 NOT NULL。

延伸閱讀
• [SQL] 利用 T-SQL 語法更改欄位名稱

MSDN 參考資料
• rowversion
• 有效位數、小數位數和長度
• column_definition
• SET ANSI_PADDING

參考資料
• Alter Table Drop Column
• [SQL SERVER][Performance] text 資料類型轉換 varchar(max)的影響
• SQL SERVER – How to alter an existing computed column in a table
• 技術論壇討論：一個 ALTER 語法更改多個欄位資料型態

[Win] IE 增強式安全性設定

使用 Windows Server 2008 練習時，用到 IE 就會出現安全性的詢問，直接把它關掉，減少麻煩，實務上當然是不建議這麼作 ~~

• 開啟 IE 預設的增強式安全性設定說明

• 輸入任何網站會出現的詢問視窗

• 在伺服器管理員的右下角有個 "設定 IE ESC"

• 把它關閉就行

• 關閉後 IE 上的提示，進入任何網站時就不會再詢問囉

[SQL] SQL Server Agent Mail

設定 SQL Server Agent Mail 可以讓 SQL Server Agent 擁有發送 mail 的功能。

SQL Server Agent Mail，預設為關閉狀態，以下說明如何設定 SQL Server Agent Mail。

• SQL Server Agent => 屬性。

• 警示系統 => 郵件工作階段 => 啟用郵件設定擋，選擇郵件系統和郵件設定擋。
• 郵件系統：
1. Database Mail：SQL Server Agent 會使用 Database Mail 傳送電子郵件。
2. SQL Mail：SQL Server Agent 會使用「擴充 MAPI」介面傳送電子郵件。
未來的 SQL 版本會移除 SQL Mail，請盡量不要使用它。
• 郵件設定檔：指的是 Database Mail Profile。

• SQL Server Agent 會快取指定設定檔的設定檔資訊，當設定檔變更時，SQL Server Agent 不會立即使用新的資訊，必須重新啟動 SQL Server Agent 服務，才能使變更生效。

重新啟動 SQL Server Agent 後，即完成設定。

延伸閱讀
• [Winodws] SMTP 設定
• [SQL] Database Mail 設定

MSDN 參考資料
• SQL Server Agent Mail

[Challenge] 階乘

Beyond Relational TSQL Beginners Challenge 3

This challenge though does not have any resemblance with the real time problem directly, but it measures about logical thinking. The problem is all about finding the factorial of numbers. Though it is known to most of us what a factorial is, but to recall the concept here is an example:

Factorial of 3 is 1*2*3 = 6 i.e. the factorial of a non-negative integer n, denoted by n!, is the product of all positive integers less than or equal to n.

• Sample Data

Nums
-----------
0 
1 
3 
5 
10

• Expected Results

Nums        Factorial
----------- -----------
0                    1
1                    1
3                    6
5                  120
10             3628800

• Rules
1. Nums should be sorted in Ascending Order.
2. The program should run in SQL SERVER 2005 and above.
3. The output should be in the same way as it has been shown. Column names should be exactly the same and the result must be sorted in Ascending order of Nums.
4. The program has to be done by a single query and should begin either with a SELECT or WITH statement with no variables, temporary table, table variables permitted.
5. You cannot use RBAR, cursors, loops etc. in your program.

[SQL] 字串中特定字串出現次數

直接利用例子來說明如何計算字串中特定字串出現次數。

SELECT 
  T.String ,
  (LEN(T.String) - LEN(REPLACE(T.String,'A',''))) / LEN('A') AS 'A 出現次數',
  (LEN(T.String) - LEN(REPLACE(T.String,'23',''))) / LEN('23')  AS '23 出現次數' ,
  (LEN(T.String) - LEN(REPLACE(T.String,'DEF',''))) / LEN('DEF')  AS 'DEF 出現次數'
FROM
  (
    SELECT '123456123456' AS String
    UNION ALL
    SELECT 'ABCDEF'
    UNION ALL
    SELECT '123ABC321'
    UNION ALL
    SELECT 'DEF456ABC'
  ) AS T

• 邏輯說明
  利用 REPLACE() 函數來取代特定字串，再利用原字串和特定字串取代後長度相減，除以特定字串長度來計算出現次數，下圖示把文字說明改為數學式來表達。

• 結果

參考資料
• 論壇問題出處
• MSDN REPLACE()、LEN()

[SQL] 複合索引 - 第一欄位

複合索引是利用多個欄位來組成索引。

利用 AdventureWorks2012 內的 Person 資料表的非叢集式索引（NonClustered Index，簡稱索引） IX_Person_LastName_FirstName_MiddleName 來說明欄位順序的影響。

IX_Person_LastName_FirstName_MiddleName 索引是由 LastName、FirstName 和 MiddleName 三個欄位依序組成

從 Person 資料表中選定一位人名針對三個欄位組合來搜尋資料並觀察 Query Optimizer（簡稱 QO） 選擇執行計畫時使用索引的狀態

1. 針對 LastName、FirstName 和 MiddleName 搜尋
   

   SELECT LastName , FirstName , MiddleName  
   FROM  [Person].[Person] 
   WHERE LastName = 'Ting' AND FirstName = 'Hung-Fu' AND MiddleName = 'T'
   

   
2. 針對 LastName 和 MiddleName 搜尋
   

   SELECT LastName , FirstName , MiddleName  
   FROM  [Person].[Person] 
   WHERE LastName = 'Ting' AND MiddleName = 'T'
   

   
3. 針對 FirstName 和 MiddleName 搜尋
   

   SELECT LastName , FirstName , MiddleName  
   FROM  [Person].[Person] 
   WHERE FirstName = 'Hung-Fu' AND MiddleName = 'T'
   

   

從上面三個例子可以看出搜尋條件中有包含 LastName 欄位的話，QO 會利用索引搜尋（Index Seek）來搜尋資料，沒有的話則是利用索引掃描（Index Scan）。

複合索引（IX_Person_LastName_FirstName_MiddleName）雖由多個欄位組成，但其資料在索引分頁（ Index Page）上，只有第一個欄位（LastName）資料是經過排序。下圖是擷取 Person 資料表內部分資料，來模擬資料在索引分頁（Index Page）上的理論資料排序情況。

而從統計資訊來觀察，會發現複合索引統計資訊內只會保存第一個欄位（LastName ）的資料長條圖（Histogram）

複合索引的第一個欄位選擇非常重要，因為它是唯一一個欄位資料有經過排序，且能發揮複合索引效能的關鍵。

延伸閱讀
• [SQL] 複合索引效能

參考資料
• 論壇問題出處
• 建立複合索引的順序
• [SQL SERVER]複合索引第一欄位重要性
  
• 論壇討論

[Win] 開機自動連線

家人說可以開機後自動連上網路嗎？還要按一下連線才可以使用網路很麻煩（OS：有這麼誇張嗎？）

這篇說明在 Windows 7 上設定，讓電腦一開機就自動連線。

• Windows 7 控制台 => 網路和網際網路 => 網路和共用中心 => 變更介面卡設定內，可以看見網路連線（我的設定是 So-Net）

• 在 So-Net 上右鍵內容 => 選項 => 取消［撥號選項］內的［提示名稱、密碼、憑證等］。

• 取消［撥號選項］內的［提示名稱、密碼、憑證等］可以避免詢問視窗出現（下圖）。

[SQL] Database Mail 設定

完成 [Winodws] SMTP 設定 後，本篇說明如何設定 SQL Server Database Mail。

• SSMS => 管理 => Database Mail => 滑鼠右鍵 => 設定 Database Mail。

• Database Mail 組態精靈頁面，使用此精靈必須是系統管理員（sysadmin）。

• 建立一個新的電子郵件設定檔（以下簡稱 Profile）

• 設定 Profile 時，假如還未啟用 SQL Database Mail 預存程序的話，會出現此畫面（啟動一），按是的話就會啟動，後面提供另外兩種事先啟動方式，事先啟動的話，則不會出現此畫面。

• 在 Surace Area Configuration 內啟用（啟動二）

• 利用 T-SQL 啟動 （啟動三）

sp_configure 'show advanced options',1
reconfigure
GO
sp_configure 'Database Mail XPs' -- 檢視起否啟動

sp_configure 'Database Mail XPs',1 -- 1 為啟動、2 為停止
reconfigure

[Challenge] 計算字元在字串中出現次數

Beyond Relational TSQL Beginners Challenge 14

This challenge is all about counting the number of occurrence of characters in a string using a SET based query. The task is to list all the characters and count of that character within the given string.

• 資料來源

Data
----
12
xx

• 產生結果

Data Chars NumberOfOccurance
---- ----- -------------------
12  1    1 appears (1) times
12  2    2 appears (1) times
xx  x    x appears (2) times

• 規則
1. Results must be sorted in ascending order of Data and then by Character.
2. The output should be in the same way as it has been shown.

[SQL] 次序函數應用-重覆資料不顯示

此論壇問題是要重覆資料不顯示，一看見問題是直覺這不是應該在報表中設定，就可以達到的效果。

DECLARE @Temp TABLE (STDID char(4),StdName char(10),Course char(20))
INSERT INTO @Temp VALUES('0001','張三','ASP.NET')
INSERT INTO @Temp VALUES('0001','張三','HTML5')
INSERT INTO @Temp VALUES('0001','張三','CSS')
INSERT INTO @Temp VALUES('0002','李四','C#')
INSERT INTO @Temp VALUES('0003','王五','T-SQL')
INSERT INTO @Temp VALUES('0003','王五','SSRS')
INSERT INTO @Temp VALUES('0003','王五','SSIS')
INSERT INTO @Temp VALUES('0003','王五','Perfomance Tuning')

SELECT 
  IIF(ROWNO = 1 , STDID , NULL) AS STDID ,
  IIF(ROWNO = 1 , STDName , NULL) AS STDName ,
  Course
FROM
  (
    SELECT * ,
      ROW_NUMBER() OVER (PARTITION BY STDID ORDER BY STDID) AS ROWNO
    FROM @Temp
  ) AS T

• 邏輯說明

利用 ROW_NUMBER() 根據 STDID 去跑出排序，在 SELECT 中根據排序利用 CASE WHEN 或是 IIF 來判斷是不是第一筆資料，非第一筆資料則用 NULL 來表示。

延伸閱讀
• [SQL] 次序函數應用-資料分組
• [SQL] 次序函數應用-判斷是否為連續日期

參考資料
• 論壇問題出處
• 論壇討論

[Win] SMTP 設定

學習 SQL Server Database Mail 時，必須先設定 SMTP 才能使用 Database Mail 來發信，紀錄一下在 Windows Server 2008 上設定 SMTP 的過程。

• 開始 => 所有程式 => 系統管理工具 => 伺服器管理員 => 功能 => 新增功能

• 勾選 SMTP 伺服器，系統會同時詢問並自動勾選 SMTP 伺服器工具 和 IIS 6.0（未安裝的話）

• 安裝進行中

• 安裝完成

[SQL] JOIN ON 條件中使用 CASE 來判斷

此論壇問題是要利用 @TempA 的 A2 欄位 JOIN @TempB 的 B1 欄位，假如 A2 欄位資料為 AA+ JOIN 不到資料，就必須改為 AA- 去JOIN，直到找不到資料。

DECLARE @TempA TABLE(A1 char(4),A2 char(3))
DECLARE @TempB TABLE(B1 char(3),B2 char(10))
INSERT INTO @TempA VALUES('0001','AA+')
INSERT INTO @TempA VALUES('0002','BB+')
INSERT INTO @TempB VALUES('AA-','值1')
INSERT INTO @TempB VALUES('BB+','值2')
INSERT INTO @TempB VALUES('BB-','值3')

-- 會產生無法解釋的非預期結果
SELECT A.A1,A.A2,B.B1,B.B2 
FROM @TempA AS A
  LEFT JOIN @TempB AS B ON
    (
      CASE
        WHEN (A.A2 = B.B1) THEN A.A2 
        ELSE SUBSTRING(A.A2,1,2) + '-' END
    ) = B.B1

-- 建議用法
SELECT 
  A.A1 , 
  A.A2 , 
  ISNULL(B1.B1,B2.B1) AS B1, 
  ISNULL(B1.B2,B2.B2) AS B2
FROM @TempA AS A 
  LEFT JOIN @TempB AS B1 ON A.A2 = B1.B1
  LEFT JOIN @TempB AS B2 ON SUBSTRING(A.A2,1,2) + '-' = B2.B1

在 JOIN ON 使用 CASE WHEN 來判斷連結條件或是多個條件比較，會產生無法解釋的非預期結果，要盡量避免此用法；在此利用多個 LEFT JOIN 搭配 ISNULL() OR COALESCE() 來達到目的，確保結果產生。

MSDN ON 說明

ON 為聯結所根據的條件，指定聯結所根據的條件。條件可以指定任何述詞 (雖然通常都是使用資料行和比較運算子)

當條件指定資料行時，這些資料行不必有相同的名稱或相同的資料類型；不過，如果資料類型不同，它們必須相容的類型或 SQL Server 可以利用隱含方式轉換的類型。 如果資料類型無法利用隱含方式轉換，條件必須藉由 CONVERT 函數，利用明確方式轉換資料類型。

ON 子句中可以有僅涉及其中一個聯結資料表的述詞。 這類述詞也可以在查詢的 WHERE 子句中。 雖然這類述詞的放置不會影響 INNER 聯結，不過，如果涉及 OUTER 聯結，就可能會造成不同的結果。 這是因為 ON 子句中的述詞會套用至聯結之前的資料表，但在語意上，WHERE 子句則套用至聯結的結果。

參考資料
• 論壇問題出處
• Conditional Joins in SQL Server
• MSDN FROM

[Challenge] 電影時刻表

Beyond Relationa TSQL Beginners Challenge 8

這個問題有點奇怪，給了兩個 Table Data，但結果好像只要一個 Table Data 就可以產生。

• 資料來源

Table: MovieReleaseDates

Movie1     Movie2  Movie3  Movie4  Movie5
----------  ---------- ---------- ---------- ----------
2010-01-20  2010-02-15  2010-02-02  2010-03-31  2010-04-16 
2010-01-22  2010-02-16  2010-02-04  2010-04-05  2010-04-18 

Table: MovieReleasedIn

City   MovieNames ReleaseDate
--------- ---------- -----------
Bangalore Movie1  2010-01-22 
Bangalore Movie2  2010-02-15 
Bangalore Movie3  2010-02-04 
Bangalore Movie4  2010-04-05 
Bangalore Movie5  2010-04-16     

• 產生結果

City   Sun Mon     Tue Wed Thu    Fri     Sat  
--------- --- ------------ --- --- ------------- ------------- ---  
Bangalore NA  NA     NA  NA  NA    Movie1(01/22) NA 
Bangalore NA  Movie2(02/15)NA  NA  NA    NA         NA 
Bangalore NA  NA     NA  NA  Movie3(02/04) NA         NA 
Bangalore NA  Movie4(04/05)NA  NA  NA    NA         NA 
Bangalore NA  NA     NA  NA  NA    Movie5(04/16) NA  

• 個人解法

DECLARE @tblMovieReleasedIn TABLE
(
  City VARCHAR(20), 
  MovieNames VARCHAR(20),
  ReleaseDate Datetime
)
INSERT INTO @tblMovieReleasedIn
SELECT 'Bangalore','Movie1','01/22/2010' UNION ALL
SELECT 'Bangalore','Movie2','02/15/2010' UNION ALL
SELECT 'Bangalore','Movie3','02/04/2010' UNION ALL
SELECT 'Bangalore','Movie4','04/05/2010' UNION ALL
SELECT 'Bangalore','Movie5','04/16/2010' 

SELECT 
  City , 
  ISNULL([1],'NA') AS Sun , 
  ISNULL([2],'NA') AS Mon , 
  ISNULL([3],'NA') AS Tue , 
  ISNULL([4],'NA') AS Wed , 
  ISNULL([5],'NA') AS Thu , 
  ISNULL([6],'NA') AS Fri , 
  ISNULL([7],'NA') AS Sat
FROM
  (
    SELECT 
      City , 
      MovieNames , 
      MovieNames + '(' + LEFT(CONVERT(char(10),ReleaseDate,101),5) + ')' AS Data , 
      DATEPArt(dw,ReleaseDate) AS dw 
    FROM  @tblMovieReleasedIn
  ) AS P
PIVOT
  (
    MAX(Data) FOR dw IN ([1],[2],[3],[4],[5],[6],[7])
  ) AS PV
ORDER BY City

參考資料
• TSQL Beginners Challenge 8 - Identify the release dates of movies on different cities
• Solutions - 兩個 Table Data 都有使用到

[SQL] 次序函數應用-資料分組

此論壇問題是要針對資料進行分組，分組模式有兩種

1. 從 1 數到 3，不斷循環。
2. 每 3 筆一個群組。

根據提問者提供資料，利用 ROW_NUMBER() 搭配除法（/）和模除（%）來進行處理

DECLARE @Temp TABLE ([Name] char(10))
INSERT INTO @Temp VALUES('A')
INSERT INTO @Temp VALUES('E')
INSERT INTO @Temp VALUES('C')
INSERT INTO @Temp VALUES('H')
INSERT INTO @Temp VALUES('G')
INSERT INTO @Temp VALUES('D')
INSERT INTO @Temp VALUES('B')
INSERT INTO @Temp VALUES('I')
INSERT INTO @Temp VALUES('J')
INSERT INTO @Temp VALUES('F')

SELECT 
  [Name] , 
  -- ROW_NUMBER() 產生的號碼排序
  ROW_NUMBER() OVER (ORDER BY [Name]) AS ROWNO ,
  -- 分組模式 1
  ((ROW_NUMBER() OVER (ORDER BY [Name]) - 1) % 3 + 1) AS Count3 ,
  -- 分組模式 2
  ((ROW_NUMBER() OVER (ORDER BY [Name]) - 1) / 3) + 1 AS Group3 
FROM @Temp

• 邏輯說明

ROW_NUMBER() 跑出號碼排序後，利用模除（%）取餘數，可以達到分組模式 1；利用除法（/）取商數可以達到分組模式 2，因為 ROW_NUMBER() 回傳值的資料型態為 bigint，整數除整數一定會得到整數，EX：10 / 3 會得到 3，不會是 3.333333，另外分組模式 1 和 2 中，-1 和 +1 的數學邏輯處理，是為了讓數字呈現從 1 開始顯示，閱讀上更直覺。

• 延伸閱讀
• [SQL] 數學運算-除法
• [SQL] 次序函數應用-重覆資料不顯示
• [SQL] 次序函數應用-判斷是否為連續日期
• 參考資料
• 論壇問題出處
• 論壇問題應用
• MSDN ROW_NUMBER()
• How to group rows in fixed group size with MSSQL