nvarchar(n)及nvarchar(max)中的n及max是否会影响性能

-- --------------------------------------------------------------------------------
--  Subject     : nvarchar(n)及nvarchar(max)中的n及max是否会影响性能
--  Author      : 柳永法(yongfa365) http://www.yongfa365.com/ yongfa365@qq.com
--  CreateDate  : 2010-11-22 23:31:04
--  Environment : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) 
--                Apr  2 2010 15:53:02 
--                Copyright (c) Microsoft Corporation
--                Enterprise Evaluation Edition on Windows NT 5.2  (Build 3790: Service Pack 2)
-- --------------------------------------------------------------------------------
-- 首先创建两个表，一个放nvarchar(4000),一个放nvarchar(max)

CREATE   TABLE   [ dbo ] . [ testnvarchar4000 ]  (
id  int   IDENTITY  ( 1 ,  1 ) ,
cnt  nvarchar ( 4000 )
) 
GO
CREATE   TABLE   [ dbo ] . [ testnvarcharmax ]  (
id  int   IDENTITY  ( 1 ,  1 ) ,
cnt  nvarchar ( max )
) 

GO

-- 然后插入10万条数据，每个cnt里放4000个字符(nvarchar(n)里n的最大值)，大约1.6G


BEGIN   TRANSACTION
     DECLARE   @i   INT  ;
     SET   @i   =   0  ;
     while   @i < 100000
     begin
         insert   into   [ testnvarchar4000 ]   values ( LEFT ( REPLICATE ( cast ( @i   as   nvarchar ) + ' 我是柳永法 ' , 1000 ), 4000 ))
         insert   into   [ testnvarcharmax ]   values ( LEFT ( REPLICATE ( cast ( @i   as   nvarchar ) + ' 我是柳永法 ' , 1000 ), 4000 ))
         set   @i = @i + 1
     END
COMMIT


-- 清空缓存，或重启SQL服务，测试查询速度及lob读取情况(lob是大对象的意思)
-- 测试testnvarcharmax
DBCC  DROPCLEANBUFFERS
DBCC  FREEPROCCACHE

SET   STATISTICS  IO  ON  
SET   STATISTICS  TIME  ON
SELECT    COUNT ( * )
FROM     testnvarcharmax
WHERE    cnt  LIKE   ' %柳永法% '

SET   STATISTICS  TIME  OFF
SET   STATISTICS  IO  OFF

-- 测试testnvarchar4000
DBCC  DROPCLEANBUFFERS
DBCC  FREEPROCCACHE

SET   STATISTICS  IO  ON  
SET   STATISTICS  TIME  ON
SELECT    COUNT ( * )
FROM     testnvarchar4000
WHERE    cnt  LIKE   ' %柳永法% '

SET   STATISTICS  TIME  OFF
SET   STATISTICS  IO  OFF


-- 结果：
-- (1 行受影响)
-- 表 'testnvarcharmax'。扫描计数 3，逻辑读取 100000 次，物理读取 8494 次，预读 99908 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
--
--  SQL Server 执行时间:
--    CPU 时间 = 1172 毫秒，占用时间 = 30461 毫秒。


-- (1 行受影响)
-- 表 'testnvarchar4000'。扫描计数 3，逻辑读取 100000 次，物理读取 8523 次，预读 99916 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
--
--  SQL Server 执行时间:
--    CPU 时间 = 968 毫秒，占用时间 = 30038 毫秒。


-- 从结果可以看出，这两次读取时间基本相同，并且都没有lob读取，以上数据为多次测试结果。



-- 给testnvarcharmax前1000条字段长度+1,来测试是不是超过4000字就会使用lob读取
UPDATE  testnvarcharmax  SET  cnt = cnt + ' 1 '   WHERE  id  <= 1000

-- 结果：

-- (1 行受影响)
-- 表 'testnvarcharmax'。扫描计数 3，逻辑读取 100000 次，物理读取 8292 次，预读 99696 次，lob 逻辑读取 900 次，lob 物理读取 83 次，lob 预读 0 次。
--
--  SQL Server 执行时间:
--    CPU 时间 = 1124 毫秒，占用时间 = 30318 毫秒。

-- 此结果显示使用了lob读取。但时间相差也不太大。


-- 字段值加倍，再测试：
UPDATE  testnvarcharmax  SET  cnt = cnt + cnt  WHERE  id  <= 1000
-- 结果：

-- (1 行受影响)
-- 表 'testnvarcharmax'。扫描计数 3，逻辑读取 100000 次，物理读取 8164 次，预读 99521 次，lob 逻辑读取 1000 次，lob 物理读取 101 次，lob 预读 0 次。
--
--  SQL Server 执行时间:
--    CPU 时间 = 1094 毫秒，占用时间 = 31095 毫秒。