2013年5月2日 星期四

資料型別

資料型別（Data Types）

SQL Server 的資料型別，可大至分成二大類：

  • 系統資料型別
  • 使用者自訂型別(UDTs)或

文字型別（Character Type）

類型型別範圍位元數2008
非Unicodechar(n)固定長度1~8000字，區分全半型1字 1Byte
varchar(n)變動長度1~8000字，區分全半型1字 1Byte
varchar(max)變動大型儲存體0 ～ 2GB
Unicodenchar(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
精確數值
（整數）		bit0、1、null1 bit 
tinyint0 ～ 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-18
精確數值
（小數）		numeric-1038+1 ～ 1038-15 ～ 17
decimal-1038+1 ～ 1038-15 ～ 17
精確數值
（貨幣）		money-922,337,203,685,477.5808 到 922,337,203,685,477.58078
smallmomey- 214、748.3648 到 214、748.36474
近似數值float–3.40E38 ～ –1.18E-38、0、1.18E-38 ～ 3.40E38n:1~24 4
n:25~53 8
real–1.79E308 ～–2.23E-308、0、2.23E-308 ～ 1.79E3084

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
日期類型 datetime1753/01/01 00:00:00.000 ~ 9999/12/31 23:59:59.999 （精確度：0.00333秒）8
SmallDatetime1900/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＊
date0001/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
XMLXML 資料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

  • CAST ：轉換運算式的資料類型。
  • CONVERT ：轉換運算式的資料類型。 
DECLARE @Time1 DateTime = '2012-05-10 06:05:003';

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 ：轉換定序。可套用在資料庫，資料表，或者陳述式之中用來做定序轉換之用。

在 SQL 2008 中，共提供了 2397 個定序，你可以使用 sys.fn_helpcollations 函式取得這些定序資訊。

當您建立資料表時，可以使用 CREATE TABLE 陳述式的 COLLATE 子句來指定字元字串型別資料行的定序。若未指定任何定序，就會使用資料庫的預設定序指派給此資料行。 

--取得 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 型別
數字bigintInt64
intInt32
smallintInt16
tinyint Byte
realSingle
decimalDecimal
floatDouble
numericDecimal
moneyDecimal
smallmoneyDecimal
moneyDecimal
日期時間dateDateTime
datetimeDateTime
datetime2DateTime
smalldatetimeDateTime
timeTimeSpan
文字(n)charString
(n)textString
(n)varcharString
識別uniqueidentifierGuid
Xmlxml Xml

張貼者： 於
標籤：

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)