事情开始得很简单MegaWare公司市场部门想要一个新的网站来发布文档开发团队觉得使用SQL Server 数据库作为文档存储仓库会使事情变得简单Steve是MegaWare的数据库管理员没有看出这有什么大问题;在数据库中存储文档而不是使用文件系统意味着服务器需要多做一些工作但是它也会使得备份和管理容易得多数据库与文件系统变得不同步也应该是不可能的
市场部门想要存储的许多文档都超过了个字节那么很明显VARCHAR不是适合这项工作的数据类型作为替代TEXT数据类型被用来定义存放数据的字段因为每个TEXT都能容纳GB的内容TEXT要存放市场部门的同事们扔进数据库的最大的文件也是没有问题的
数月过去了市场用大量的无聊拷贝填满了整个数据库但是这还不是Steve真正关心的问题数据库愉快地嗡嗡作响地运转着每个人对项目的结果都很满意
直到公司的标语改变的那个重大的日子市场部的团队认为MegaWare: Its really cool!要比原来的Its MegaWares Way or the Highway! 听起来更好因为市场部团队已经将原来的标语嵌入了仓库中每个文档的页脚上现在Steve的工作就是更改所有这些文档的页脚
没有问题 Steve想打开SQL Server 查询分析器工具执行了如下的TSQL批处理:
UPDATE MarketingDocuments
SET Document =
REPLACE(Document
Its MegaWares Way or the Highway!
MegaWare: Its really cool!)
当他看到出现的错误消息的时候Steve的轻松的微笑很快消失了替换函数的参数text数据类型无效
替换函数在编写出来的时候就对TEXT数据类型不起作用同样也对CHARINDEX或者SUBSTRING不起作用——或者至少是他们在超过千个字符的情况下不起作用更进一步地讲开发人员忘了处理TEXT或者IMAGE类型的本地变量;实际上不支持任何操作即使是简单地更新一个文档中的一个子字符串都需要用到晦涩的东西以及难以使用的类似READTEXT和WRITETEXT的函数而不是开发人员或者忙碌的数据库管理员因为想要弄清如何正确使用而采用了不同类型的函数消耗了时间
SQL Server的开发人员很幸运他们将会拨开乌云见蓝天SQL Server 引入了一系列新的被称为MAX的数据类型这是VARCHARNVARCHAR和VARBINARY类型的扩展这几种类型以前被限制在字节以下MAX可以容纳高达GB的数据与TEXT和IMAGE一样——并且完全兼容所有的SQL Server内置的字符串函数
用MAX关键字定义一个某种MAX类型的变量与替代字符串的尺寸(为VARCHAR/NVARCHAR的时候)或者字节(为VARBINARY的时候)一样简单
DECLARE @BigString VARCHAR(MAX)
SET @BigString = abc
虽然这个变量可以自由地操纵并且可以传递给任何的内置的字符串函数兼容性仍然不是没有问题首先开发人员不能期望指定了尺寸的VARCHAR和VARBINARY变量在达到个字节的极限的时候可以自动升级到MAX版本例如如下的批处理:
DECLARE @String VARCHAR()
DECLARE @String VARCHAR()
SET @String = REPLICATE( )
SET @String = REPLICATE( )
SELECT LEN(@String + @String)
+=但是指定了尺寸的VARCHAR的极限是因为这两个变量中没有一个是MAX类型LEN函数的结果就是不是在将两个变量连接的时候一种简单的修正方法就是声明这两个变量中的一个为VARCHAR(MAX)或者将其中的一个变量进行转换与一个规定了尺寸的类型进行连接的时候优先考虑MAX类型最终结果是MAX类型所以以下批处理的结果是正如我们期望的一样:
DECLARE @String VARCHAR()
DECLARE @String VARCHAR()
SET @String = REPLICATE( )
SET @String = REPLICATE( )
SELECT LEN(CONVERT(VARCHAR(MAX) @String) + @String)
在传递给字符串函数的时候开发人员意识到字符串的原意在默认情况下是规定了尺寸的而不是MAX类型也是至关重要的例如以下查询的结果就很令人惊奇:
SELECT LEN(REPLICATE( ))
因为字符串是被作为规定了尺寸的VARCHAR对待而不是VARCHAR(MAX)结果就是——但是在SQL Server 中REPLICATE函数能够产生高达GB的字符串要修正这个问题可以将字符串转换为VARCHAR(MAX)这样函数就会输出同样的类型了:
SELECT LEN(REPLICATE(CONVERT(VARCHAR(MAX) ) ))
这个查询现在将会返回期望的结果:记住总是要对采用了新特性编写的代码进行非常仔细的测试;隐藏的问题例如上面描述的问题可能并且毫无疑问地会在最坏的时间里造成灾难性的后果
除了变量之外MAX类型也可以用于定义表的字段:
CREATE TABLE BigStrings
(
BigString VARCHAR(MAX)
)
当用于表的时候意识到MAX类型具有与TEXT和IMAGE类型稍微不同的行溢出行为是非常重要的在SQL Server中最大的行尺寸是字节要超过这个限制并且仍然管理每个都拥有高达GB的存储用TEXT和IMAGE类型存储的数据会被存储引擎自动地断行在行里只留下一个字节的指针这意味着行的尺寸是减少了这对性能有好处然而检索大数据是昂贵的因为它不是与同一行的数据存放在同一个位置
MAX数据类型在默认情况下使用TEXT/IMAGE溢出行为和正常尺寸的VARCHAR/VARBINARY类型的行为的混合方式如果一个字段的数据加上表中所有其他字段的数据总量少于字节数据就存放在行内如果数据超过字节MAX字段的数据就会存放在行外对于大字符串的表以下的行将会与表中的其他数据存储在同一个数据页内:
INSERT BigStrings (BigString)
VALUES (REPLICATE( ))
But the following row will result in an overflow:
INSERT BigStrings (BigString)
VALUES (REPLICATE(CONVERT(VARCHAR(MAX) ) ))
你可以更改MAX数据类型在每个表的基础上的默认的行为它们会表现得和TEXT和IMAGE类型一样这是通过使用sp_tableoption 存储过程中的大数值类型在行外选项实现的为了修改大字符串表以将MAX类型的处理方式变得与TEXT和IMAGE数据类型的处理方式相同可以使用如下的TSQL:
EXEC sp_tableoption
BigStrings
large value types out of row
看看定义一个MAX数据类型有多容易与他们提供的灵活性一样一些数据设计师将会被引诱以下列的方式开始定义表:
CREATE TABLE Addresses
(
Name VARCHAR(MAX)
AddressLine VARCHAR(MAX)
AddressLine VARCHAR(MAX)
City VARCHAR(MAX)
State VARCHAR(MAX)
PostalCode VARCHAR(MAX)
)
设计师要注意了:不要这样做!一个企业中的数据模型既应该包含有具有实际限制的数据还要给用户接口设计师有关字段尺寸的大致的指导像这样的表又该创建什么样的用户接口呢?
除了数据整合和用户接口含义之外如果设计师这样不必要地使用这些类型还会带来性能上的损害记住查询优化器使用字段的尺寸作为判断优化查询计划的众多标准之一对于这个表优化器几乎没有任何选择
所以现在你知道了MAX数据类型为SQL Server 处理大数据增加了很大部分的灵活性但是MegaWare的那个不幸的数据库管理员Steve会发生什么变化?还在坚持使用SQL Server 他开始更新简历想象着如果更新表失败了话他的工作也就失去了但是他也是幸运的——还有世界各地的MegaWare产品的拥护者——用GOOGLE的搜索可以很快地找到这篇文章《在TEXT字段中查找并替代》这篇文章告诉他如何正确的进行更新他花了整晚的时间来学习资料;再过几个月之后TEXT和IMAGE数据类型就仅仅是一段不愉快的记忆了
