資料型別（Data Types）
SQL Server 的資料型別，可大至分成二大類：
- 系統資料型別
- 使用者自訂型別(UDTs)或
文字型別（Character Type）
| 類型 | 型別 | 範圍 | 位元數 | 2008 |
|---|---|---|---|---|
| 非Unicode | char(n) | 固定長度1~8000字，區分全半型 | 1字 1Byte | |
| varchar(n) | 變動長度1~8000字，區分全半型 | 1字 1Byte | ||
| varchar(max) | 變動大型儲存體 | 0 ～ 2GB | ||
| Unicode | nchar(n) | 固定長度1~4000字，不區分全半型，為Unicode編碼 | 1字 2Byte | |
| nvarchar(n) | 變動長度1~4000字，不區分全半型，為Unicode編碼 | 1字 2Byte | ||
| nvarchar(max) | 變動大型儲存體 | 0 ～ 2GB |
text and ntext
這二個型別在未來的 SQL 版本將不再支援，所以應避免再使用。
請使用 varchar(max) 或 nvarchar(max) 替換。
char vs varchar
- char is fixed-length and varchar is variable-length
- char always allocates enough storage space to store its entire declared length
- varchar stores only the actual data entered.
定序（collation）
當使用 char 或 varchar 型別時，你必須同時設定該欄位的定序（collation），用來指明文字編碼所使用的字元集（ Code Page or Character Set）和文字的排序方式。
針對文字的排序與比對方式，每種定序又被區分以下幾個類別：
- Case Sensitivity(CS) ： 區分大小寫
若是 Case Sensitivity 則 A 與 a 是不同的； 若是 Case Insesitivity 則 A 與 a 是相同的，也就是查詢A，連同a也會被查詢到。 - Accent Sensitivity(AS) ： 區分腔調
AS/AI 是用來設定是否區分腔調字。
例如：a 跟 á 、 o 跟 ö 在腔調上是相同的，若要區分就設成 AS ，若不區分就設成 AI (Accent Insesitivity)。 - Kana Sensitivity(KS) ： 區分平假名(Katakana)片假名(Hiragana)
KS/KI 是用來設定是否將日文中的平假名和片假名視為相同。 - Width Sensitivity(WS) ： 區分半形與全型字
WS/WI 是用來設定是否將半形字與全型字視為相同。
所以，每種定序分別代表著不同的代碼頁，不同的排序與比對的規則。 在 SQL 2008 中，共提供了 2397 個定序，你可以使用 fn_helpcollations() 取得這些定序資訊。
Chinese_Taiwan_Stroke_CI_AI_WS
像上面這個定序，就表示：
- Code Page = 950
- Case Insensitive
- Accent Insensitive
- Kana Insensitive
- Width Sensitivive
nchar vs nvarchar
這二種都是 Unicode 型別，使用 Unicode Universal Code Page (UCS-2) 編碼，所以每個字都使用 2 個 byte 。 所以你可以不用考量使用哪一種代碼頁的定序，不過，你仍然必須指定一個定序，因為定序包含文字的排序與比對規則的定義。
必要時，你也可以依特定的定序，以進行資料的比對。 下面例子，預設的定序會區分全型與半型字，你也可以在表式中指定不區分半型與全型的定序以進行資料比對。
區分全型半型
不區分全型半型
數字型別（Numeric Types）
| 類型 | 型別 | 範圍 | 位元數 | 2008 |
|---|---|---|---|---|
| 精確數值 （整數） | bit | 0、1、null | 1 bit | |
| tinyint | 0 ～ 28-1 (0 ～ 255) | 1 | ||
| smallint | -215 ～ 215-1 (-32,768 ～ 32,767) | 2 | ||
| int | -231 ～ 231-1 (-2,147,483,648 ～ 2,147,483,647) | 4 | ||
| bigint | -263 ～ 263-1 | 8 | ||
| 精確數值 （小數） | numeric | -1038+1 ～ 1038-1 | 5 ～ 17 | |
| decimal | -1038+1 ～ 1038-1 | 5 ～ 17 | ||
| 精確數值 （貨幣） | money | -922,337,203,685,477.5808 到 922,337,203,685,477.5807 | 8 | |
| smallmomey | - 214、748.3648 到 214、748.3647 | 4 | ||
| 近似數值 | float | –3.40E38 ～ –1.18E-38、0、1.18E-38 ～ 3.40E38 | n:1~24 4 n:25~53 8 | |
| real | –1.79E308 ～–2.23E-308、0、2.23E-308 ～ 1.79E308 | 4 |
numeric and decimal
這二個型別在 SQL 中基本上是一樣的。它們最多可以使用 38 個數字來表達，其語法如下：
numeric(p, s)
- P : precision, 表示有效位數（小數點左右二邊的位數和）
- S : scale, 表示小數位數
因為只能使用 38 個數字來表達，所以 P 最大就是 38 。而 S 必須小於或等於 P 。例如：
- XXXX.yyy : 這可以用 numeric(7, 3) 表示。
- numeric(38, 0) : 這是可表達範圍中的最大值，也就是有效位數全部都是整數。
- numeric(38, 38): 這是可表達範圍中的最小值，也就是整數為０，小數有38個。
- numeric(18, 0) : 這是預設值。
float and real
- float 和 real 資料類型也稱為近似資料類型。
- real 相當於 float(24)
- 近似數值資料類型不會儲存對許多數字指定的精確數值，而會儲存十分接近的近似值。
- 在需要精確數值行為時請勿使用這些資料類型，例如財務應用程式、牽涉到四捨五入的作業或者進行相等檢查作業時。
- 避免在 WHERE 子句的搜尋條件中使用 float 或 real 資料行，尤其是 = 與 <> 運算子。最好將 float 和 real 資料行限制為 > 或 < 比較。
日期和時間型別（Date and Time）
| 類型 | 型別 | 範圍 | 位元數 | 2008 |
|---|---|---|---|---|
| 日期類型 | datetime | 1753/01/01 00:00:00.000 ~ 9999/12/31 23:59:59.999 （精確度：0.00333秒） | 8 | |
| SmallDatetime | 1900/01/01 00:00 ~ 2079/06/06 23:59 （精確度：１分） | 4 | ||
| datetime2(n) | 0001/01/01 00:00:00.0000000 ~ 9999/12/31 23:59:59.9999999 （精確度：n:0~7，10-n秒） | 6~8 | ＊ | |
| date | 0001/01/01 ~ 9999/12/31 （精確度：１天） | 3 | ＊ | |
| time(n) | 00:00:00.0000000 ~ 23:59:59.9999999 （精確度：n:0~7，10-n秒） | 3~5 | ＊ | |
| DatetimeOffset(n) | 0001/01/01 00:00:00.0000000 ~ 9999/12/31 23:59:59.9999999 （以UTC為單位） | 8~10 | ＊ |
不得不知：
- datetime 的精準度是 3/1000 秒，所以 datetime 欄位值的最後一碼，一定是 0, 3, or 7 。
- smalldatetime 的精準度是 1 分
- date 的格式為: YYYY-MM-DD
- time 的格式為: HH:MI:SS.NNNNNNN
- datetime2 結合 date 和 time 型別，格式為: YYYY-MM-DD HH:MI:SS.NNNNNNN
DatetimeOffset 的格式為: YYYY-MM-DD HH:MI:SS.NNNNNNN+ -HH:MI
用法
select * from tableb where t_datetime='2013/05/03 19:18:26.463'
select * from tableb where t_date='2013/05/03'
select * from tableb where t_time='19:18:30'
update tableb set t_datetime='2013/05/03 19:18:26.462' -- 系統會儲存成尾碼3 -> 2013/05/03 19:18:26.463
二進位資料
| 類型 | 型別 | 範圍 | 位元數 | 2008 |
|---|---|---|---|---|
| 二進位資料 | binary(n) | 固定長度 1~8000 Byte | ||
| varbinary(n) | 變動長度 1~8000 Byte | |||
| varbinary(max) | 變動大型儲存體 | 0 ～ 2GB | ||
| 空間資料 | Geometry | 利用 CLR 實作資料類型，適用平面座標系統的資料 | ＊ | |
| Geography | 利用 CLR 實作資料類型，適用經緯度座標系統的資料 | ＊ |
其他型別
| 型別 | 範圍 | 位元數 | 2008 |
|---|---|---|---|
| XML | XML 資料 | 0 ～ 2GB | |
| UniqueIdentifier | 全域唯一識別碼 | 16 | |
| Timestamp | 資料庫內自動產生唯一的二進位值 | ||
| HierarchyID | 利用 CLR 實作資料類型，用以管理具有階層式結構的資料及資料表 | ＊ |
特殊型別
以下型別無法使用於實體資料表中的欄位型態。常用於建立資料表變數之中。
| 型別 | 適用時機 | 2008 |
|---|---|---|
| table | 用以儲存結果集的資料表的資料類型 | |
| cursor | 用以建立資料指標 | |
| sql_variant | 可用於儲存各種 SQL Server 支援型別的資料。 例如，若欄位定義為 sql_variant ，則可以儲存 int、binary 和 char 等型別的值。 | |
| sysname | 內建資料型別，限制 128 Unicode 大小，不允許 NULL。 用於儲存物件名稱。 |
DECLARE @tDBSize TABLE(
DBName SYSNAME DEFAULT(DB_NAME()),
Fileid INT,
FileGroup INT,
TotalExtents INT,
UsedExtents INT,
Name SYSNAME,
FileName NVARCHAR(4000)
)
INSERT @tDBSize(Fileid,FileGroup,TotalExtents,UsedExtents,Name,FileName)
EXEC ('DBCC showfilestats')
SELECT
DBName N'資料庫',Name N'資料檔案',
TotalExtents\*64.0/1024 N'資料檔案使用硬碟空間(MB)',
UsedExtents\*64.0/1024 N'資料實際使用空間(MB)',
FileName N'實體檔案路徑'
FROM @tDBSize
使用者定義資料表類型
暫略
計算的資料行（Computed Columns）
計算的資料行不能算是一種資料型別，而是 SQL Server 提供一種特殊的欄位格式，稱為計算的資料行（Computed Columns）. 這種欄位的值，是使用同一資料表中其他欄位的值運算而成的。
建立計算的資料行
1) 使用SSMS的UI進行設定
2) 使用 DDL 進行設定
--建立計算欄位
ALTER TABLE Orders
ADD OrderMonth AS MONTH(OrderDate);
ALTER TABLE OrderDetails
ADD SubTotal1 AS ( UnitPrice \* Quantity )
計算的資料行也可以使用自訂函式
ALTER TABLE Products
ADD Price2 AS dbo.GetProductPrice(ProductID);
計算資料行的使用限制
- 若要在計算資料行上使用 CHECK、FK、NOT NULL 條件約束，則該欄位必須標示為 PERSISTED。
- 若希望計算的資料行將值實際儲存，而不是等到要用時才計算，則該欄位必須標示為 PERSISTED。
- 若該計算資料行是可決定性的(deterministic expression)，則該欄位也可以成為索引鍵。
- 計算資料行無法成為 INSERT 或 UPDATE 陳述式的目標。
型別轉換
Convert and Cast
SELECT CAST( @Time1 as varchar(10) ), –05 10 2012 CAST( @Time1 as varchar(15) ), –05 10 2012 6:0 CAST( @Time1 as varchar(18) ) –05 10 2012 6:05AM
SELECT CONVERT( varchar(18), @Time1, 0 ), –05 10 2012 6:05AM CONVERT( varchar(18), @Time1, 101 ), –05/10/2012 CONVERT( varchar(18), @Time1, 102 ), –2012.05.10 CONVERT( varchar(18), @Time1, 103 ), –10/05/2012 CONVERT( varchar(18), @Time1, 108 ), –06:05:03 CONVERT( varchar(18), @Time1, 111 ), –2012/05/10 CONVERT( varchar(18), @Time1, 112 ) –20120510
## Collate
- [COLLATE](http://msdn.microsoft.com/zh-tw/library/ms184391.aspx) ：轉換定序。可套用在資料庫，資料表，或者陳述式之中用來做定序轉換之用。
在 SQL 2008 中，共提供了 2397 個定序，你可以使用 [sys.fn_helpcollations](http://msdn.microsoft.com/zh-tw/library/ms187963.aspx) 函式取得這些定序資訊。
當您建立資料表時，可以使用 [CREATE TABLE](http://msdn.microsoft.com/zh-tw/library/ms174979.aspx) 陳述式的 [COLLATE](http://msdn.microsoft.com/zh-tw/library/ms184391.aspx) 子句來指定字元字串型別資料行的定序。若未指定任何定序，就會使用資料庫的預設定序指派給此資料行。
```sql
--取得 SERVER 的預設定序
SELECT CONVERT (varchar, SERVERPROPERTY('collation'))
--取得 DATABASE 的定序
SELECT CONVERT (varchar, DATABASEPROPERTYEX('test','collation')) --test is a DatabaseName
--TABLE 本身沒有定序 , 但它的字串型別欄位才有，若建立欄位時沒有特別指明定序，就會使用資料庫的預設定序.
--取得 Products.ProductName 的定序
SELECT name as ColName, collation_name as Collation
FROM sys.columns
WHERE
OBJECT_NAME(object_id)='Products'
AND name='ProductName'
--變更 DATABASE 的定序
ALTER DATABASE test COLLATE Chinese_Taiwan_Stroke_CI_AS
--變更資料行的定序
ALTER TABLE myTable ALTER COLUMN mycol NVARCHAR(10) COLLATE Greek_CS_AI
--轉換結詢結果的定序
SELECT EmpName COLLATE Chinese_Taiwan_Stroke_CS_AS
FROM Emp ORDER BY EmpName
--使用不同定序做排序
SELECT EmpName FROM Emp
ORDER BY EmpName COLLATE Chinese_Taiwan_Stroke_CS_AS ASC;
SQL 資料型別與.NET Framework 型別對應
| SQL 型別 | .NET Framework 型別 | |
|---|---|---|
| 數字 | bigint | Int64 |
| int | Int32 | |
| smallint | Int16 | |
| tinyint | Byte | |
| real | Single | |
| decimal | Decimal | |
| float | Double | |
| numeric | Decimal | |
| money | Decimal | |
| smallmoney | Decimal | |
| money | Decimal | |
| 日期時間 | date | DateTime |
| datetime | DateTime | |
| datetime2 | DateTime | |
| smalldatetime | DateTime | |
| time | TimeSpan | |
| 文字 | (n)char | String |
| (n)text | String | |
| (n)varchar | String | |
| 識別 | uniqueidentifier | Guid |
| Xml | xml | Xml |