sqlserver 數據類(lèi)型分類(lèi)
1. 數據類(lèi)型分類(lèi)
1.1. 整型
1.1.1. 概述
在SQL Server 2000中整型按照數據范圍從小到大分為: bit, tinyint, smallint, int, bigint。
1.1.2. 詳細描述
數據類(lèi)型
取值范圍
描述
備注
bit
0, 1, NULL
1. 不能對bit類(lèi)型建立索引
2. bit類(lèi)型最多占用1個(gè)字節。
SQL server會(huì )優(yōu)化bit類(lèi)型存儲，如果一個(gè)表中有不多于 8個(gè)的 bit 列，這些列將作為一個(gè)字節存儲。如果表中有 9到 16 個(gè) bit列，這些列將作為兩個(gè)字節存儲。更多列的情況依此類(lèi)推。
tinyint
從 0 到 255的整型數據。
占用1個(gè)字節
適合于做一些狀態(tài)記錄
smallint
從-32768到32767之間的整數。
占用2個(gè)字節
適合于做一些狀態(tài)記錄以及小的數據記錄。
對應于Java中的short類(lèi)型，但在SMP的實(shí)際使用中將其映射為Integer類(lèi)型
int
從 -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647)的整型數據（所有數字）。
占用4個(gè)字節
對應于Java中的Integer類(lèi)型?？梢员４娲蠖鄶档臄底?div style="height:15px;">
bigint
從 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型數據（所有數字）。
占用8個(gè)字節
對應于Java中的Long型數據。
1.1.3. 小結
SQL Server2000 中最常使用的是int類(lèi)型，tinyint和smallint在計算時(shí)如果發(fā)生溢出，會(huì )自動(dòng)轉換成范圍更大的類(lèi)型進(jìn)行計算，但int不會(huì )隱式轉換為bigint類(lèi)型。
1.1.4. 示例
declare @numInt int
declare @numTinyInt tinyint
declare @numSmallint smallint
set @numInt = 2147483647
set @numTinyInt = 255
set @numSmallint = 32767
print @numInt + 1 -- 出現溢出錯誤
print @numTinyInt + 1
print @numSmallint + 1
1.2. 精確浮點(diǎn)型
1.2.1. 概述
浮點(diǎn)類(lèi)型有兩種，一種是decimal，一種是numeric。
1.2.2. 詳細描述
數據類(lèi)型
取值范圍
描述
備注
decimal[(p[, s])]
使用最大精度時(shí)，有效值從 - 10^38 +1 到 10^38 - 1。
p(精度)：小數點(diǎn)左邊和右邊能存儲十進(jìn)制數字的最大個(gè)數。最大為38
s(小數位數):小數點(diǎn)右邊能存儲十進(jìn)制數字的個(gè)數。
p和s之間的關(guān)系：
0<=s<=p<=38
兩者完全一樣，可以互相賦值，但如果精度或小數位數不同，會(huì )丟失精度。
numeric[(p[, s])]
decimal數據類(lèi)型的大小和精度關(guān)系如下：
精度
存儲字節數
1 - 9
5
10-19
9
20-28
13
29-38
17
1.2.3. 示例
declare @numDec decimal(3, 2)
declare @numNum numeric(3, 1)
set @numDec = 1.5
set @numNum = @numDec
-- 完全一致
print @numNum
set @numDec = 1.55
set @numNum = @numDec
-- 丟失精度
print @numNum
1.3. 貨幣類(lèi)型
1.3.1. 概述
用來(lái)表示金融或貨幣的數據類(lèi)型，有smallmoney和money兩種。
1.3.2. 詳細描述
數據類(lèi)型
取值范圍
描述
備注
smallmoney
-214,748.3648 與 +214.748,3647。
4個(gè)字節
精確到貨幣單位的千分之十。
money
貨幣數據值介于 -2^63 (-922,337,203,685,477.5808)與 2^63 - 1 (+922,337,203,685,477.5807)之間。
8個(gè)字節
精確到貨幣單位的千分之十。
1.3.3. 小結
在使用中貨幣類(lèi)型完全可以使用精確浮點(diǎn)類(lèi)型代替，可能在一些特殊的應用程序中可以用到，大部分情況下使用精確浮點(diǎn)類(lèi)型擴展性更好些。
1.4. 近似浮點(diǎn)型
1.4.1. 概述
用于表示浮點(diǎn)數字數據的近似數字數據類(lèi)型。浮點(diǎn)數據為近似值；并非數據類(lèi)型范圍內的所有數據都能精確地表示，但可以表述的數據范圍極大。
1.4.2. 詳細描述
數據類(lèi)型
取值范圍
描述
備注
float[(n)]
從 - 1.79E + 308 到 1.79E + 308之間的浮點(diǎn)數字數據。
n表示使用科學(xué)記數法時(shí)尾數的位數，同時(shí)指示其精度和存儲大小。n的取值范圍是0～53
double表示float(53)
real
從 -3.40E + 38 到 3.40E + 38之間的浮點(diǎn)數字數據。
4字節.
在 SQL Server 中，real的同義詞為 float(24)。
占用空間大?。?div style="height:15px;">
n 所在范圍
精度
存儲大小
1-24
7 位數
4 字節
25-53
15 位數
8 字節
1.4.3. 兩種浮點(diǎn)類(lèi)型之間的比較
精確浮點(diǎn)類(lèi)型
近似浮點(diǎn)類(lèi)型
表示方式
每一個(gè)十進(jìn)制位都表示一個(gè)精確的數字，不需要近似
當需要表示的數字超過(guò)限制后，會(huì )對尾數進(jìn)行四舍五入
精度
完全精確
在一定范圍內可以精確標識，超出范圍后會(huì )發(fā)生舍入
表示范圍
最多只有38位
可以表示數量級為10^302數量級的數據
1.5. 日期和時(shí)間
1.5.1. 概述
用來(lái)保存日期和時(shí)間，有datetime和smalldatetime兩種。
1.5.2. 詳細描述
數據類(lèi)型
取值范圍
描述
備注
datetime
從 1753 年 1月 1 日到 9999年 12 月 31日的日期和時(shí)間數據. 精確度為百分之三秒（等于 3.33毫秒或 0.00333 秒）。
兩個(gè)4字節組成datetime的值，其中4字節保存日期值，另外4個(gè)字節保存時(shí)間值。
一個(gè)datetime占用8個(gè)字節
Microsoft? SQL Server?拒絕所有其不能識別為 1753年到 9999 年間的日期的值。
smalldatetime
從 1900 年 1月 1 日到 2079年 6 月 6日的日期和時(shí)間數據精確到分鐘。29.998 秒或更低的 smalldatetime值向下舍入為最接近的分鐘，29.999 秒或更高的 smalldatetime值向上舍入為最接近的分鐘。
兩個(gè)2字節組成smalldatetime的值，其中2個(gè)字節保存日期值，2個(gè)字節保存時(shí)間值。
一個(gè)smalldatetime占用4個(gè)字節。
1.5.3. 示例
時(shí)間日期類(lèi)型舍入示例：
-- 返回 12:35
SELECT CAST('2000-05-08 12:35:29.998' AS smalldatetime)
GO
-- 返回 12:36
SELECT CAST('2000-05-08 12:35:29.999' AS smalldatetime)
GO
-- 1998-01-02 00:00:00.000
select cast('1998-1-1 23:59:59.999' as datetime)
-- 1998-01-01 23:59:59.997
select cast('1998-1-1 23:59:59.995' as datetime)
select cast('1998-1-1 23:59:59.996' as datetime)
select cast('1998-1-1 23:59:59.997' as datetime)
select cast('1998-1-1 23:59:59.998' as datetime)
-- 1998-01-01 23:59:59.993
select cast('1998-1-1 23:59:59.992' as datetime)
select cast('1998-1-1 23:59:59.993' as datetime)
select cast('1998-1-1 23:59:59.994' as datetime)
-- 1998-01-01 23:59:59.990
select cast('1998-1-1 23:59:59.990' as datetime)
select cast('1998-1-1 23:59:59.991' as datetime)
1.5.4. 小結
一般情況下使用datetime值，因為smalldatetime精度差而且數據范圍小，而且只能保存2076年以前的數據。
1.6. 字符串
1.6.1. 概述
在SQL Server 2000中，字符串分為兩種，一種是ASCII字符串，另一種是Unicode字符串。
其中ASCII字符串使用三種數據類(lèi)型表示，分別是char, varchar, text三種類(lèi)型，而Unicode字符串使用nchar, nvarchar, ntext三種類(lèi)型表示。
1.6.2. 詳細描述
數據類(lèi)型
描述
char[(n)]
長(cháng)度為 n 個(gè)字節的固定長(cháng)度且非 Unicode的字符數據。n 必須是一個(gè)介于 1和 8,000 之間的數值。存儲大小為 n個(gè)字節。
在定義數據時(shí)如果沒(méi)有指定n，則n默認為1
在cast時(shí)如果沒(méi)有指定n，則n默認為30
varchar[(n)]
長(cháng)度為 n 個(gè)字節的可變長(cháng)度且非 Unicode的字符數據。n 必須是一個(gè)介于 1和 8,000 之間的數值。存儲大小為輸入數據的字節的實(shí)際長(cháng)度，而不是 n個(gè)字節。所輸入的數據字符長(cháng)度可以為零。
在定義數據時(shí)如果沒(méi)有指定n，則n默認為1
在cast時(shí)如果沒(méi)有指定n，則n默認為30
text
服務(wù)器代碼頁(yè)中的可變長(cháng)度非 Unicode 數據的最大長(cháng)度為 231-1 (2,147,483,647)個(gè)字符。當服務(wù)器代碼頁(yè)使用雙字節字符時(shí)，存儲量仍是 2,147,483,647字節。存儲大小可能小于 2,147,483,647 字節（取決于字符串）。
nchar
包含 n 個(gè)字符的固定長(cháng)度 Unicode字符數據。n 的值必須介于 1與 4,000 之間。存儲大小為 n字節的兩倍。
在定義數據時(shí)如果沒(méi)有指定n，則n默認為1
在cast時(shí)如果沒(méi)有指定n，則n默認為30
nvarchar
包含 n 個(gè)字符的可變長(cháng)度 Unicode字符數據。n 的值必須介于 1與 4,000 之間。字節的存儲大小是所輸入字符個(gè)數的兩倍。所輸入的數據字符長(cháng)度可以為零。
在定義數據時(shí)如果沒(méi)有指定n，則n默認為1
在cast時(shí)如果沒(méi)有指定n，則n默認為30
ntext
可變長(cháng)度 Unicode 數據的最大長(cháng)度為 230 - 1 (1,073,741,823)個(gè)字符。存儲大小是所輸入字符個(gè)數的兩倍（以字節為單位）。
1.6.3. 小結
在實(shí)際使用中ASCII字符串可以根據當前字符串中的每一個(gè)字符是否能用一個(gè)字節表示來(lái)決定實(shí)際使用的字節數，而Unicode字符串不判斷輸入字符串中的每一個(gè)字節，將所有字符都按照兩個(gè)字節處理。
1.6.4. Q&A
1. 什么時(shí)候使用變長(cháng)字符串，什么時(shí)候使用定長(cháng)字符串？
當存儲數據長(cháng)度相差很大時(shí)，應該使用變長(cháng)的字符，否則應該使用定長(cháng)數據。如果需要在字符列中定義索引，最好使用定長(cháng)數據，因為定長(cháng)數據的索引效率要高于變長(cháng)數據。
2. 為什么需要text和ntext數據類(lèi)型？為什么char, varchar, nchar，nvarchar會(huì )有8000個(gè)字節的限制？
在SQL Server 2000中，所有數據是保存在數據頁(yè)中的，每一個(gè)數據頁(yè)的大小是8K，每一個(gè)數據必須保存表中的至少一行數據，每一行的數據不能跨頁(yè)保存，所以char, varchar, nchar，nvarchar四種字符串的最大長(cháng)度都小于8K字節。如果需要保存8K以上的內容，就需要將這部分的字符串分離到數據頁(yè)之外獨立保存，于是text和ntext數據類(lèi)型就誕生了。如果在SQL Server 2000 中定義text或ntext類(lèi)型數據，插入時(shí)默認將數據保存到一個(gè)文本頁(yè)中，這個(gè)頁(yè)里面專(zhuān)門(mén)用來(lái)存儲ntext, text和image類(lèi)型數據，在保存行的數據頁(yè)中只保留一個(gè)指針，用來(lái)指向實(shí)際的數據。
3. 對于JDBC來(lái)說(shuō)text以及ntext類(lèi)型在使用上有什么區別？
對JDBC的使用者來(lái)說(shuō)，text和ntext與其他字符串類(lèi)型沒(méi)有任何區別，對于JDBC的開(kāi)發(fā)者來(lái)說(shuō)，需要考慮數據緩沖區和分塊傳輸的情況。
1.7. 二進(jìn)制類(lèi)型
1.7.1. 概述
SQL Server 2000中可以定義專(zhuān)門(mén)用來(lái)保存二進(jìn)制數據的數據類(lèi)型，分為三種：binary和varbinary，image。
1.7.2. 詳細描述
數據類(lèi)型
描述
binary[(n)]
固定長(cháng)度的 n 個(gè)字節二進(jìn)制數據。N 必須從 1 到 8,000。存儲空間大小為 n+4 字節。
varbinary[(n)]
n 個(gè)字節變長(cháng)二進(jìn)制數據。n 必須從 1 到 8,000。存儲空間大小為實(shí)際輸入數據長(cháng)度 +4 個(gè)字節，而不是 n 個(gè)字節。輸入的數據長(cháng)度可能為 0 字節。在 SQL-92 中 varbinary 的同義詞為 binary varying。
image
可變長(cháng)度二進(jìn)制數據介于 0 與 231-1 (2,147,483,647) 字節之間。
1.7.3. 小結
1. 從二進(jìn)制類(lèi)型占用空間可以看出，二進(jìn)制類(lèi)型在SQL Server 2000的頁(yè)文件中分為兩部分保存，有4個(gè)字節保存數據長(cháng)度，剩余部分是數據內容。
2. 如果定義數據時(shí)沒(méi)有指定n，默認為1；cast時(shí)沒(méi)有指定n，默認為30。image的存儲方式和text，ntext類(lèi)型一致。
1.7.4. Q&A
1. 如何使用JDBC訪(fǎng)問(wèn)binary，varbinary以及image類(lèi)型的數據?
和其他類(lèi)型完全一致。
1.8. 其他數據類(lèi)型
除了以上描述的幾種數據類(lèi)型之外，SQL Server 2000中還有幾種特殊的數據類(lèi)型。
1.8.1. cursor：游標類(lèi)型
游標類(lèi)型，可以定義為變量或存儲過(guò)程的OUTPUT參數，但不能定義為表或視圖中的列。
使用游標需要經(jīng)過(guò)定義，打開(kāi)，使用，關(guān)閉，銷(xiāo)毀五個(gè)過(guò)程，對于同一個(gè)SQL Server連接來(lái)說(shuō)，游標屬于全局資源，如果定義后不銷(xiāo)毀或沒(méi)關(guān)閉，容易造成內存泄漏。
-- 定義游標
declare hiRuleName_cursor cursor for
select hiRuleName from T_HI_RULE
-- 打開(kāi)游標
open hiRuleName_cursor
fetch next from hiRuleName_cursor
declare @hiRuleName varchar(32)
fetch next from hiRuleName_cursor into @hiRuleName
select @hiRuleName
-- 關(guān)閉游標
close hiRuleName_cursor
-- 刪除游標
deallocate hiRuleName_cursor
1.8.2. sql_variant：變體類(lèi)型
可以用來(lái)保存SQL Server中的任何類(lèi)型數據（text、ntext、image、timestamp和 sql_variant 除外）?？梢杂迷诒砘蛞晥D的列，局部變量以及存儲過(guò)程或函數中。
在使用時(shí)需要首先轉換成相應的類(lèi)型，類(lèi)似Java中的Object類(lèi)。
在SQL server 2000中使用sql_varaint類(lèi)型：
-- 創(chuàng )建臨時(shí)表
create table #t(
value sql_variant
)
-- 在表中插入不同類(lèi)型的數據
insert into #t(value) values(123)
insert into #t(value) values(1.5)
insert into #t(value) values('feawfe')
-- 查詢(xún)
select * from #t
-- 將其中任意一行修改為另一種數據類(lèi)型
update #t set value='111' where value=123
-- 查詢(xún)
select * from #t
-- 刪除表
drop table #t
在JDBC中如何使用sql_variant類(lèi)型？
和其他類(lèi)型完全一致。
1.8.3. table：表格類(lèi)型
以表格的方式保存計算結果便于后續處理。
使用表格的數據類(lèi)型不但可以返回二維數組，而且返回值也可以象一張真實(shí)的表一樣擁有列類(lèi)型，主鍵，唯一性約束，CHECK約束，默認值等。
table類(lèi)型和臨時(shí)表相比，有以下幾個(gè)優(yōu)點(diǎn)：
l table變量是局部變量，有明確的作用域，超過(guò)作用域變量自動(dòng)清除。
l 在存儲過(guò)程中使用table變量，不需要重新編譯。
l 涉及table變量更新的事務(wù)只在table變量更新期間存在，減少table變量對鎖定和記錄資源的需求。
使用table類(lèi)型局部變量：
declare @tb table(
c1 bigint primary key , -- 定義主鍵
c2 varchar(32) not null unique, --定義唯一非空列
c3 int check(c3 < 5 and c3 > 0) --定義check約束
)
-- 插入時(shí)沒(méi)有違反約束
insert into @tb(c1, c2, c3) values(1, '123', 3)
select * from @tb
-- 違反主鍵唯一性約束
insert into @tb(c1, c2, c3) values(1, '123', 3)
-- 違反c2非空約束
insert into @tb(c1, c2, c3) values(2, null, 3)
-- 違反c3 check約束
insert into @tb(c1, c2, c3) values(2, '123', 8)
1.8.4. timestamp：時(shí)間戳
自動(dòng)生成的二進(jìn)制數，確保這些數據在數據庫中是唯一的。占用8個(gè)字節。
時(shí)間戳用于表示 SQL Server 在一行上的活動(dòng)順序，按二進(jìn)制格式以遞增的數字來(lái)表示。當表中的行發(fā)生變動(dòng)時(shí)，用從 @@DBTS函數獲得的當前數據庫的時(shí)間戮值來(lái)更新時(shí)間戮。timestamp 數據與插入或修改數據的日期和時(shí)間無(wú)關(guān)。若要自動(dòng)記錄表中數據更改的時(shí)間，使用 datetime或 smalldatetime 數據類(lèi)型記錄事件或觸發(fā)器。
創(chuàng )建表時(shí)可以不為timestamp類(lèi)型指定列名，SQL Server 2000會(huì )自動(dòng)生成一個(gè)列名。
一個(gè)表只能有一個(gè)timestamp列，每次插入或更新包含timestamp列的行時(shí)，時(shí)間戳列會(huì )自動(dòng)更新。
-- 創(chuàng )建臨時(shí)表
create table #t(
value varchar(32),
timestamp
)
-- 查看當前時(shí)間戳
select @@DBTS
insert into #t(value) values('aaa')
insert into #t(value) values('bbb')
insert into #t(value) values('ccc')
select * from #t
-- 更新
update #t set value='bbb'
select * from #t
drop table #t
1.8.5. uniqueidentifier：（全局統一標識符）
一個(gè)36字節的十六進(jìn)制字符串，可以作為表的主鍵。
一般使用newid()方法隨機生成。
2. 自定義數據類(lèi)型
2.1. 概述
在SQL Server 2000 中允許用戶(hù)自定義數據類(lèi)型，定義自定義數據類(lèi)型時(shí)，需要提供以下三個(gè)參數：
l 名稱(chēng)
l 數據類(lèi)型
l 為空性
如果用戶(hù)自定義數據類(lèi)型在module數據庫中創(chuàng )建，將作為全局的數據類(lèi)型出現，在其他的所有數據庫中都可見(jiàn)，如果在其他數據庫中定義，只在相應的數據庫中看到。
2.2. 詳細描述
創(chuàng )建用戶(hù)自定義數據類(lèi)型，使用系統存儲過(guò)程sp_addtype，刪除用戶(hù)自定義數據類(lèi)型，使用系統存儲過(guò)程sp_droptype。
SQL Server 2000 中大多數數據類(lèi)型都可以用來(lái)創(chuàng )建新的數據類(lèi)型（除了cursor，table，timestamp之外)。
2.3. 示例
EXEC sp_addtype telephone, 'varchar(32)', 'NOT NULL'
exec sp_droptype telephone
2.4. 小結
使用自定義數據類(lèi)型，可以在開(kāi)發(fā)的初期形成一套開(kāi)發(fā)規范，所有人都使用自定義數據類(lèi)型進(jìn)行數據庫定義，不容易出現名稱(chēng)類(lèi)型或長(cháng)度不一致的情況。
3. 數據類(lèi)型之間的轉換
3.1. 概述
數據類(lèi)型的轉換分為隱式轉換和顯示轉換兩種。
l 隱式轉換不可見(jiàn)，系統自動(dòng)進(jìn)行。
l 顯示轉換使用cast或convert函數進(jìn)行。
3.2. cast和convert函數
兩個(gè)函數都是用來(lái)顯示轉換數據類(lèi)型的，其中cast是遵循SQL－92標準，大多數時(shí)候已經(jīng)足夠，convert函數在日期轉換時(shí)有用，可以指定日期格式。
