http://bbs.zdnet.com.cn/thread-99851-1-1.html(转)
SQL Server 2005关于数据类型最大值48
事情开始得很简单。MegaWare公司市场部门想要一个新的网站来发布文档,开发团队觉得使用SQL Server 2000数据库作为文档存储仓库会使事情变得简单。Steve是MegaWare的数据库管理员,没有看出这有什么大问题;在数据库中存储文档,而不是使用文件系统,意味着服务器需要多做一些工作,但是它也会使得备份和管理容易得多。数据库与文件系统变得不同步也应该是不可能的。
市场部门想要存储的许多文档都超过了8000个字节,那么很明显VARCHAR不是适合这项工作的数据类型。作为替代,TEXT数据类型被用来定义存放数据的字段。因为每个TEXT都能容纳2GB的内容,TEXT要存放市场部门的同事们扔进数据库的最大的文件也是没有问题的。
数月过去了,市场用大量的无聊拷贝填满了整个数据库。但是这还不是Steve真正关心的问题。数据库愉快地嗡嗡作响地运转着,每个人对项目的结果都很满意。
直到公司的标语改变的那个重大的日子。市场部的团队认为“MegaWare: It's really cool!”要比原来的“It's MegaWare's Way or the Highway!” 听起来更好。因为市场部团队已经将原来的标语嵌入了仓库中每个文档的页脚上,现在Steve的工作就是更改所有这些文档的页脚。
“没有问题,” Steve想,打开SQL Server 查询分析器工具,执行了如下的T-SQL批处理:
UPDATE MarketingDocuments
SET Document =
REPLACE(Document,
'It''s MegaWare''s Way or the Highway!',
'MegaWare: It''s really cool!)
当他看到出现的错误消息的时候,Steve的轻松的微笑很快消失了,“替换函数的参数1,text数据类型无效。”
替换函数在编写出来的时候,就对TEXT数据类型不起作用。同样也对CHARINDEX或者SUBSTRING不起作用??或者至少是他们在超过8千个字符的情况下不起作用。更进一步地讲,开发人员忘了处理TEXT或者IMAGE类型的本地变量;实际上不支持任何操作。即使是简单地更新一个文档中的一个子字符串都需要用到晦涩的东西,以及难以使用的类似READTEXT和WRITETEXT的函数。而不是开发人员或者忙碌的数据库管理员因为想要弄清如何正确使用而采用了不同类型的函数消耗了时间。
SQL Server的开发人员很幸运,他们将会拨开乌云见蓝天。SQL Server 2005引入了一系列新的被称为MAX的数据类型。这是VARCHAR,NVARCHAR和VARBINARY类型的扩展,这几种类型以前被限制在8000字节以下。MAX可以容纳高达2GB的数据,与TEXT和IMAGE一样??并且完全兼容所有的SQL Server内置的字符串函数。
用MAX关键字定义一个某种MAX类型的变量与替代字符串的尺寸(为VARCHAR/NVARCHAR的时候)或者字节(为VARBINARY的时候)一样简单。
DECLARE @BigString VARCHAR(MAX)
SET @BigString = 'abc'
虽然这个变量可以自由地操纵,并且可以传递给任何的内置的字符串函数,兼容性仍然不是没有问题。首先,开发人员不能期望指定了尺寸的VARCHAR和VARBINARY变量在达到8000个字节的极限的时候可以自动“升级”到MAX版本。例如,如下的批处理:
DECLARE @String1 VARCHAR(4001)
DECLARE @String2 VARCHAR(4001)
SET @String1 = REPLICATE('1', 4001)
SET @String2 = REPLICATE('2', 4001)
SELECT LEN(@String1 + @String2)
4001+4001=8002,但是指定了尺寸的VARCHAR的极限是8000。因为这两个变量中没有一个是MAX类型,LEN函数的结果就是8000,不是8002。在将两个变量连接的时候,一种简单的修正方法就是声明这两个变量中的一个为VARCHAR(MAX)或者将其中的一个变量进行转换。与一个规定了尺寸的类型进行连接的时候,优先考虑MAX类型,最终结果是MAX类型。所以,以下批处理的结果是8002,正如我们期望的一样:
DECLARE @String1 VARCHAR(4001)
DECLARE @String2 VARCHAR(4001)
SET @String1 = REPLICATE('1', 4001)
SET @String2 = REPLICATE('2', 4001)
SELECT LEN(CONVERT(VARCHAR(MAX), @String1) + @String2)
在传递给字符串函数的时候,开发人员意识到字符串的原意在默认情况下是规定了尺寸的,而不是MAX类型,也是至关重要的。例如,以下查询的结果就很令人惊奇:
SELECT LEN(REPLICATE('1', 8002))
因为字符串‘1’是被作为规定了尺寸的VARCHAR对待,而不是VARCHAR(MAX),结果就是8000??但是在SQL Server 2005中,REPLICATE函数能够产生高达2GB的字符串。要修正这个问题,可以将字符串转换为VARCHAR(MAX),这样函数就会输出同样的类型了:
SELECT LEN(REPLICATE(CONVERT(VARCHAR(MAX), '1'), 8002))
这个查询现在将会返回期望的结果:8002。记住,总是要对采用了新特性编写的代码进行非常仔细的测试;隐藏的问题,例如上面描述的问题,可能并且毫无疑问地会在最坏的时间里造成灾难性的后果。
本贴来自ZDNetChina中文社区 http://bbs.zdnet.com.cn ,本贴地址:http://bbs.zdnet.com.cn/viewthread.php?tid=99851
除了变量之外,MAX类型也可以用于定义表的字段:
CREATE TABLE BigStrings
(
BigString VARCHAR(MAX)
)
当用于表的时候,意识到MAX类型具有与TEXT和IMAGE类型稍微不同的行溢出行为是非常重要的。在SQL Server中,最大的行尺寸是8060字节。要超过这个限制,并且仍然管理每个都拥有高达2GB的存储,用TEXT和IMAGE类型存储的数据会被存储引擎自动地断行,在行里只留下一个16字节的指针。这意味着行的尺寸是减少了,这对性能有好处。然而,检索大数据是昂贵的,因为它不是与同一行的数据存放在同一个位置。
MAX数据类型在默认情况下,使用TEXT/IMAGE溢出行为和正常尺寸的VARCHAR/VARBINARY类型的行为的混合方式。如果一个字段的数据,加上表中所有其他字段的数据,总量少于8060字节,数据就存放在行内。如果数据超过8060字节,MAX字段的数据就会存放在行外。对于大字符串的表,以下的行将会与表中的其他数据存储在同一个数据页内:
INSERT BigStrings (BigString)
VALUES (REPLICATE('1', 8000))
But the following row will result in an overflow:
INSERT BigStrings (BigString)
VALUES (REPLICATE(CONVERT(VARCHAR(MAX), '1'), 100000))
你可以更改MAX数据类型在每个表的基础上的默认的行为,它们会表现得和TEXT和IMAGE类型一样。这是通过使用sp_tableoption 存储过程中的“大数值类型在行外”选项实现的。为了修改大字符串表以将MAX类型的处理方式变得与TEXT和IMAGE数据类型的处理方式相同,可以使用如下的T-SQL:
EXEC sp_tableoption
'BigStrings',
'large value types out of row',
'1'
看看定义一个MAX数据类型有多容易,与他们提供的灵活性一样,一些数据设计师将会被引诱以下列的方式开始定义表:
CREATE TABLE Addresses
(
Name VARCHAR(MAX),
AddressLine1 VARCHAR(MAX),
AddressLine2 VARCHAR(MAX),
City VARCHAR(MAX),
State VARCHAR(MAX),
PostalCode VARCHAR(MAX)
)
设计师要注意了:不要这样做!一个企业中的数据模型既应该包含有具有实际限制的数据,还要给用户接口设计师有关字段尺寸的大致的指导。像这样的表又该创建什么样的用户接口呢?
除了数据整合和用户接口含义之外,如果设计师这样不必要地使用这些类型还会带来性能上的损害。记住,查询优化器使用字段的尺寸作为判断优化查询计划的众多标准之一。对于这个表,优化器几乎没有任何选择。
所以,现在你知道了MAX数据类型为SQL Server 2005处理大数据增加了很大部分的灵活性。但是MegaWare的那个不幸的数据库管理员,Steve会发生什么变化?还在坚持使用SQL Server 2000,他开始更新简历,想象着如果更新表失败了话,他的工作也就失去了。但是他也是幸运的??还有世界各地的MegaWare产品的拥护者??用GOOGLE的搜索可以很快地找到这篇文章《在TEXT字段中查找并替代》,这篇文章告诉他如何正确的进行更新。他花了整晚的时间来学习资料;再过几个月之后,TEXT和IMAGE数据类型就仅仅是一段不愉快的记忆了
本贴来自ZDNetChina中文社区 http://bbs.zdnet.com.cn ,本贴地址:http://bbs.zdnet.com.cn/viewthread.php?tid=99851
分享到:
相关推荐
Sqlserver中char,nchar,varchar与Nvarchar的区别分析,使用sqlserver的朋友可以参考下。
SQLServer中char、varchar、nchar、nvarchar的区别 SQLServer中char、varchar、nchar、nvarchar的区别.txt
对于程序中的一般字符串类型的字段,SQL Server中有char、varchar、nchar、nvarchar四种类型来对应,那么这四种类型有什么区别呢,这里做一下对比。 1.定长或变长 所谓定长就是长度固定,当要保存的数据长度不够时将...
关于SQL server2005中插入汉字变成问号的解决办法 1.右击你所建的数据库,点击“属性”,选择“选项”并点击。将其中的排序规则设置为:Chinese_PRC_CI_AS(选择下拉列表框即可设置),然后“确定“。2.在表中将你的...
sqlserver字段类型介绍 数据库中,英文字符只需要一个字节存储就足够了,但汉字和其他众多非英文字符,则需要两个字节存储。如果英文与汉字同时存在,由于占用空间数不同,容易造成混乱,导致读取出来的字符串是乱码...
如果我们数据库的ID设置为varchar型的 在查询的时候... 适用于SQLServer Oracle 二、 代码如下: select * from yourtable order by convert(int,yourcol); 仅适用于SQLServer 作者 itmyhome 您可能感兴趣的文章:SQL S
中文字符存储到SQL Server中会保存为两个字节(一般采用Unico编码),英文字符保存到数据库中,如果字段的类型为varchar,则只会占用一个字节,而如果字段的类型为nvarchar,则会占用两个字节。 正常情况下,我们...
在SQL Server中,当我们设置字符型字段的时候,往往有很多个数据类型供我们选择,如:char nchar varchar nvarchar等,那么我们到底应该选择哪一个呢?下面就一一来描述其区别。 char:固定长度,非Unicode字符数据...
有人讨论char nchar varchar nvarchar这几个数据类型的区别跟实际使用情况,很多人都搞不清楚究竟哪个场景使用哪个数据类型,下面用代码解释一下
因最近自己制作的软件需运行在简繁系统中,但数据库设计时...此脚本可批量处理数据库内所有char和varchar转换为nchar和nvarchar,已集成重建主键,约束,索引,此脚本没有启用事务,运行前请备份原数据库或手动增加事务
Microsoft Access、MySQL 以及 SQL Server 所使用的数据类型和范围。 SQL Server 数据类型 Character 字符串 数据类型 描述 存储 char(n) 固定长度的字符串。最多 8,000 个字符。 n varchar(n) 可变长度的...
SQL Server遍历表一般都要用到游标,SQL Server中可以很容易的用游标实现循环,实现SQL Server遍历表中记录。本文将介绍利用使用表变量和游标实现数据库中表的遍历。 表变量来实现表的遍历 以下代码中,代码块之间的...
解决sqlserver,float去除后面的零超过四位会转换成特殊的编码格式的问题,无限制去除后面多余的0
SQLServer2000数据类型 varchar number
varchar(n) 长度为 n 个字节的可变长度且非 Unicode 的字符数据。n 必须是一个介于 1 和 8,000 之间的数值。...如字段值只是英文可选择varchar,而字段值存在较多的双字节(中文、韩文等)字符时用nva
常规数据类型包括CHAR和VARCHAR,Unicode数据类型包括NCAHR和NVARCHAR。常规字符的每个字符使用1个字节存储,而Unicode数据的每个字符要求2个字节。常规字符列限制为仅仅只针对于英语,而Unicode则是针对于多种语言...
NULL 博文链接:https://yizhilong28.iteye.com/blog/824656