【个人学习之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个不同数据类型的数据进行计算的时候 首先低优先级向高优先级转化，然后计算

附上数据类型优先级表：

 

1. 用户定义数据类型（最高）
   
2. sql_variant
   
3. xml
   
4. datetime
   
5. smalldatetime
   
6. float
   
7. real
   
8. decimal
   
9. money
   
10. smallmoney
    
11. bigint
    
12. int
    
13. smallint
    
14. tinyint
    
15. bit
    
16. ntext
    
17. text
    
18. image
    
19. timestamp
    
20. uniqueidentifier
    
21. nvarchar（包括 nvarchar(max)）
    
22. nchar
    
23. varchar （包括 varchar(max)）
    
24. char
    
25. varbinary（包括 varbinary(max)）
    
26. 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

  -----------------------