一个关于nvarchar(max) 的疑问，

varchar(max)和nvarchar(max)都是大值数据类型，前者可以存储4000个字符，主要应用于英文数字，后者可以存储8000字符，用于中文或其他单字占用两个字节的字符。如果没有实际存储内容的话，即使定义成varchar(max)或nvarchar(max)，是不会占空间和影响性能的。但是建议根据实际需要定义长度。

varchar(max)就是以前的sql server 2005之前版本的text类型，它是单独保存在一个地方的，在行内只保留指定它的指针，所以比varchar要慢

大值数据类型的存储位置我记得是可以设置的：1 = 表中的 varchar(max)、nvarchar(max)、varbinary(max)、xml 和大型用户定义类型 (UDT) 列都存储在行外，用一个 16 字节的指针指向根目录。
0 = varchar(max)、nvarchar(max)、varbinary(max)、xml 和大型 UDT 值直接存储在数据行中（最大限制值为 8000 个字节，只要记录中可以容纳该值）。如果记录中容纳不下该值，则指针存储在行内，其余内容存储在 LOB 存储空间内的行外。 0 为默认值。
参考：
http://msdn.microsoft.com/zh-cn/library/ms173530.aspx在没有必要用varchar(max)的情况下，还是建议用varchar(n)，杀鸡焉用牛刀...

varchar和nvarchar都是可变长度的，是根据字段长度觉得占有空间的。但是nvarchar字段字节的存储大小是所输入字符个数的两倍。查询速度我觉得多少会有些影响

   此文为本人在2010/12/22发表于：http://www.cnblogs.com/yongfa365/archive/2010/11/22/1884943.html,现记录在此    前些天写一文章：varchar(n),nvarchar(n) 长度、性能、及所占空间分析，其中说到n不会影响到数据库空间大小及性能，甚至n是max也可能影响不到，占用空间好说，有官方文档，自己测一下也能看出，但是否影响性能，只道听途说，拿不出论据来，今天我来拿个例子证明一下以上说法，如有不妥之处，敬请拍砖view plaincopy to clipboardprint?01.----------------------------------------------------------------------------------
02.-- Subject     : nvarchar(n)及nvarchar(max)中的n及max是否会影响性能
03.-- Author      : 柳永法(yongfa365) http://www.yongfa365.com/ [email protected]
04.-- CreateDate  : 2010-11-22 23:31:04
05.-- Environment : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
06.--               Apr  2 2010 15:53:02
07.--               Copyright (c) Microsoft Corporation
08.--               Enterprise Evaluation Edition on Windows NT 5.2 <X86></X86> (Build 3790: Service Pack 2)
09.----------------------------------------------------------------------------------
10.--首先创建两个表，一个放nvarchar(4000),一个放nvarchar(max)
11.
12.CREATE TABLE [dbo].[testnvarchar4000] (
13.id int IDENTITY (1, 1) ,
14.cnt nvarchar(4000)
15.)
16.GO
17.CREATE TABLE [dbo].[testnvarcharmax] (
18.id int IDENTITY (1, 1) ,
19.cnt nvarchar(max)
20.)
21.
22.GO
23.
24.--然后插入10万条数据，每个cnt里放4000个字符(nvarchar(n)里n的最大值)，大约1.6G
25.
26.
27.BEGIN TRANSACTION
28.    DECLARE @i INT ;
29.    SET @i = 0 ;
30.    while @i<100000
31.    begin
32.        insert into [testnvarchar4000] values(LEFT(REPLICATE(cast(@i as nvarchar)+'我是柳永法',1000),4000))
33.        insert into [testnvarcharmax] values(LEFT(REPLICATE(cast(@i as nvarchar)+'我是柳永法',1000),4000))
34.        set @i=@i+1
35.    END
36.COMMIT
37.
38.
39.--清空缓存，或重启SQL服务，测试查询速度及lob读取情况(lob是大对象的意思)
40.--测试testnvarcharmax
41.DBCC DROPCLEANBUFFERS
42.DBCC FREEPROCCACHE
43.
44.SET STATISTICS IO ON
45.SET STATISTICS TIME ON
46.SELECT  COUNT(*)
47.FROM    testnvarcharmax
48.WHERE   cnt LIKE '%柳永法%'
49.
50.SET STATISTICS TIME OFF
51.SET STATISTICS IO OFF
52.
53.--测试testnvarchar4000
54.DBCC DROPCLEANBUFFERS
55.DBCC FREEPROCCACHE
56.
57.SET STATISTICS IO ON
58.SET STATISTICS TIME ON
59.SELECT  COUNT(*)
60.FROM    testnvarchar4000
61.WHERE   cnt LIKE '%柳永法%'
62.
63.SET STATISTICS TIME OFF
64.SET STATISTICS IO OFF
65.
66.
67.--结果：
68.--(1 行受影响)
69.--表 'testnvarcharmax'。扫描计数 3，逻辑读取 100000 次，物理读取 8494 次，预读 99908 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
70.--
71.-- SQL Server 执行时间:
72.--   CPU 时间 = 1172 毫秒，占用时间 = 30461 毫秒。
73.
74.
75.--(1 行受影响)
76.--表 'testnvarchar4000'。扫描计数 3，逻辑读取 100000 次，物理读取 8523 次，预读 99916 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
77.--
78.-- SQL Server 执行时间:
79.--   CPU 时间 = 968 毫秒，占用时间 = 30038 毫秒。
80.
81.
82.--从结果可以看出，这两次读取时间基本相同，并且都没有lob读取，以上数据为多次测试结果。
83.
84.
85.
86.--给testnvarcharmax前1000条字段长度+1,来测试是不是超过4000字就会使用lob读取
87.UPDATE testnvarcharmax SET cnt=cnt+'1' WHERE id <=1000
88.
89.--结果：
90.
91.--(1 行受影响)
92.--表 'testnvarcharmax'。扫描计数 3，逻辑读取 100000 次，物理读取 8292 次，预读 99696 次，lob 逻辑读取 900 次，lob 物理读取 83 次，lob 预读 0 次。
93.--
94.-- SQL Server 执行时间:
95.--   CPU 时间 = 1124 毫秒，占用时间 = 30318 毫秒。
96.
97.--此结果显示使用了lob读取。但时间相差也不太大。
98.
99.
100.--字段值加倍，再测试：
101.UPDATE testnvarcharmax SET cnt=cnt+cnt WHERE id <=1000
102.--结果：
103.
104.--(1 行受影响)
105.--表 'testnvarcharmax'。扫描计数 3，逻辑读取 100000 次，物理读取 8164 次，预读 99521 次，lob 逻辑读取 1000 次，lob 物理读取 101 次，lob 预读 0 次。
106.--
107.-- SQL Server 执行时间:
108.--   CPU 时间 = 1094 毫秒，占用时间 = 31095 毫秒。
----------------------------------------------------------------------------------
-- Subject     : nvarchar(n)及nvarchar(max)中的n及max是否会影响性能
-- Author      : 柳永法(yongfa365) http://www.yongfa365.com/ [email protected]
-- 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 FREEPROCCACHESET 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 FREEPROCCACHESET 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 毫秒。
     从以上结果基本上可以证明自己文章头提到的观点，nvarchar(n)或nvarchar(max)中的n及max只是一个符号，不会影响占用空间及性能，除非max存储字符数大于4000，所以我们只要关注实际业务需要就可以了，

上述测试，引自于http://www.yongfa365.com/Item/SQL-Server-varchar-nvarchar-max-performance.html

MVP大牛啊，感谢你的解决。。

调试易

一个关于nvarchar(max) 的疑问，

解决方案 »