SQL Server2005学习笔记3之--关于LOB和数据的隐式转化

1.关于LOB（Large Object）
2005后SQL 引进了新的MAX说明符来支持LOB。虽然这个版本的TEXT,NTEXT,IMAGE继续被支持着，因为前者的出现而不再推荐使用。
它还引入了XML数据类型，允许你存储和处理XML数据。还允许使用BULK引擎高效地将文件数据加载为行集。
MAX:
通过MAX说明符，存储的值可以高达2GB 这是目前为止最大量的数据类型。
2005中统一了常规数据类型和LOB类型的编程模型，所有支持常规类型的函数现在也支持由MAX说明符定义的数据类型。
这里提一点 ：如果你想修改这类型数据的一部分值的时候 如果采用STUFF这样的函数将重新加载整个字符串，这样对大型的数据效率是个问题。这个时候你可以尝试对MAX列制作的函数 WRITE方法。
UPDATE dbo.CustomerData
  SET txt_data.WRITE('one hundred and two', 9, 3)
WHERE custid = 102;
--'one hundred and two' 是替换的目标值，9是替换的起始位置（从0开始，9就是第十个），3替换长度
WRITE(A,B,C)
1.A为NULL 从B位置开始的字符串截断
2.B为NULL && C==0 把A直接添加到字符串的末尾
3.C为NULL B之后的字符串截断，然后将A添加到字符串末尾
另外：如果数据对象为NULL，write方法更新失效
BULK：
--建表
IF OBJECT_ID('dbo.Shippers') IS NOT NULL
  DROP TABLE dbo.Shippers;
GO
CREATE TABLE dbo.Shippers
(
  ShipperID   INT          NOT NULL PRIMARY KEY,
  CompanyName NVARCHAR(40) NOT NULL,
  Phone       NVARCHAR(24) NOT NULL CHECK(Phone NOT LIKE '%[^0-9() ]%')
);
GO
--利用OPENROWSET函数返回值作为表
INSERT INTO dbo.Shippers WITH (IGNORE_CONSTRAINTS)--目标表（IGNORE_CONSTRAINTS 插入时候无视约束）
  SELECT ShipperID, CompanyName, Phone
    FROM OPENROWSET(BULK 'c:\temp\shippers.txt',
           FORMATFILE = 'c:\temp\shippers.fmt') AS S;
--这里关于BULK的数据转移不再多说 内容很多 日后整理出大量数据迁移的三种基本方法 BCP, INSERT BULK, OPENROWSET(BULK...) 后续学习后整理。

2.隐式转化
大家都知道当2个不同数据类型的数据进行计算的时候 首先低优先级向高优先级转化，然后计算
附上数据类型优先级表：

用户定义数据类型（最高）
sql_variant
xml
datetime
smalldatetime
float
real
decimal
money
smallmoney
bigint
int
smallint
tinyint
bit
ntext
text
image
timestamp
uniqueidentifier
nvarchar（包括 nvarchar(max)）
nchar
varchar （包括 varchar(max)）
char
varbinary（包括 varbinary(max)）
binary（最低）
这就是为什么1+'1' =2 ,1. *col 结果为DECIMAL类型了
下面说个由自动转换引发的问题：
SELECT
  CASE
    WHEN 1 > 1 THEN 10
    WHEN 1 = 1 THEN 'abc'
    WHEN 1 < 1 THEN 10.
  END;
-----错误：消息 8114，级别 16，状态 5，第 1 行
从数据类型 varchar 转换为 numeric 时出错。
-----------------------------------------------
为什么会错呢？从错误可以看出 本来预想的结果为‘abc’ 然后我们看'abc' 10 10. 数据类型分别为varchar,int,numeric 优先级最高为
numeric ,所以结果也应该是numeric ‘abc’无法转成NUMERIC 所以。。。。
解决方法有2个：
1.将结果表达式都转化成通用的数据类型,比如varchar(100),但是这样做的可能会出现比较，排序的时候的问题
2.将结果表达式都转化成SQL_VARIANT
SELECT
  CASE
    WHEN 1 > 1 THEN CAST(10 AS SQL_VARIANT)
    WHEN 1 = 1 THEN CAST('abc' AS SQL_VARIANT)
    WHEN 1 < 1 THEN CAST(10. AS SQL_VARIANT)
  END;
比较具有不同基本数据类型的 sql_variant 值，而且基本数据类型属于不同的数据类型系列时，认为在层次结构图中数据类型系列较高的值为两值中的较大值。
比较具有不同基本数据类型的 sql_variant 值，而且基本数据类型属于相同的数据类型系列时，层次结构图中基本数据类型较低的值先隐式转换成其他数据类型，然后再进行比较。
关于SQL_VARIANT 参考http://msdn.microsoft.com/zh-cn/library/ms181071.aspx
筛选表达式也存在隐式转化：
a.表达式2边数据类型相同时候,如果存在索引，就会考虑使用索引，当然使用函数后就不再是SARG了
b.表达式2边数据类型不相同的时候，不相同版本处理方法是不一样的。
2000中col=标量表达式 如果标量表达式优先级大于col列的数据类型 列就转换成标量表达式的类型 无法再使用SARG；如果是2个表的列COL1=COL2.COL1 COL2数据类型系列即使一样，优化器也不会认为2列的索引具有相同的排序行为，除非低优先级的一侧在转化数据类型后使用SORT运算显示排序。
2005更加方法 只要知道2列属于同一个数据类型系列（注意，不是数据类型 是数据类型系列 这个可以去百度下），索引就具有相同的排序行为，所以可以使用索引了。
测试代码，查看执行计划即可以发现
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;
IF OBJECT_ID('dbo.T2') IS NOT NULL
  DROP TABLE dbo.T2;
GO
CREATE TABLE dbo.T1(col1 INT PRIMARY KEY);
CREATE TABLE dbo.T2(col1 NUMERIC(12, 2) PRIMARY KEY);
INSERT INTO dbo.T1(col1) VALUES(1);
INSERT INTO dbo.T1(col1) VALUES(2);
INSERT INTO dbo.T1(col1) VALUES(3);
INSERT INTO dbo.T2(col1) VALUES(1.);
INSERT INTO dbo.T2(col1) VALUES(2.);
INSERT INTO dbo.T2(col1) VALUES(3.);
SELECT T1.col1, T2.col1
FROM T1 INNER MERGE JOIN T2
  ON dbo.T1.col1 = dbo.T2.col1;
GO