SQL Server 2008資料類型

標籤：

在建立表時，必須為表中的每列指派一種資料類型。本節將介紹SQL Server中最常用的一些資料類型。即使建立自訂資料類型，它也必須基於一種標準的SQL Server資料類型。例如，可以使用如下文法建立一種自訂資料類型(Address)，但要注意，它基於SQL Server標準的varchar資料類型。

1. CREATE TYPE Address  
2. FROM varchar(35) NOT NULL 

如果在SQL Server Management Studio的表設計介面中更改一個大型表中某列的資料類型，那麼該操作可能需要很長時間。可以通過在Management Studio介面中指令碼化這種改變來觀察其原因。Management Studio再建立一個暫存資料表，採用像tmpTableName這樣的名稱，然後將資料複製到該表中。最後，介面刪除舊錶並用新的資料類型重新命名新表。當然，此過程中還涉及其他一些用於處理表中索引和其他任何關係的步驟。

如果有一個包含數百萬條記錄的大型表，則該過程可能需要花費十分鐘，有時可能是數小時。為避免這種情況，可在查詢時段中使用簡單的單行T-SQL語句來更改該列的資料類型。例如，要將Employees表中JobTitle列的資料類型改為varchar(70)，可以使用如下文法。

1. ALTER TABLE HumanResources.Employee ALTER COLUMN JobTitle Varchar(70) 

說明：

在轉換為與當前資料不相容的資料類型時，可能丟失重要資料。例如，如果要將包含一些資料(如15.415)的numeric資料類型轉換為integer資料類型，那麼15.415這個資料將四捨五入為整數。

您可能想對SQL Server表編寫一個報表，顯示表中每列的資料類型。完成這項任務的方法有很多種，但最常見的一種方法是串連sys.objects表和sys.columns表。在下面的代碼中，有兩個函數可能不太為您所熟悉。函數TYPE_NAME()將資料類型id轉換為適當的名稱。要進行反向操作，可使用TYPE_ID()函數。需要注意的另一個函數是SCHEMA_ID()，它用於返回架構的標識值。在需要編寫有關SQL Server中繼資料的報表時，這是特別有用的。

1. SELECT o.name AS ObjectName,  
2. c.name AS ColumnName,  
3. TYPE_NAME(c.user_type_id) as DataType  
4. FROM    sys.objects o JOIN sys.columns c  
5. ON      o.object_id = c.object_id  
6. WHERE   o.name =‘Department‘ 
7. and             o.Schema_ID = SCHEMA_ID(‘HumanResources‘) 

該代碼返回如下結果(注意，Name是一種使用者定義的資料類型)。

1. ObjectName                ColumnName                    DataType  
2. ---------------------------------------------------  
3. Department                DepartmentID          smallint  
4. Department                Name                        
   Name  
5. Department                GroupName                         Name  
6. Department                ModifiedDate        
   datetime 

1. 字元資料類型

字元資料類型包括varchar、char、nvarchar、nchar、text以及ntext。這些資料類型用於儲存字元資料。varchar和char類型的主要區別是資料填充。如果有一表列名為FirstName且資料類型為varchar(20)，同時將值Brian儲存到該列中，則物理上只儲存5個位元組。但如果在資料類型為char(20)的列中儲存相同的值，將使用全部20個位元組。SQL將插入拖尾空格來填滿20個字元。

如果要節省空間的，那麼為什麼還使用char資料類型呢？使用varchar資料類型會稍增加一些系統開銷。例如，如果要儲存兩字母形式的州名縮寫，則最好使用char(2)列。儘管有些DBA認為應最大可能地節省空間的，但一般來說，好的做法是在組織中找到一個合適的閾值，並指定低於該值的採用char資料類型，反之則採用varchar資料類型。通常的原則是，任何小於或等於5個位元組的列應儲存為char資料類型，而不是varchar資料類型。如果超過這個長度，使用varchar資料類型的好處將超過其額外開銷。

nvarchar資料類型和nchar資料類型的工作方式與對等的varchar資料類型和char資料類型相同，但這兩種資料類型可以處理國際性的Unicode字元。它們需要一些額外開銷。以Unicode形式儲存的資料為一個字元佔兩個位元組。如果要將值Brian儲存到nvarchar列，它將使用10個位元組；而如果將它儲存為nchar(20)，則需要使用40位元組。由於這些額外開銷和增加的空間，應該避免使用Unicode列，除非確實有需要使用它們的業務或語言需求。

接下來要提的資料類型是text和ntext。text資料類型用於在資料頁內外儲存大型字元資料。應儘可能少地使用這兩種資料類型，因為可能影響效能但可在單行的列中儲存多達2GB的資料。與text資料類型相比，更好的選擇是使用varchar(max)類型，因為將獲得更好的效能。另外，text和ntext資料類型在SQL Server的一些未來版本中將不可用，因此現在開始還是最好使用varchar(max)和nvarchar(max)而不是text和ntext資料類型。

表1-1列出了這些類型，對其作了簡單描述，並說明了要求的儲存空間。

表1-1

數 據 類 型	描    述	存 儲 空 間
Char(n)	N為1~8000字元之間	n位元組
Nchar(n)	N為1~4000 Unicode字元之間	(2n位元組)＋2字 節額外開銷
Ntext	最多為230–1 (1?073?741?823)Unicode字元	每字元2位元組
Nvarchar(max)	最多為230–1 (1?073?741?823)Unicode字元	2×字元數＋2字 節額外開銷
Text	最多為231–1 (2?147?483?647)字元	每字元1位元組
Varchar(n)	N為1~8000字元之間	每字元1位元組＋2 位元組額外開銷
Varchar(max)	最多為231–1(2?147?483?647)字元	每字元1位元組＋2 位元組額外開銷

2. 精確數值資料類型

數值資料類型包括bit、tinyint、smallint、int、bigint、numeric、decimal、money、float以及real。這些資料類型都用於儲存不同類型的數字值。第一種資料類型bit只儲存0或1，在大多數應用程式中被轉換為true或false。bit資料類型非常適合用於開關標記，且它只佔據一個位元組空間。其他常見的數值資料類型如表1-2所示。

表1-2

數 據 類 型	描    述	存 儲 空 間
bit	0、1或Null	1位元組(8位)
tinyint	0～255之間的整數	1位元組
smallint	–32?768～32?767之間的整數	2位元組
int	–2?147?483?648～ 2?147?483?647之間的整數	4位元組
bigint	–9?223?372?036?854?775?808～ 9?223?372?036?854?775?807 之間的整數	8位元組

(續表)

數 據 類 型	描    述	存 儲 空 間
numeric(p,s)或 decimal(p,s)	–1?038＋1～1?038–1之間的數值	最多17位元組
money	–922?337?203?685?477.580?8～ 922?337?203?685?477.580?7	8位元組
smallmoney	–214?748.3648～2?14?748.3647	4位元組

 

 

    SQL Server 2008資料類型（2）

如decimal和numeric等數值資料類型可儲存小數點右邊或左邊的變長位元。Scale是小數點右邊的位元。精度(Precision)定義了總位元，包括小數點右邊的位元。例如，由於14.88531可為numeric(7,5)或decimal(7,5)。如果將14.25插入到numeric(5,1)列中，它將被舍入為14.3。

3. 近似數值資料類型

這個分類中包括資料類型float和real。它們用於表示浮點數據。但是，由於它們是近似的，因此不能精確地表示所有值。

float(n)中的n是用於儲存該數尾數(mantissa)的位元。SQL Server對此只使用兩個值。如果指定位於1~24之間，SQL就使用24。如果指定25~53之間，SQL就使用53。當指定float()時(括弧中為空白)，預設為53。

表1-3列出了近似數值資料類型，對其進行簡單描述，並說明了要求的儲存空間。

表1-3

數 據 類 型	描    述	存 儲 空 間
float[(n)]	–1.79E＋308~–2.23E– 308,0,2.23E–308~1.79E＋308	N< =24－4位元組 N> 24－8位元組
real()	–3.40E＋38~–1.18E– 38,0,1.18E–38~3.40E＋38	4位元組

注意：

real的同義字為float(24)。

4. 位元據類型

如varbinary、binary、varbinary(max)或image等位元據類型用於儲存位元據，形檔案、Word文檔或MP3檔案。其值為十六進位的0x0~0xf。image資料類型可在資料頁外部儲存最多2GB的檔案。image資料類型的首選替代資料類型是varbinary(max)，可儲存最多8KB的位元據，其效能通常比image資料類型好。SQL Server 2008的新功能是可以在作業系統檔案中通過FileStream儲存選項儲存varbinary(max)對象。這個選項將資料存放區為檔案，同時不受varbinary(max)的2GB大小的限制。

表1-4列出了位元據類型，對其作了簡單描述，並說明了要求的儲存空間。

表1-4

數 據 類 型	描    述	存 儲 空 間
Binary(n)	N為1~8000十六進位數字之間	n位元組
Image	最多為231–1 (2?147?483?647)十六進位數位	每字元1位元組
Varbinary(n)	N為1~8000十六進位數字之間	每字元1位元組 ＋2位元組額外開銷
Varbinary(max)	最多為231–1 (2?147?483?647)十六進位數字	每字元1位元組 ＋2位元組額外開銷

5. 日期和時間資料類型

datetime和smalldatetime資料類型用於儲存日期和時間資料。smalldatetime為4位元組，儲存1900年1月1日~2079年6月6日之間的時間，且只精確到最近的分鐘。datetime資料類型為8位元組，儲存1753年1月1日~9999年12月31日之間的時間，且精確到最近的3.33毫秒。

SQL Server 2008有4種與日期相關的新資料類型：datetime2、dateoffset、date和time。通過SQL Server聯機叢書可找到使用這些資料類型的樣本。

datetime2資料類型是datetime資料類型的擴充，有著更廣的日期範圍。時間總是用時、分鐘、秒形式來儲存。可以定義末尾帶有可變參數的datetime2資料類型--如datetime2(3)。這個運算式中的3表示儲存時秒的小數精度為3位，或0.999。有效值為0~9之間，預設值為3。

datetimeoffset資料類型和datetime2資料類型一樣，帶有時區位移量。該時區位移量最大為＋/-14小時，包含了UTC位移量，因此可以合理化不同時區捕捉的時間。

date資料類型只儲存日期，這是一直需要的一個功能。而time資料類型只儲存時間。它也支援time(n)聲明，因此可以控制小數秒的粒度。與datetime2和datetimeoffset一樣，n可為0~7之間。

表1-5列出了日期/時間資料類型，對其進行簡單描述，並說明了要求的儲存空間。

表1-5

數 據 類 型	描    述	存 儲 空 間
Date	9999年1月1日~12月31日	3位元組
Datetime	1753年1月1日~9999年12月31日， 精確到最近的3.33毫秒	8位元組
Datetime2(n)	9999年1月1日~12月31日 0~7之間的N指定小數秒	6~8位元組
Datetimeoffset(n)	9999年1月1日~12月31日 0~7之間的N指定小數秒＋/–位移量	8~10位元組
SmalldateTime	1900年1月1日~2079年 6月6日，精確到1分鐘	4位元組
Time(n)	小時:分鐘:秒.9999999 0~7之間的N指定小數秒	3~5位元組

6. 其他系統資料類型

還有一些之前未見過的資料類型。表1-6列出了這些資料類型。

表1-6

數 據 類 型	描    述	存 儲 空 間
Cursor	包含一個對游標的引用和 可以只用作變數或預存程序參數	不適用
Hierarchyid	包含一個對階層中位置的引用	1~892位元組＋2 位元組的額外開銷
SQL_Variant	可能包含任何系統資料類 型的值，除了text、ntext、 image、timestamp、xml、 varchar(max)、nvarchar(max)、 varbinary (max)、sql_variant以 及使用者定義的資料類型。最大尺 寸為8000位元組資料＋16位元組 (或中繼資料)	8016位元組
Table	用於儲存用於進一步處理的數 據集。定義類似於Create Table。 主要用於返回資料表值函式的結果集， 它們也可用於預存程序和批處理中	取決於表定 義和儲存的行數
Timestamp or Rowversion	對於每個表來說是唯一的、自 動儲存的值。通常用於版本戳， 該值在插入和每次更新時自動改變	8位元組
Uniqueidentifier	可以包含通用唯一識別碼 (Globally Unique Identifier， GUID)。guid值可以從Newid() 函數獲得。這個函數返回的值對 所有電腦來說是唯一的。 儘管儲存為16位的二進位值， 但它顯示為char(36)	16位元組
XML	可以以Unicode或非Unicode形式儲存	最多2GB

注意：

cursor資料類型可能不用於Create Table語句中。

hierarchyid列是SQL Server 2008中新出現的。您可能希望將這種資料類型的列添加到這樣的表中--其表行中的資料可用階層表示，就像組織階層或經理/僱員階層一樣。儲存在該列中的值是行在階層中的路徑。階層中的層級顯示為斜杠。斜杠間的值是這個成員在行中的數字層級，如/1/3。可以運用一些與這種資料類型一起使用的特殊函數。

XML資料存放區XML文檔或片段。根據文檔中使用UTF-16或是UTF-8，它在尺寸上像text或ntext一樣儲存。XML資料類型使用特殊構造體進行搜尋和索引。第15章將更詳細地介紹這些內容。

7. CLR整合

在SQL Server 2008中，還可使用公用語言運行庫(Common Language Runtime，CLR)建立自己的資料類型和預存程序。這讓使用者可以使用Visual Basic或C#編寫更複雜的資料類型，以滿足業務需求。這些類型被定義為基本的CLR語言中的類結構。第8章將更詳細地介紹其管理部分的內容。

SQL Server 2008資料類型