在創(chuàng)建表時，必須為表中的每列指派一種數(shù)據(jù)類型。本節(jié)將介紹SQL Server中最常用的一些數(shù)據(jù)類型。即使創(chuàng)建自定義數(shù)據(jù)類型，它也必須基于一種標準的SQL Server數(shù)據(jù)類型。例如，可以使用如下語法創(chuàng)建一種自定義數(shù)據(jù)類型(Address)，但要注意，它基于SQL Server標準的varchar數(shù)據(jù)類型。
如果在SQL Server Management Studio的表設計界面中更改一個大型表中某列的數(shù)據(jù)類型，那么該操作可能需要很長時間。可以通過在Management Studio界面中腳本化這種改變來觀察其原因。Management Studio再創(chuàng)建一個臨時表，采用像tmpTableName這樣的名稱，然后將數(shù)據(jù)復制到該表中。最后，界面刪除舊表并用新的數(shù)據(jù)類型重命名新表。當然，此過程中還涉及其他一些用于處理表中索引和其他任何關系的步驟。
如果有一個包含數(shù)百萬條記錄的大型表，則該過程可能需要花費十分鐘，有時可能是數(shù)小時。為避免這種情況，可在查詢窗口中使用簡單的單行T-SQL語句來更改該列的數(shù)據(jù)類型。例如，要將Employees表中JobTitle列的數(shù)據(jù)類型改為varchar(70)，可以使用如下語法。
說明：
在轉換為與當前數(shù)據(jù)不兼容的數(shù)據(jù)類型時，可能丟失重要數(shù)據(jù)。例如，如果要將包含一些數(shù)據(jù)(如15.415)的numeric數(shù)據(jù)類型轉換為integer數(shù)據(jù)類型，那么15.415這個數(shù)據(jù)將四舍五入為整數(shù)。
您可能想對SQL Server表編寫一個報表，顯示表中每列的數(shù)據(jù)類型。完成這項任務的方法有很多種，但最常見的一種方法是連接sys.objects表和sys.columns表。在下面的代碼中，有兩個函數(shù)可能不太為您所熟悉。函數(shù)TYPE_NAME()將數(shù)據(jù)類型id轉換為適當?shù)拿Q。要進行反向操作，可使用TYPE_ID()函數(shù)。需要注意的另一個函數(shù)是SCHEMA_ID()，它用于返回架構的標識值。在需要編寫有關SQL Server元數(shù)據(jù)的報表時，這是特別有用的。
該代碼返回如下結果(注意，Name是一種用戶定義的數(shù)據(jù)類型)。
1. 字符數(shù)據(jù)類型
字符數(shù)據(jù)類型包括varchar、char、nvarchar、nchar、text以及ntext。這些數(shù)據(jù)類型用于存儲字符數(shù)據(jù)。varchar和char類型的主要區(qū)別是數(shù)據(jù)填充。如果有一表列名為FirstName且數(shù)據(jù)類型為varchar(20)，同時將值Brian存儲到該列中，則物理上只存儲5個字節(jié)。但如果在數(shù)據(jù)類型為char(20)的列中存儲相同的值，將使用全部20個字節(jié)。SQL將插入拖尾空格來填滿20個字符。
如果要節(jié)省空間，那么為什么還使用char數(shù)據(jù)類型呢？使用varchar數(shù)據(jù)類型會稍增加一些系統(tǒng)開銷。例如，如果要存儲兩字母形式的州名縮寫，則最好使用char(2)列。盡管有些DBA認為應最大可能地節(jié)省空間，但一般來說，好的做法是在組織中找到一個合適的閾值，并指定低于該值的采用char數(shù)據(jù)類型，反之則采用varchar數(shù)據(jù)類型。通常的原則是，任何小于或等于5個字節(jié)的列應存儲為char數(shù)據(jù)類型，而不是varchar數(shù)據(jù)類型。如果超過這個長度，使用varchar數(shù)據(jù)類型的好處將超過其額外開銷。
nvarchar數(shù)據(jù)類型和nchar數(shù)據(jù)類型的工作方式與對等的varchar數(shù)據(jù)類型和char數(shù)據(jù)類型相同，但這兩種數(shù)據(jù)類型可以處理國際性的Unicode字符。它們需要一些額外開銷。以Unicode形式存儲的數(shù)據(jù)為一個字符占兩個字節(jié)。如果要將值Brian存儲到nvarchar列，它將使用10個字節(jié)；而如果將它存儲為nchar(20)，則需要使用40字節(jié)。由于這些額外開銷和增加的空間，應該避免使用Unicode列，除非確實有需要使用它們的業(yè)務或語言需求。
接下來要提的數(shù)據(jù)類型是text和ntext。text數(shù)據(jù)類型用于在數(shù)據(jù)頁內外存儲大型字符數(shù)據(jù)。應盡可能少地使用這兩種數(shù)據(jù)類型，因為可能影響性能但可在單行的列中存儲多達2GB的數(shù)據(jù)。與text數(shù)據(jù)類型相比，更好的選擇是使用varchar(max)類型，因為將獲得更好的性能。另外，text和ntext數(shù)據(jù)類型在SQL Server的一些未來版本中將不可用，因此現(xiàn)在開始還是最好使用varchar(max)和nvarchar(max)而不是text和ntext數(shù)據(jù)類型。
表1-1列出了這些類型，對其作了簡單描述，并說明了要求的存儲空間。
表1-1
數(shù) 據(jù) 類 型 | 描 述 | 存 儲 空 間 |
Char(n) | N為1~8000字符之間 | n字節(jié) |
Nchar(n) | N為1~4000 Unicode字符之間 | (2n字節(jié))＋2字 節(jié)額外開銷 |
Ntext | 最多為230–1 (1?073?741?823)Unicode字符 | 每字符2字節(jié) |
Nvarchar(max) | 最多為230–1 (1?073?741?823)Unicode字符 | 2×字符數(shù)＋2字 節(jié)額外開銷 |
Text | 最多為231–1 (2?147?483?647)字符 | 每字符1字節(jié) |
Varchar(n) | N為1~8000字符之間 | 每字符1字節(jié)＋2 字節(jié)額外開銷 |
Varchar(max) | 最多為231–1(2?147?483?647)字符 | 每字符1字節(jié)＋2 字節(jié)額外開銷 |
2. 精確數(shù)值數(shù)據(jù)類型
數(shù)值數(shù)據(jù)類型包括bit、tinyint、smallint、int、bigint、numeric、decimal、money、float以及real。這些數(shù)據(jù)類型都用于存儲不同類型的數(shù)字值。第一種數(shù)據(jù)類型bit只存儲0或1，在大多數(shù)應用程序中被轉換為true或false。bit數(shù)據(jù)類型非常適合用于開關標記，且它只占據(jù)一個字節(jié)空間。其他常見的數(shù)值數(shù)據(jù)類型如表1-2所示。
表1-2
數(shù) 據(jù) 類 型 | 描 述 | 存 儲 空 間 |
bit | 0、1或Null | 1字節(jié)(8位) |
tinyint | 0～255之間的整數(shù) | 1字節(jié) |
smallint | –32?768～32?767之間的整數(shù) | 2字節(jié) |
int | –2?147?483?648～ 2?147?483?647之間的整數(shù) | 4字節(jié) |
bigint | –9?223?372?036?854?775?808～ 9?223?372?036?854?775?807 之間的整數(shù) | 8字節(jié) |
(續(xù)表)
數(shù) 據(jù) 類 型 | 描 述 | 存 儲 空 間 |
numeric(p,s)或 decimal(p,s) | –1?038＋1～1?038–1之間的數(shù)值 | 最多17字節(jié) |
money | –922?337?203?685?477.580?8～ 922?337?203?685?477.580?7 | 8字節(jié) |
smallmoney | –214?748.3648～2?14?748.3647 | 4字節(jié) |
SQL Server 2008數(shù)據(jù)類型（2）
如decimal和numeric等數(shù)值數(shù)據(jù)類型可存儲小數(shù)點右邊或左邊的變長位數(shù)。Scale是小數(shù)點右邊的位數(shù)。精度(Precision)定義了總位數(shù)，包括小數(shù)點右邊的位數(shù)。例如，由于14.88531可為numeric(7,5)或decimal(7,5)。如果將14.25插入到numeric(5,1)列中，它將被舍入為14.3。
3. 近似數(shù)值數(shù)據(jù)類型
這個分類中包括數(shù)據(jù)類型float和real。它們用于表示浮點數(shù)據(jù)。但是，由于它們是近似的，因此不能精確地表示所有值。
float(n)中的n是用于存儲該數(shù)尾數(shù)(mantissa)的位數(shù)。SQL Server對此只使用兩個值。如果指定位于1~24之間，SQL就使用24。如果指定25~53之間，SQL就使用53。當指定float()時(括號中為空)，默認為53。
表1-3列出了近似數(shù)值數(shù)據(jù)類型，對其進行簡單描述，并說明了要求的存儲空間。
表1-3
數(shù) 據(jù) 類 型 | 描 述 | 存 儲 空 間 |
float[(n)] | –1.79E＋308~–2.23E– 308,0,2.23E–308~1.79E＋308 | N< =24－4字節(jié) N> 24－8字節(jié) |
real() | –3.40E＋38~–1.18E– 38,0,1.18E–38~3.40E＋38 | 4字節(jié) |
注意：
real的同義詞為float(24)。
4. 二進制數(shù)據(jù)類型
如varbinary、binary、varbinary(max)或image等二進制數(shù)據(jù)類型用于存儲二進制數(shù)據(jù)，如圖形文件、Word文檔或MP3文件。其值為十六進制的0x0~0xf。image數(shù)據(jù)類型可在數(shù)據(jù)頁外部存儲最多2GB的文件。image數(shù)據(jù)類型的首選替代數(shù)據(jù)類型是varbinary(max)，可保存最多8KB的二進制數(shù)據(jù)，其性能通常比image數(shù)據(jù)類型好。SQL Server 2008的新功能是可以在操作系統(tǒng)文件中通過FileStream存儲選項存儲varbinary(max)對象。這個選項將數(shù)據(jù)存儲為文件，同時不受varbinary(max)的2GB大小的限制。
表1-4列出了二進制數(shù)據(jù)類型，對其作了簡單描述，并說明了要求的存儲空間。
表1-4
數(shù) 據(jù) 類 型 | 描 述 | 存 儲 空 間 |
Binary(n) | N為1~8000十六進制數(shù)字之間 | n字節(jié) |
Image | 最多為231–1 (2?147?483?647)十六進制數(shù)位 | 每字符1字節(jié) |
Varbinary(n) | N為1~8000十六進制數(shù)字之間 | 每字符1字節(jié) ＋2字節(jié)額外開銷 |
Varbinary(max) | 最多為231–1 (2?147?483?647)十六進制數(shù)字 | 每字符1字節(jié) ＋2字節(jié)額外開銷 |
5. 日期和時間數(shù)據(jù)類型
datetime和smalldatetime數(shù)據(jù)類型用于存儲日期和時間數(shù)據(jù)。smalldatetime為4字節(jié)，存儲1900年1月1日~2079年6月6日之間的時間，且只精確到最近的分鐘。datetime數(shù)據(jù)類型為8字節(jié)，存儲1753年1月1日~9999年12月31日之間的時間，且精確到最近的3.33毫秒。
SQL Server 2008有4種與日期相關的新數(shù)據(jù)類型：datetime2、dateoffset、date和time。通過SQL Server聯(lián)機叢書可找到使用這些數(shù)據(jù)類型的示例。
datetime2數(shù)據(jù)類型是datetime數(shù)據(jù)類型的擴展，有著更廣的日期范圍。時間總是用時、分鐘、秒形式來存儲?？梢远x末尾帶有可變參數(shù)的datetime2數(shù)據(jù)類型--如datetime2(3)。這個表達式中的3表示存儲時秒的小數(shù)精度為3位，或0.999。有效值為0~9之間，默認值為3。
datetimeoffset數(shù)據(jù)類型和datetime2數(shù)據(jù)類型一樣，帶有時區(qū)偏移量。該時區(qū)偏移量最大為＋/-14小時，包含了UTC偏移量，因此可以合理化不同時區(qū)捕捉的時間。
date數(shù)據(jù)類型只存儲日期，這是一直需要的一個功能。而time數(shù)據(jù)類型只存儲時間。它也支持time(n)聲明，因此可以控制小數(shù)秒的粒度。與datetime2和datetimeoffset一樣，n可為0~7之間。
表1-5列出了日期/時間數(shù)據(jù)類型，對其進行簡單描述，并說明了要求的存儲空間。
表1-5
數(shù) 據(jù) 類 型 | 描 述 | 存 儲 空 間 |
Date | 9999年1月1日~12月31日 | 3字節(jié) |
Datetime | 1753年1月1日~9999年12月31日， 精確到最近的3.33毫秒 | 8字節(jié) |
Datetime2(n) | 9999年1月1日~12月31日 0~7之間的N指定小數(shù)秒 | 6~8字節(jié) |
Datetimeoffset(n) | 9999年1月1日~12月31日 0~7之間的N指定小數(shù)秒＋/–偏移量 | 8~10字節(jié) |
SmalldateTime | 1900年1月1日~2079年 6月6日，精確到1分鐘 | 4字節(jié) |
Time(n) | 小時:分鐘:秒.9999999 0~7之間的N指定小數(shù)秒 | 3~5字節(jié) |
6. 其他系統(tǒng)數(shù)據(jù)類型
還有一些之前未見過的數(shù)據(jù)類型。表1-6列出了這些數(shù)據(jù)類型。
表1-6
數(shù) 據(jù) 類 型 | 描 述 | 存 儲 空 間 |
Cursor | 包含一個對光標的引用和 可以只用作變量或存儲過程參數(shù) | 不適用 |
Hierarchyid | 包含一個對層次結構中位置的引用 | 1~892字節(jié)＋2 字節(jié)的額外開銷 |
SQL_Variant | 可能包含任何系統(tǒng)數(shù)據(jù)類 型的值，除了text、ntext、 image、timestamp、xml、 varchar(max)、nvarchar(max)、 varbinary (max)、sql_variant以 及用戶定義的數(shù)據(jù)類型。最大尺 寸為8000字節(jié)數(shù)據(jù)＋16字節(jié) (或元數(shù)據(jù)) | 8016字節(jié) |
Table | 用于存儲用于進一步處理的數(shù) 據(jù)集。定義類似于Create Table。 主要用于返回表值函數(shù)的結果集， 它們也可用于存儲過程和批處理中 | 取決于表定 義和存儲的行數(shù) |
Timestamp or Rowversion | 對于每個表來說是唯一的、自 動存儲的值。通常用于版本戳， 該值在插入和每次更新時自動改變 | 8字節(jié) |
Uniqueidentifier | 可以包含全局唯一標識符 (Globally Unique Identifier， GUID)。guid值可以從Newid() 函數(shù)獲得。這個函數(shù)返回的值對 所有計算機來說是唯一的。 盡管存儲為16位的二進制值， 但它顯示為char(36) | 16字節(jié) |
XML | 可以以Unicode或非Unicode形式存儲 | 最多2GB |
注意：
cursor數(shù)據(jù)類型可能不用于Create Table語句中。
hierarchyid列是SQL Server 2008中新出現(xiàn)的。您可能希望將這種數(shù)據(jù)類型的列添加到這樣的表中--其表行中的數(shù)據(jù)可用層次結構表示，就像組織層次結構或經(jīng)理/雇員層次結構一樣。存儲在該列中的值是行在層次結構中的路徑。層次結構中的級別顯示為斜杠。斜杠間的值是這個成員在行中的數(shù)字級別，如/1/3?？梢赃\用一些與這種數(shù)據(jù)類型一起使用的特殊函數(shù)。
XML數(shù)據(jù)存儲XML文檔或片段。根據(jù)文檔中使用UTF-16或是UTF-8，它在尺寸上像text或ntext一樣存儲。XML數(shù)據(jù)類型使用特殊構造體進行搜索和索引。第15章將更詳細地介紹這些內容。
7. CLR集成
在SQL Server 2008中，還可使用公共語言運行庫(Common Language Runtime，CLR)創(chuàng)建自己的數(shù)據(jù)類型和存儲過程。這讓用戶可以使用Visual Basic或C#編寫更復雜的數(shù)據(jù)類型，以滿足業(yè)務需求。這些類型被定義為基本的CLR語言中的類結構。第8章將更詳細地介紹其管理部分的內容。
