SQL SERVER的数据类型
1.SQL SERVER的数据类型
数据类型是数据的一种属性，表示数据所表示信息的类型。任何一种计算机语言都定义了自己的数据类型。当然，不同的程序语言都具有不同的特点，所定义的数据类型的各类和名称都或多或少有些不同。SQLServer 提供了 25 种数据类型：
·Binary [(n)]
·Varbinary [(n)]
·Char [(n)]
·Varchar[(n)]
·Nchar[(n)]
·Nvarchar[(n)]
·Datetime
·Smalldatetime
·Decimal[(p[,s])]
·Numeric[(p[,s])]
·Float[(n)]
·Real
·Int
·Smallint
·Tinyint
·Money
·Smallmoney
·Bit
·Cursor
·Sysname
·Timestamp
·Uniqueidentifier
·Text
·Image
·Ntext
以下为SQL SERVER7.0以上版本的字段类型说明。SQL SERVER6.5的字段类型说明请参考SQL SERVER提供的说明。
|
字段类型 |
描述 |
|
bit |
0或1的整型数字 |
|
int |
从-2^31(-2,147,483,648)到2^31(2,147,483,647)的整型数字 |
|
smallint |
从-2^15(-32,768)到2^15(32,767)的整型数字 |
|
tinyint |
从0到255的整型数字 |
|
|
|
|
decimal |
从-10^38到10^38-1的定精度与有效位数的数字 |
|
numeric |
decimal的同义词 |
|
|
|
|
money |
从-2^63(-922,337,203,685,477.5808)到2^63-1(922,337,203,685,477.5807)的货币数据，最小货币单位千分之十 |
|
smallmoney |
从-214,748.3648到214,748.3647的货币数据，最小货币单位千分之十 |
|
|
|
|
float |
从-1.79E+308到1.79E+308可变精度的数字 |
|
real |
从-3.04E+38到3.04E+38可变精度的数字 |
|
|
|
|
datetime |
从1753年1月1日到9999年12日31的日期和时间数据，最小时间单位为百分之三秒或3.33毫秒 |
|
smalldatetime |
从1900年1月1日到2079年6月6日的日期和时间数据，最小时间单位为分钟 |
|
|
|
|
timestamp |
时间戳，一个数据库宽度的唯一数字 |
|
uniqueidentifier |
全球唯一标识符GUID |
|
|
|
|
char |
定长非Unicode的字符型数据，最大长度为8000 |
|
varchar |
变长非Unicode的字符型数据，最大长度为8000 |
|
text |
变长非Unicode的字符型数据，最大长度为2^31-1(2G) |
|
|
|
|
nchar |
定长Unicode的字符型数据，最大长度为8000 |
|
nvarchar |
变长Unicode的字符型数据，最大长度为8000 |
|
ntext |
变长Unicode的字符型数据，最大长度为2^31-1(2G) |
|
|
|
|
binary |
定长二进制数据，最大长度为8000 |
|
varbinary |
变长二进制数据，最大长度为8000 |
|
image |
变长二进制数据，最大长度为2^31-1(2G) |
2. 字符数据类型
字符数据的类型包括 Char，Nchar,Varchar，Nvarchar 和 Text。字符数据是由任何字母、符号和数字任意组合而成的数据。
Varchar 是变长字符数据，其长度不超过 8KB。存储效率（存储数据的快慢）没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么“+1”呢？这一个字节用于保存实际使用了多大的长度。
Char 是定长字符数据，其长度最多为 8KB。Char(n): 系统分配n个字节给此字段，不管字段实际长度(后边用空格补齐) 。CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间。
超过 8KB 的ASCII 数据可以使用Text数据类型存储。例如，因为 Html 文档全部都是 ASCII 字符，并且在一般情况下长度超过 8KB，所以这些文档可以 Text 数据类型存储在SQL Server 中。 现在Text被淘汰。
1）到底选char、nchar、varchar、nvarchar、text、ntext中哪一种呢？
结果很可能是两种，一种是节俭人士的选择：最好是用定长的，感觉比变长能省些空间，而且处理起来会快些，无法定长只好选用定长，并且将长度设置尽可能地小；另一种是则是觉得无所谓，尽量用可变类型的，长度尽量放大些。 鉴于现在硬件像萝卜一样便宜的大好形势，纠缠这样的小问题实在是没多大意义，不过如果不弄清它，总觉得对不起劳累过度的CPU和硬盘。
2） nvarchar 和 varchar 的区别
nvarchar 和 varchar 的区别是存储方式不同
varchar在SQL Server中是采用单字节来存储数据的，nvarchar是使用Unicode来存储数据的。中文字符存储到SQL Server中会保存为两个字节（一般采用Unicode编码），英文字符保存到数据库中，如果字段的类型为varchar，则只会占用一个字节，而如果字段的类型为nvarchar，则会占用两个字节。 varchar(40),能存储40个字节长度的字符,存储中文字符的时候,因为中文字符1个字符就等于2个字节，所以varchar(40)只能存储20个中文字符。nvarchar(40),就可以存储40个中文字符,也就是说可以存储80个字节长度的字符。nvarchar要相对于存储的字符类型.
正常情况下，我们使用varchar也可以存储中文字符，但是如果遇到操作系统是英文操作系统并且对中文字体的支持不全面时, 在SQL Server存储中文字符为varchar就会出现乱码(显示为??)．而且正常情况下，主机都会支持中文的环境，所以如果使用varchar来存储数据，在开发阶段是发现不了的．多数情况下，在布署的时候也不会有问题．但是！如果布署的主机是英文操作系统，并且不支持中文环境，那问题就出来了．所有的varchar字段在存储中文的时候都会变成乱码（显示为??）．而且一般情况下你不会知道这是因为你采用了错误的数据类型来存储所造成的，你会试着去装中文字体，试着去设置操作系统的语言环境．．．这些都不能解决问题，唯一能解决问题的是把数据库字段的类型个性为nvarchar（或者nchar）．对项目管理比较熟悉的朋友应该都知道，到布署阶段再来修改数据库是一个很恐怖的事情．
使用nvarchar的另一个非常好处就是在判断字符串的时候可以不需要考虑中英文两种字符的差别．
当然，使用nvarchar存储英文字符会增大一倍的存储空间．但是在存储代价已经很低廉的情况下，优先考虑兼容性会给你带来更多好处的．
所以在Design的时候应该尽量使用nvarchar来存储数据．只有在你确保该字段不会保存中文的时候，才采用varchar来存储．
3）SQL Server中TEXT和VARCHAR(MAX)数据类型的区别
以前只知道text和image是可能被SQL Server淘汰的数据类型，但具体原因不太清楚，今天读书的时候发现了text与与varchar(max)和nvarchar(max)的区别，主要是对操作符的限制，text只能被下列函数作用：
|
函数 |
语句 |
|
DATALENGTH |
READTEXT |
|
PATINDEX |
SET TEXTSIZE |
|
SUBSTRING |
UPDATETEXT |
|
TEXTPTR |
WRITETEXT |
|
TEXTVALID |
举个列子，如果“文本”这一列的数据类型为text，那么它将不能用于“=”“left()”等操作
text在以后的版本将被删除，用varchar(max)，可以保证良好的向后兼容，这也正是推荐使用的原因。与此类似的，有：nvarchar(max)代替ntext，varbinary(max) 代替image。
使用varchar(max)还有一个很棒的地方，可以定义长度超过8000字节的变量，从此不用再为旧版本这个限制而苦恼了。
附：SQL Server 2005 联机丛书 中文相关说明
1、在 Microsoft SQL Server 的未来版本中将删除 ntext、text 和 image 数据类型。请避免在新开发工作中使用这些数据类型，并考虑修改当前使用这些数据类型的应用程序。请改用 nvarchar(max)、varchar(max) 和 varbinary(max)。有关详细信息，请参阅 使用大值数据类型。
2、当 sp_tableoption 存储过程的 'large value types out of row' 选项设置为 OFF 时，大值类型的行内存储限制为8000 个字节。当此选项设置为 ON 时，只在行内存储 16 字节的根。有关详细信息，请参阅 sp_tableoption (Transact-SQL)。
3、请使用 varchar(max)、nvarchar(max) 和 varbinary(max) 数据类型，而不要使用 text、ntext 和 image 数据类型。
4）如何设置字段的长度查询最快又节省空间？ ——char、varchar、text
从空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点。
1.char 数据类型使用固定长度来存储字符，最长可以容纳8000个字节。利用char数据类型来定义表列或定义变量时，应该给定数据的最大长度。如果实际的字符长度短于给定的最大长充，刚多的字节会被空格填充。如果实际的多了，则被截断。(好处：可以精确计算数据占有的空间)
2.varchar是最长可以达到8000字节的变长字符型数据。它随存储在表列中的每一个数据的字符数的不同而变化。例如，定义表列为varchar(20)，那么存储在该列的数据最多可以长达20个字节，如没达到20个字节，并不会在多余的字节上填充空格。所以大部分时候都选择varchar，可以有效的节省空间，不浪费。
3.text不常用，因为它是存储非常庞大的字符型数据的类型。当大于8000字节时，可以用text，它最大长度可以达到2的31次方减1个字符，约2G。 强调：text也是变长字符数据。
个人看法：
1.占用空间上varchar（100）和varchar（2000）没什么区别。 varchar(100)中的100并不多余，在未存储数据时用于占位，系统会用于预先计划分配空间，但直到真正存储数据时才确实分配存储空间。 varchar的存放和string是一样原理的，即length {block}这种方式，所以varchar的长度和它实际占用空间是无关的。
2.但varchar(100)会效率较低，因为按你说的该字段会5-2000，若大于100，则您每次固定写入100会需要多次写操作，众所周知写操作是比较耗时的。
3.查询性能方面，跟这儿怎么存没太大关系，重要的还是常见的数据库查询优化，如索引、条件等等
总结陈词：
1、如果数据量非常大，又能100%确定长度且保存只是ansi字符，那么char
2、能确定长度又不一定是ansi字符或者，那么用nchar；
3、不确定长度，要查询且希望利用索引的话，用nvarchar类型吧，将它们设到400；
4、不查询的话没什么好说的，用nvarchar(4000)
5、性格豪爽的可以只用3和4，偶尔用用1，毕竟这是一种额外说明，等于告诉别人说，我一定需要长度为X位的数据
3. 数字数据类型
数字数据只包含数字。数字数据类型包括正数和负数、小数（浮点数）和整数
整数由正整数和负整数组成，例如 39、25、0-2 和 33967。在 Micrsoft SQL Server 中，整数存储的数据类型是Int，Smallint和 Tinyint。Int 数据类型存储数据的范围大于 Smallint 数据类型存储数据的范围，而 Smallint 据类型存储数据的范围大于Tinyint 数据类型存储数据的范围。使用Int 数据狗昔存储数据的范围是从 -2 147 483 648 到 2 147 483 647（每一个值要求4个字节存储空间）。使用 Smallint 数据类型时，存储数据的范围从 -32 768 到 32 767（每一个值要求2个字节存储空间）。使用Tinyint 数据类型时，存储数据的范围是从0 到255（每一个值要求1个字节存储空间）。
精确小娄数据在 SQL Server 中的数据类型是 Decimal 和 Numeric。这种数据所占的存储空间根据该数据的位数后的位数来确定。
在SQL Server 中，近似小数数据的数据类型是 Float 和 Real。例如，三分之一这个分数记作。3333333，当使用近似数据类型时能准确表示。因此，从系统中检索到的数据可能与存储在该列中数据不完全一样。
4. 日期和时间数据类型
日期和时间数据类型包括 Datetime 和 Smalldatetime两种类型
日期和时间数据类型由有效的日期和时间组成。例如，有效的日期和时间数据包括“4/01/98 12:15:00:00:00 PM”和“1:28:29:15:01AM 8/17/98”。前一个数据类型是日期在前，时间在后一个数据类型，后一个是是霎时间在前，日期在后。在 Microsoft SQL Server中，日期和时间数据类型包括Datetime 和 Smalldatetime 两种类型，使用 Datetime数据类型时，所存储的日期范围是从 1753年 1 月 1 日开始，到9999 年12 月 31 日结束(每一个值要求 8 个存储字节)。使用 Smalldatetime数据类型时，所存储的日期范围是 1900年 1 月 1日开始，到 2079 年 12 月 31 日结束(每一个值要求 4 个存储字节)。
日期的格式可以设定。设置日期格式的命令如下：
Set DateFormat {format | @format _var|
其中，format | @format_var 是日期的顺序。有效的参数包括 MDY、DMY、YMD、YDM、MYD 和DYM。在默认情况下，日期格式为MDY。
例如，当执行 Set DateFormat YMD 之后，日期的格式为年 月 日 形式；当执行 Set DateFormat DMY 之后，日期的格式为日 月有年 形式
5. 货币数据表示正的或者负的货币数量
在 Microsoft SQL Server 中，货币数据的数据类型是Money 和 Smallmoney
Money数据类型要求 8 个存储字节，Smallmoney 数据类型要求 4 个存储字节。
6. 特殊数据类型
特殊数据类型包括前面没有提过的数据类型。特殊的数据类型有3种，即 Timestamp、Bit和 Uniqueidentifier。
Timestamp 用于表示SQL Server 活动的先后顺序，以二进投影的格式表示。Timestamp 数据与插入数据或者日期和时间没有关系。
Bit 由 1 或者 0 组成。当表示真或者假、ON 或者 OFF 时，使用 Bit 数据类型。例如，询问是否是每一次访问的客户机请求可以存储在这种数据类型的列中。
Uniqueidentifier 由 16 字节的十六进制数字组成，表示一个全局唯一的。当表的记录行要求唯一时，GUID是非常有用。例如，在客户标识号列使用这种数据类型可以区别不同的客户。
7. Unicode 数据类型
Unicode 数据类型包括 Nchar,Nvarchar 和Ntext
在 Microsoft SQL Server 中，传统的非 Unicode 数据类型允许使用由特定字符集定义的字符。在 SQL Server安装过程中，允许选择一种字符集。使用 Unicode 数据类型，列中可以存储任何由Unicode 标准定义的字符。在 Unicode 标准中，包括了以各种字符集定义的全部字符。使用Unicode数据类型，所战胜的窨是使用非 Unicode 数据类型所占用的窨大小的两倍。
在 SQL Server 中，Unicode 数据以 Nchar、Nvarchar 和 Ntext 数据类型存储。使用这种字符类型存储的列可以存储多个字符集中的字符。当列的长度变化时，应该使用Nvarchar 字符类型，这时最多可以存储 4000 个字符。当列的长度固定不变时，应该使用 Nchar 字符类型，同样，这时最多可以存储4000 个字符。当使用 Ntext 数据类型时，该列可以存储多于 4000 个字符。