詳解SQL Server中的數據類型

2024-08-31 01:04:23

字體：大中小

來源：轉載

供稿：網友

前言

前面幾篇文章我們講解了索引有關知識，這一節我們再繼續我們下面內容講解，簡短的內容，深入的理解。

數據類型

SQL Server支持兩種字符數據類型，一種是常規，另外一種則是Unicode。常規數據類型包括CHAR和VARCHAR，Unicode數據類型包括NCAHR和NVARCHAR。常規字符的每個字符使用1個字節存儲，而Unicode數據的每個字符要求2個字節。常規字符列限制為僅僅只針對于英語，而Unicode則是針對于多種語言。兩種字符數據類型的文本表示方式也不相同，在表示常規字符文本時，只需要使用單引號，比如'Hello,my name is JeffckyWang,I'm from cnblogs'，而對于Unicode字符文本時，需要指定字符N作為前綴，即N‘Hello,my name is JeffckyWang,I'm from cnblogs'。

名稱中沒有VAR元素的任何數據類型（CHAR、NCHAR）具有固定長度，即SQL Server按照列定義大小保留行空間，而不是按照字符中的實際字符保留空間。比如某列定義大小為CHAR(25)，則SQL Server在該行保留25個字符的空間，而不管存儲字符串的長度。

名稱中含有VAR元素的數據類型（VARCHAR、NVARCHAR）具有可變長度，即SQL Server根據存儲需要，在行中使用盡可能多的存儲空間存儲字符串，同時外加兩個額外的字節偏移數據。例如，如果將某列定義為VARCHAR(25)，此時支持的最大字符數為25，但實際上按照字符串中實際字符確定存儲量。-摘抄自SQL Server 2012 T-SQL基礎教程。

這里關于Unicode字符數據類型我們需要重點理解下。我們先創建一個表，如下：

CREATE TABLE UnicodeType( firstname VARCHAR(5) NOT NULL, lastname NVARCHAR(5) NOT NULL);

此時我們手動插入數據，正常插入，如下：

INSERT dbo.UnicodeType  ( firstname, lastname )VALUES ( '11111', -- firstname - varchar(5)   N'啊的發個好' -- lastname - nvarchar(5)   )

字符都完全插入表中，如下：

sqlserver,數據類型

此時我們將firstname，插入五個中文試試如下：

INSERT dbo.UnicodeType  ( firstname, lastname )VALUES ( '達得到讓人', -- firstname - varchar(5)   N'達得到讓人' -- lastname - nvarchar(5)   )

此時出現如下結果：

sqlserver,數據類型

也就是說在常規字符類型如上述VARVHAR中定義為五個字符，此時我們插入五個中文字符則會被截取，當然也插入不進去。因為上述已經明確講了1個非英語字符串相當于兩個字節，此時中文所占用的是十個字節，而此時VARCHAR才五個字符，所以出現警告。我們再來將firstname插入兩個中文兩個英文或者數字看看

INSERT dbo.UnicodeType  ( firstname, lastname )VALUES ( '達得1', -- firstname - varchar(5)   N'達得到讓人' -- lastname - nvarchar(5)   )

此時插入進去為出現警告，因為此時兩個中文字符即四個字節加上一個數字字節剛好五個字節，所以能正常插入，我們再來看看lastname，由上知，既然英文或者數字被當做一個字節，那么我們對lastname插入四個中文字符和兩個英文字節剛好十個字節應該是好使的。我們看看：

INSERT dbo.UnicodeType  ( firstname, lastname )VALUES ( '達得1', -- firstname - varchar(5)   N'達得到讓ab' -- lastname - nvarchar(5)   )

oh，shit，此時居然出錯了，如下：

sqlserver,數據類型

我們上述分析的不是有理有據么，難道這里英文不是占用一個字節么，我們插入一個英文試試。

INSERT dbo.UnicodeType  ( firstname, lastname )VALUES ( '達得1', -- firstname - varchar(5)   N'達得到讓b' -- lastname - nvarchar(5)   )

結果正確了，實踐是檢驗真理的唯一標準，從這里我們可以看出：在常規字符中，一個中文會當做是兩個字節來使用，一個英文會當做是一個字節使用，但是在Unicode中，一個中文也是會當做兩個字節來使用，但是一個英文也會當做是兩個字節來使用。至此我們可以得出結論，個人一直以為在Unicode中，將英文是作為一個字節存儲，見識短啊。

常規字符和Unicode中一個中文字符用兩個字節存儲，而對英文，常規字符用一個字節存儲，而Unicode依然是用兩個字節存儲。

字符串函數

對字符串操作的函數有SUBSTRING、LEFT、RIGHT、CHARINDEX、PATINDEX、REPLACE、REPICATE、STUFF、UPPER、LOWER、RTRIM、LTRIM、FORMAT。對于簡單的函數我們略過，下面我們來講講幾個需要注意的地方。

LEN與DATALENGTH比較

我們首先創建如下測試表

CREATE TABLE StringFun( firststr VARCHAR(max) NOT NULL, secondstr TEXT NOT NULL);

我們插入測試數據

INSERT dbo.StringFun  ( firststr, secondstr )VALUES ( '我是JeffckyWang，我來自于博客園，專注于.NET技術', -- firststr - varchar(max)   '我是JeffckyWang，我來自于博客園，專注于.NET技術' -- secondstr - text   )

我們首先利用LEN函數來返回firststr和secondstr的字符串長度大小

SELECT LEN(firststr) AS VARCAHRFieldSize FROM dbo.StringFunSELECT LEN(secondstr) AS TEXTFieldSize FROM dbo.StringFun

sqlserver,數據類型

好極了，出錯了。LEN函數無法對TEXT進行操作。我們接著往下看。

SELECT DATALENGTH(firststr) AS VARCAHRFieldSize FROM dbo.StringFunSELECT DATALENGTH(secondstr) AS TEXTFieldSize FROM dbo.StringFun

sqlserver,數據類型

此時未報錯誤，結果顯示為47個字節大小。既然LEN對文本無效，我們不對文本操作就是。

SELECT LEN(firststr) AS VARCAHRFieldSize FROM dbo.StringFunSELECT DATALENGTH(secondstr) AS TEXTFieldSize FROM dbo.StringFun

sqlserver,數據類型

此時類型為VARCAHR的firststr字節大小卻為31，為何，看到這里我們想必恍然大悟，在上述我們講到常規字符會對中文以一個字符兩個字節大小存儲，但是這里實際上返回的是實際字符大小，當然一個是存儲，一個是檢索，還是有點不同，同時我們也不會將中文存儲到VARCHAR中。到這里我們可以得出結論。

結論：DATALENGTH函數是針對于TEXT，而LEN是針對于VARCHAR，對TEXT無效會報錯。

到這里我們還有一個特殊值未進行處理，那就是NULL。那么問題來了，LEN和DATALENGTH對NULL，它的長度大小是多少呢，是0還是不是0尼？

是我們來測試下：

DECLARE @MyVar VARCHAR(10)SET @MyVar = NULLIF (LEN(@MyVar) = 0)PRINT 'LEN of NULL is 0'ELSEPRINT 'LEN of NULL is NULL'

sqlserver,數據類型

我們上述得到的結果是LEN of NULL is NULL，DATALENGTH就不再演示了。

結論：LEN和DATALENGTH對于NULL計算的結果就是NULL。

我們再來看看二者差異的一個小地方：

SELECT LEN('JeffckyWang ') AS 'LEN'SELECT DATALENGTH('JeffckyWang ') AS 'DATALENGTH'

sqlserver,數據類型

結論：LEN會刪除尾隨空格，而DATALENGTH不會

CHARINDEX與PATINDEX比較

CHARINDEX和PATINDEX字符串函數都是查詢返回指定匹配字符串的開始位置。

我們先查詢一個字符串，此字符串在表中存在，如下：

USE AdventureWorks2012;GOSELECT CHARINDEX('Worn', DocumentSummary) AS 'CHARINDEX'FROM Production.DocumentWHERE ChangeNumber = 55;GOSELECT PATINDEX('Worn', DocumentSummary) AS 'PATINDEX'FROM Production.DocumentWHERE ChangeNumber = 55;

sqlserver,數據類型

為何CHARINDEX函數查找到了，而PATINDEX沒有查詢到呢？此時就說說二者的區別，二者都有兩個參數，第二個參數都是要匹配的字符串，但是PATINDEX函數必須在需要匹配的字符串之前或者之后添加百分號即通配符，而CHARINDEX函數則不需要。如下即可：

USE AdventureWorks2012;GOSELECT CHARINDEX('Worn', DocumentSummary) AS 'CHARINDEX'FROM Production.DocumentWHERE ChangeNumber = 55;GOSELECT PATINDEX('%Worn%', DocumentSummary) AS 'PATINDEX'FROM Production.DocumentWHERE ChangeNumber = 55;

sqlserver,數據類型