[SQL] 執行 SQL Profile 權限

執行 SQL Profile 要是 sysadmin 或有 ALTER TRACE 權限，一般使用者通常不會是 sysadmin，因此要避免一般使用者利用 SQL Profile 連進 SQL Server 來追蹤資料，只要讓使用者不具備 ALTER TRACE 權限即可。

• 利用 T-SQL 語法

-- 授予
GRANT ALTER TRACE TO UserName;

-- 拒絕 
DENY ALTER TRACE TO UserName; 

• SSMS => 安全性 => 登入 => 使用者 => 右鍵屬性 => 安全性實體 => 改變追蹤 => 勾選拒絕 checkbox。

• 利用 SQL Profile 連進不是 sysadmin 或沒有 ALTER TRACE 權限的 SQL Server 會收到此錯誤訊息。

參考資料
• 論壇問題討論
• 執行 SQL Server Profiler 所需的權限

[Challenge] 從字串中產生關係樹狀結構

Beyond Relational TSQL Challenge 60

An ETL application receives product and category information in a text file. The category hierarchy is specified as a string. Your job is to read the hierarchy information and generate a relational table (result set) representing the hierarchy of categories.

• Sample Data

Categories
---------------------------
food/biscuits/britania
food/milk
food/baby/icecream
food/baby/milk powder
detergents/liquids/handwash

• Expected Results

id  name        parent
--- ----------- -------
1   detergents  null
2   food        null
3   liquids     1
4   baby        2
5   biscuits    2
6   milk        2
7   handwash    3
8   icecream    4
9   milk powder 4
10  britania    5

• Rules
1. Pay attention to the order of the output table. The "ID" values of the categories are generated in a special way. The values are ordered by "parent" and then by "name".
2. There is no limit in the number of levels in the hierarchy.
3. Each hierarchy node will have at most one parent.
4. Each category ends in a terminal node of a hierachy and begins with the main parent node of the hierarchy.
5. There will be no duplicate entries.

[SQL] 交易備份與交易紀錄檔案大小

常聽見論壇上詢問為何進行交易備份後，交易記錄檔案大小並沒有因此縮小的疑問。

交易備份（Backup Log）是針對交易記錄檔案（ldf 檔案）內的［交易記錄］進行備份動作，要對交易記錄檔案（ldf 檔案）大小進行縮減，請進行壓縮（Shrink）。

體重、腰圍和褲子大小的關係來說明

• 褲子 = 交易紀錄檔案大小（ldf 檔案大小）
• 體重、腰圍 = 交易紀錄檔案內的交易紀錄（LOG）
• 運動 = 進行交易備份（BACKUP LOG）
• 修改褲子 = 進行壓縮（Shrink）

一位體重過重、腰圍過寬（ldf 檔案內充滿 LOG）的人運動瘦身後（進行 BACKUP LOG），體重會下降、腰圍會減少（ldf 內的 LOG 會被備份出來），但是褲子並不會縮小（ldf 檔案大小並不會改變），必須拿去給裁縫師修改褲子（進行 Shrink），褲子大小才會合身。

• 延伸閱讀
• [SQL] 標準報表 - 磁碟使用量
• 參考資料
• SQL Server LDF 容量大爆炸 (SQL Server ldf truncate & shrink)

[Challenge] 組織圖

Beyond Relational TSQL Challenge 8

This challenge is more about processing HIERARCHIES. SQL Server 2008 introduced a new data type HIERARCHYID which is pretty good for processing hierarchies. However, the problem explained in this challenge should be fixed without using the HIERARCHYID data type.

The problem is all about identifying all the employees directly or indirectly reporting to a given manager. Here is the organization chart of my fictitious company.

Jacob
    Jess
    Steve
        John
    Bob
        Michael
        Paul
Rui
    Smith
        Lana
        Johnson
    Bobbey
        Mic
        Stev
Jacobson
    Steffi
        Paulson
    Bracha
        Jessica

he challenge is to write a query that can take a Manager Name as a parameter and list all the employees reporting to that manager, directly or indirectly. If the parameter contains “Smith” the query should return:

Smith
    Lana
    Johnson

If the parameter passed is ‘Jacob’, the query should return:

Jacob
    Jess
    Steve
        John
    Bob
        Michael
        Paul

Your query should ideally look like the following:

DECLARE @manager VARCHAR(20)
SELECT @manager = 'Smith'
-- Your query here:

This query is pretty easy to write using a recursive CTE. To make the query complex, so that it will meet the complexity level expected for the ‘TSQL Challenges’ series, I am adding the following restriction.

“The query should be written using a recursive CTE, but the filter for "@manager” should not be applied inside the CTE”

• 規則：
1. The query should run on SQL Server 2005 and 2008
2. Write a single query that returns the results. No temp tables, functions etc should be used
3. Use 4 spaces for indentation while generating the output hierarchy
4. Siblings within a parent node may be sorted either by Name or by ID (I have sorted them by ID in the sample given above)

[SQL] 使用變數進行備份

以往把 BACKUP T-SQL 寫的彈性一些，都是利用組字串來進行，閱讀 MSDN BACKUP 文件時，發現 BACKUP T-SQL 的 DatabaseName 和 Device 參數可以直接用變數帶入。

{ database_name| @database_name_var }

這是要備份交易記錄、部分資料庫或完整資料庫的來源資料庫。 如果這個名稱是以變數 (@database_name_var) 的形式提供，則除了 ntext 或 text 資料類型之外，您可以將這個名稱指定為字串常數 (@database_name_var=database name)，或指定為字元字串資料類型的變數。

TO backup_device [ ,...n]

指出隨附的備份裝置集合是未鏡像的媒體集，或是鏡像媒體集內的第一個鏡像 (如果宣告了一個或多個 MIRROR TO 子句)。

• backup_device：指定備份作業要使用的邏輯或實體備份裝置。
• { logical_device_name | @logical_device_name_var }
這是用來備份資料庫之備份裝置的邏輯名稱。邏輯名稱必須遵照識別碼的規則。如果備份裝置名稱是以變數 (@logical_device_name_var) 的方式來提供，則除了 ntext 或 text 資料類型之外，您可以將這個名稱指定為字串常數 (@logical_device_name_var = 邏輯備份裝置名稱)，或指定為字元字串資料類型的變數。
• { DISK | TAPE } = { 'physical_device_name' | @physical_device_name_var }
  指定磁碟檔案或磁帶裝置。在 BACKUP 陳述式內指定磁碟裝置之前，該裝置不需要存在。 如果實體裝置存在，且 BACKUP 陳述式並未指定 INIT 選項，就會將備份附加至裝置中。
  
• n：這是一個預留位置，表示可以在逗號分隔清單中指定最多達 64 個備份裝置。

• 利用 AdventureWorks 來 Demo

DECLARE @DBName AS nvarchar(100)
DECLARE @bakPath AS nvarchar(4000)
SET @DBName  = N'AdventureWorks'
SET @bakPath = N'C:\BackupVariable' + @DBName + '.bak'

BACKUP DATABASE @DBName TO DISK = @bakPath

[Challenge] 字串中計算 HTML Tag 發生次數

This challenge involves counting the number of occurrences of HTML tags in a given list of strings. You can assume that there will be only valid HTML tags in the input strings. The output should display tags in ascending order.

• Sample Data

ID  HtmlText
-- ------------------------------------------------------------------------
1    ＜html＞＜body＞＜font＞This is challenge #18＜/Font＞＜font＞＜/Font＞＜/Body＞＜/Html＞

• Expected Results

ID TagNamesOccurance 
-- --------------------------------------------------------------
1  Body(Found: 1 time), Font(Found: 2 times), Html(Found: 1 time)

• Rules
1. ID 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 ID.

[SQL] T-SQL 語法邏輯順序

T-SQL 執行時有其邏輯順序，根據不同邏輯順序會產生不一樣的結果，官方文章上分散在兩篇文章內，分別為

• SELECT - Logical processing order of the SELECT statement
• FROM clause plus JOIN, APPLY, PIVOT - on-search_condition

 該篇會把內容整合記錄

Sample Data

每個學生擁有的書籍來當成範例說明

DECLARE @STD TABLE (STDNO char(3),STDName nchar(10))
INSERT INTO @STD VALUES('001',N'張三')
INSERT INTO @STD VALUES('002',N'李四')
INSERT INTO @STD VALUES('003',N'王五')

DECLARE @Books TABLE (ID int,STDNO char(3) , Kind char(10) , BookName char(30))
INSERT INTO @Books VALUES(1,'001','SQL','SQL Server T-SQL')
INSERT INTO @Books VALUES(2,'001','SQL','SQL Server Turning')
INSERT INTO @Books VALUES(3,'001','SQL','SQL Server Statistics')
INSERT INTO @Books VALUES(4,'003','Win','Windows Server')
INSERT INTO @Books VALUES(5,'003','SQL','SQL Server T-SQL')
INSERT INTO @Books VALUES(6,'003','Win','HyperV')

Logical processing order of the SELECT statement

官方文件內容說明

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. The actual physical execution of the statement is determined by the query processor and the order might vary from this list.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE 或 WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

文件上的備註說明

The preceding sequence is usually true. However, there are uncommon cases where the sequence might differ.

For example, suppose you have a clustered index on a view, and the view excludes some table rows, and the view's SELECT column list uses a CONVERT that changes a data type from varchar to integer. In this situation, the CONVERT can execute before the WHERE clause executes. Uncommon indeed. Often there is a way to modify your view to avoid the different sequence, if it matters in your case.

以欄位別名為例，利用兩個簡單範例來說明上述邏輯順序

-- 簡易範例 1
SELECT STDNO AS 學生編號 , STDName AS 學生姓名
FROM @STD
ORDER BY 學生編號 DESC -- 利用欄位別名來排序

ORDER BY 的順序在 SELECT 之後，因此 ORDER BY 可以指定 SELECT 的欄位別名，當成排序的欄位選擇。

-- 簡易範例 2
SELECT STDNO AS 學生編號 , STDName AS 學生姓名
FROM @STD
WHERE 學生編號 = '001' -- 會出現此錯誤訊息"無效的資料行名稱 '學生編號'"。

WHERE 的邏輯順序，因為在 SELECT 之前，所以 WHERE 條件內不可以指定欄位別名來當成條件

on-search_condition

官方文件說明

There can be predicates that involve only one of the joined tables in the ON clause. Such predicates also can be in the WHERE clause in the query. Although the placement of such predicates doesn't make a difference for INNER joins, they might cause a different result when OUTER joins are involved. This is because the predicates in the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join.

計算每個學生擁有關於 SQL 的書籍有多少本，沒有的學生必須秀 0 來表示，藉此來說明 LEFT JOIN 篩選條件位置（ON 或 WHERE）的差異

-- WHERE 內條件判斷
SELECT 
  A.STDNO AS 學生編號 , 
  COUNT(B.BookName) AS 書籍數量
FROM @STD AS A 
  LEFT JOIN @Books AS B ON A.STDNO = B.STDNO
WHERE B.Kind = 'SQL'
GROUP BY A.STDNO

-- ON 內條件判斷
SELECT 
  A.STDNO AS 學生編號 , 
  COUNT(B.BookName) AS 書籍數量
FROM @STD AS A 
  LEFT JOIN @Books AS B ON A.STDNO = B.STDNO AND B.Kind = 'SQL' 
GROUP BY A.STDNO

在 WHERE 內判斷，是在 LEFT JOIN 後產生結果後（下圖），再對其結果（下圖）進行條件判斷

ON 內判斷是先針對 @Book 內進行條件判斷，再把判斷後結果（下圖）跟 @STD 進行 LEFT JOIN 結合

LEFT JOIN 或 RIGHT JOIN 才會有此邏輯順序問題，JOIN 的話不論是 WHERE 或 ON 中判斷，其結果都是一樣的。

• 參考資料
• SELECT、FROM
• 注意Left Join時過濾條件擺放的位置，不同的位置可是會有不同的結果歐
  
• SQL queries don't start with SELECT
• [SQL]SELECT 優先順序與變數指定的異常案例分享

[Challenge] 判斷有效的 GUID 並計算總和

Beyond Relational TSQL Beginners Challenge 13

The challenge involves validating the GUID values and perform horizontal and vertical count of the characters "1, 2, 3, 4, 5, A, B, C, D, E".

The definition of valid GUID is "The GUID value should contain all the characters 1, 2, 3, 4, 5, A, B, C, D, E".

e.g. In "7FC61235-F252-4E32-BD31-99FB1FC8A574" all the characters given in character set are present while in "7FX61235-X252-4Y32-BZ31-99FB1FC8Z574" some of them are missing even though they both are GUIDs. So the program should accept "7FC61235-F252-4E32-BD31-99FB1FC8A574" as a valid GUID.

A typical GUID looks like 704A9279-B245-4D4E-9616-7ACB6E9DF7A0.

• Sample Data

Guid
------------------------------------
7FC61235-F252-4E32-BD31-99FB1FC8A574
D031BC00-ADD3-4C40-BE24-A613586FD42C
81B10B95-5A22-4D3A-949A-FF268103A554
ABCDE12345E

• Expected Results

ValidGuid                            1 2 3 4 5 A B C D E Sum
------------------------------------ - - - - - - - - - - -----
7FC61235-F252-4E32-BD31-99FB1FC8A574 3 4 3 2 3 1 2 2 1 1  22
D031BC00-ADD3-4C40-BE24-A613586FD42C 2 2 3 4 1 2 2 3 4 1  24
Vertical Sum                         5 6 6 6 4 3 4 5 5 2  46

• Rules
1. Output should be sorted in Ascending Order of ValidGuid.
2. Consider the character count for only the characters 1,2,3,4,5,A,B,C,D,E.
3. The output should be formatted as shown in 'Expected Result'.Column name should be exactly the same and the result must be sorted in Ascending order of ValidGuid.