sys.sysmessages

select msglangid,* from sys.sysmessages where error = '207'

不同語系的錯誤訊息

sys.syslanguages

select msglangid,* from sys.syslanguages
msglangid為1028是繁體中文

Table Scan和Index Scan

Table Scan沒有順序
Index Scan有順序

one clustered index per table

一個資料表只能有一個叢集索引

備註:
一個資料表可以有多個非叢集索引(nonclustered indexes)

In SQL Server, indexes are organized as B-trees.
Nonclustered indexes have the same B-tree structure as clustered indexes,
https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx


ref:
Clustered Index Structures
Nonclustered Index Structures

DBCC CHECKIDENT(testRESEED, RESEED, 1)

DBCC CHECKIDENT(testRESEED, RESEED, 1)
testRESEED為Table名稱
將testRESEED這資料表的索引值，改為從1開始計算
以下insert紅框處，id變成2

SET IDENTITY_INSERT ON

先關閉自動取號
所以insert語法需要加id
一次只能ON一個TABLE
結束後使用SET IDENTITY_INSERT OFF

錯誤訊息:
當 IDENTITY_INSERT 設為 OFF 時，無法將外顯值插入資料表 'Products' 的識別欄位中
(Cannot insert explicit value for identity column in table 'Products' when IDENTITY_INSERT is set to OFF.)

REF:
https://msdn.microsoft.com/en-us/library/ms188059.aspx
http://blog.xuite.net/linriva/blog/35114164-%5BSQL+Server%5D+SET+IDENTITY_INSERT%3ARe-Insert
測試語法：

CTE和子查詢的效能

CTE(Common Table Expressions)和子查詢(subquery)的效能是一樣的
只是CTE語法比較有結構性，會比較好維護

P.S.從SQL Server 2005之後新增CTE語法

REF:
Recursive Queries Using Common Table Expressions
https://msdn.microsoft.com/en-us/library/ms186243(v=SQL.110).aspx

T-SQL -- COMMON TABLE EXPRESSION (CTE) 教學重點筆記
http://blog.kkbruce.net/2011/01/t-sql-common-table-expression-cte.html#.VtxCA7Z96Uk