详解SQL Server中的数据类型

前言

前面几篇文章我们讲解了索引有关知识，这一节我们再继续我们下面内容讲解，简短的内容，深入的理解。

数据类型

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)   )

此时我们将firstname，插入五个中文试试如下：

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

也就是说在常规字符类型如上述VARVHAR中定义为五个字符，此时我们插入五个中文字符则会被截取，当然也插入不进去。因为上述已经明确讲了1个非英语字符串相当于两个字节，此时中文所占用的是十个字节，而此时VARCHAR才五个字符，所以出现警告。我们再来将firstname插入两个中文两个英文或者数字看看

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

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

我们上述分析的不是有理有据么，难道这里英文不是占用一个字节么，我们插入一个英文试试。

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

常规字符和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   )

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

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

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

结论：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'

结论：LEN和DATALENGTH对于NULL计算的结果就是NULL。

我们再来看看二者差异的一个小地方：

SELECT LEN('JeffckyWang ') AS 'LEN'SELECT DATALENGTH('JeffckyWang ') AS '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;

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

总结

本节我们主要讲解了SQL中的数据类型以及几个需要注意的地方，简短的内容，深入的理解，我们下节再会。

更多相关文章

随机推荐