效能調校(5)-使用資料表分割

分割資料表（Partition Table）就如同資料表，只是在分割資料表中的資料會被分割成數個分割區（Partition）。 你可以將分割資料表建立在特定的檔案群組上，則所有分割區都會使用同一個檔案群組。 當然你也可以將分割資料表建立在特定的分割配置（Partition Scheme）上，則每個分割區都會使用不同的檔案群組，藉此提升存取效能。

只有 SQL Server Enterprise、Developer 和 Evaluation 版本上才可使用資料分割資料表和索引。

資料庫物件

SQL Server Data Tools

資料庫專案

Visual Studio 從 2005 版本開始提供資料庫專案範本， 這種專案完全使用TSQL的宣告方式來定義資料庫，可横跨資料庫開發的所以階段。 此外，資料庫專案也可以用來進行資料庫的版本管控，建立測試資料，發佈資料庫，方便整個資料庫的管理作業。

SQL Server Data Tool （SSDT）

到了 Visual Studio 2012 或者 SQL Server 2012 又推出了 SQL Server Data Tool （SSDT）。 它是一套整合在 Visual Studio 裡的資料庫管理工具，讓開發人員幾乎無需使用 SSMS 管理工具，而直接在 Visual Studio 內，就可以完成資料庫的設計、建置、資料庫專案(SSDT-DP)、T-SQL 重構等作業。 SSDT 不僅僅是資料庫管理工具，也是用來開發特殊專案類型工具，如： SSAS、SSRS 和 Integration Services Business Intelligence (BI) 方案 (之前稱為 Business Intelligence Development Studio)。

SSDT 已包含在 SQL Server 2012 或 Visual Studio 2012 的安裝檔案裡， 若使用 Visual Studio 2010 ，必須額外下載安裝程式。

效能調校(4)-善用覆蓋索引

通常大家都知道，在資料庫加入索引，可以增進查詢效率。可是若將查詢欄位加上了索引之後，為什麼使用到 RowNumber() 進行查詢時，效率還是很慢？ 在說明之前，可先參考這篇，瞭解索引的類型。

下面這二個例子，CreatedTime 和 RevisedTime 這二個欄位都有建立 NonClustered-Index，但分別以該欄位進行排序搜尋後的結果，二者效能上卻差了10倍以上。 問題就在於，這二個索引之中，CreatedTime 欄位沒有使用內含資料行。

效能調校-心得分享

有些程式員在撰寫前端的應用程式時，會透過各種 OOP 語言將存取資料庫的 SQL 陳述式串接起來，卻忽略了 SQL 語法的效能問題。版工曾聽過某半導體大廠的新進程式員，所兜出來的一段 PL/SQL 跑了好幾分鐘還跑不完；想當然爾，即使他前端的 AJAX 用得再漂亮，程式效能頂多也只是差強人意而已。以下是版工整理出的一些簡單心得，讓長年鑽究 ASP.NET / JSP / AJAX 等前端應用程式，卻無暇研究 SQL 語法的程式員，避免踩到一些 SQL 的效能地雷。

效能調校(2)-分析執行計畫

當一道TSQL，由用戶端送出，一直到伺服器端執行完畢，這中間可能包含了很多過程。 不過簡單來看大至包含以下三個步驟：

1. Parse：先檢查語法，再建立 processor tree（定義logical steps）。
2. Optimize：使用「Query Optimizer」取得資料的統計資訊（如多少筆資料，有多少唯一的資料，需要多少resources, CPU & I/O等等）。 「Query Optimizer」會依這些資訊建立很多的 plan，然後選擇最好的plan。
3. Execute：最後就是依「Query Optimizer」送過來的 plan 去執行。

而圖形化的執行計畫是 SQL Server 提供給開發人員或 DBA 用來分析查詢執行成本的工具，以做為 T-SQL 指令碼效能調校的參考。

效能調校-案例探討

建立索引(1)-叢集與非叢集索引

資料庫的索引，就像書本的索引資訊一樣，用來提升資料詢找的效率。 但是索引的建立，卻不是隨便的，也不是越多越好，正確的設定才可以真正提升 SQL Server 的執行效率。

效能調校(1)-善用各種索引

SQL Server 2008 supports two basic types of indexes: clustered and nonclustered. Both indexes are implemented as a balanced tree, where the leaf level is the bottom level of the structure. The difference between these index types is that the clustered index is the actual table; that is, the bottom level of a clustered index contains the actual rows, including all columns, of the table. A nonclustered index, on the other hand, contains only the columns included in the index's key, plus a pointer pointing to the actual data row. If a table does not have a clustered index defined on it, it is called a heap, or an unsorted table. You could also say that a table can have one of two forms: It is either a heap (unsorted) or a clustered index (sorted).

效能調校概說

登入者、使用者、結構描述

User Defined Functions

Functions are programmable objects that are used to perform calculations that can be returned to a calling application or integrated into a result set. Functions can access data and return results, but they cannot make any modifications.

You can create your own functions, referred to as user-defined functions, and store the functions in any database for which you have CREATE FUNCTION authority.

Views

檢視（VIEW） 同時也稱為虛擬資料表，它將一連串複雜的 SELECT 陳述式組合成一個虛擬資料表，可以用來簡化日後的查詢操作。 除了這個優點，你也可以透過 VIEW 來更新資料。此外，若搭配使用索引檢視（Indexed View）還可以用來提升查詢效能。

Trigger

TRIGGER 是一種特殊的預存程序，雖然也是由使用者自訂的可程式化物件，但是它不可以直接被使用者執行。 它必須建構在 table 或 view 的特定事件中，如：INSERT, UPDATE, DELETE。 當這些事件發生時，才會自動引發 TRIGGER 執行。

Cursor

SQL Server is built to process sets of data. However, there are times when you need to process data one row at a time. The result of a SELECT statement is returned to a server-side object called a cursor, which allows you to access one row at a time within the result set and even allows scrolling forward as well as backward through the result set.

預存程序的錯誤處理

Stored Procedures

The first time that a stored procedure is accessed, SQL Server generates compile and execution plans that are stored in the query cache and reused for subsequent executions. Therefore, you can receive a slight performance benefit when using a stored procedure by avoiding the need to parse, compile, and generate a query plan on subsequent executions of a stored procedure.

使用內建函式查詢

合併查詢結果

在早期的 SQL Server 版本中就有提供 UNION 運算子，用以結合多個查詢結果。 直到 SQL2005 才又加入了 EXCEPT 和 INTERSECT 運算子，以取得多個查詢結果的差集和交集。

彙總查詢

Aggregate functions allow you to apply calculations on values in a column. Adding the GROUP BY clause allows you to provide aggregate on subsets of the data.

次序函數

排名函數是指可以傳回查詢結果的序號或排名的函式。 自 SQL 2005 關始，SQL Server 開始提供符合 ANSI SQL:2003 標準的排名函數，以方便使用者能夠將資料做相關的其他用途。

• ROW_NUMBER ：為每筆結果加上一個序列號。
• RANK ：若遇到相同資料值，則同序列號；後續資料則跳號。
• DENSE_RANK ：若遇到相同資料值，則同序列號；後續資料不跳號，接續排名。
• NTILE ：根據帶入的參數，將資料分割成 N 群組。

ROW_NUMBER() OVER ( [ <partition_by> ] < order_by_clause > )
RANK()       OVER ( [ <partition_by> ] < order_by_clause > )
DENSE_RANK() OVER ( [ <partition_by> ] < order_by_clause > )
NTILE (n)    OVER ( [ <partition_by> ] < order_by_clause > )

子查詢

子查詢讓關發人員仍夠以較直覺的方式撰寫 TSQL ，也能夠讓較複雜的查詢在單一次的查詢中完成。 子查詢最多能夠使用至 32 層，但仍取決於伺服器的可用記憶體及查詢的複雜度。

通用資料表運算式(CTE)

通用資料表運算式（CTEs）

通用資料表運算式 (Common Table Expression) 是 SQL 2005 才開始支援的查詢語法。 它有二個主要的用途：簡化子查詢與遞迴查詢。

資料完整性

資料的完整性指的就是資料的正確性，通常我們都會在程式端撰寫程式驗證資料，較少在資料庫端驗證，這個文章就是要來介紹如何設計資料的驗證。

資料型別

Transactions

Output 與 Merge

The OUTPUT clause allows you to return information from rows affected by an INSERT, UPDATE, or DELETE statement. With this functionality, you can perform additional tasks more cleanly based on the information provided. These tasks can include confirmation e-mails, data auditing, and similar duties.

The MERGE statement provides you WITH the ability to perform an INSERT, UPDATE, or DELETE operation on a target table based on a set of rules that are determined by a row comparison between the target table and a source table.

資料新增、修改、刪除

使用 JOIN 查詢

SQL 中的 JOIN 運算，大至包含以下幾種分類

1. INNER JOIN
2. OUTER JOIN
   • LEFT OUTER JOIN
   • RIGHT OUTER JOIN
   • FULL OUTER JOIN
3. CROSS JOIN
4. Self-Joins

INNER 與 OUTER 關鍵字皆可以省略。

查詢基本語法

Select

判斷 Null 值

Like

Between