sql中NVARCHAR(MAX) 性能和占空间分析 varchar(n),nvarchar(n) 长度性能及所占空间分析

时间：2022-05-08 09:33:44

varchar(n),nvarchar(n) 中的n怎么解释：
　　nvarchar(n)最多能存n个字符，不区分中英文。
　　varchar(n)最多能存n个字节，一个中文是两个字节。

所占空间：

　　nvarchar(n)一个字符会占两个字节空间。
　　varchar(n)中文占两字节空间，英文占一个。

n的取值范围：
　　nvarchar(n)   n的范围是：1与4000之间
　　varchar(n)   n的范围是：1与8000之间

n的大小是否会影响性能：
　　varchar及nvarchar里的长度 n 不会影响空间大小及性能。除非n是max并且内容大于4000或8000
　　设置n更多的是业务需要，如限制身份证只能输入18位，再多就报错，或者防止恶意攻击撑爆硬盘。对空间及性能都没有影响

n设置多大比较好：
　　既然对空间及性能都没有影响，那我们只要考虑业务需要就可以了，我分析过微软的数据库，大都设置为：256，也会看到64,128,512,max等，可能是便于记忆吧。

varchar(n),nvarchar(n)存储空间举例解释：
　　包含 n 个字符的可变长度 Unicode 字符数据。字节的存储大小是所输入字符个数的两倍。 
　　两字段分别有字段值：我和coffee 
　　那么varchar字段占2×2+6=10个字节的存储空间，而nvarchar字段占8×2=16个字节的存储空间。 
　　如字段值只是英文可选择varchar，而字段值存在较多的双字节（中文、韩文等）字符时用nvarchar。

varchar和nvarchar如何选择？

　　varchar在SQL Server中是采用单字节来存储数据的，nvarchar是使用Unicode来存储数据的．中文字符存储到SQL Server中会保存为两个字节（一般采用Unicode编码），英文字符保存到数据库中，如果字段的类型为varchar，则只会占用一个字节，而如果字段的类型为nvarchar，则会占用两个字节．

例

 代码如下 复制代码

----------------------------------------------------------------------------------
-- Subject     : nvarchar(n)及nvarchar(max)中的n及max是否会影响性能
-- 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 <X86></X86> (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 毫秒。

正常情况下，我们使用varchar也可以存储中文字符，但是如果遇到操作系统是英文操作系统并且对中文字体的支持不全面时, 在SQL Server存储中文字符为varchar就会出现乱码(显示为??)．而且正常情况下，主机都会支持中文的环境，所以如果使用varchar来存储数据，在开发阶段是发现不了的．多数情况下，在布署的时候也不会有问题．
　　但是！如果布署的主机是英文操作系统，并且不支持中文环境，那问题就出来了．所有的varchar字段在存储中文的时候都会变成乱码（显示为??）．而且一般情况下你不会知道这是因为你采用了错误的数据类型来存储所造成的，你会试着去装中文字体，试着去设置操作系统的语言环境．．．这些都不能解决问题，唯一能解决问题的是把数据库字段的类型个性为nvarchar（或者nchar）．对项目管理比较熟悉的朋友应该都知道，到布署阶段再来修改数据库是一个很恐怖的事情．

使用nvarchar的另一个非常好处就是在判断字符串的时候可以不需要考虑中英文两种字符的差别．
　　当然，使用nvarchar存储英文字符会增大一倍的存储空间．但是在存储代价已经很低廉的情况下，优先考虑兼容性会给你带来更多好处的．

所以在Design的时候应该尽量使用nvarchar来存储数据．只有在你确保该字段不会保存中文的时候，才采用varchar来存储

如果 varchar(300) 和 varchar(8000) 都存储相同的字符数，性能上是没有差别的，存储行为上也没有不同。因为它们都有相同的存储结构，两个字节的偏移，两个字节的列数（如果表中所有的列都是 varchar 类型）。区别只在于存储容量上。
大多数的性能比较都集中在 varchar 和 char，varchar 和 varchar(max) 上。还有，行外存储（SQL Server 2005 支持的）。

　　varchar(max) （lob 类型）与 varchar 存储方式是不同的。
　　当 LOB 数据足够小时，可以考虑将数据直接存储在数据行（行所在的数据页面）中，从而可以避免额外的读取 LOB 页面，提升访问 LOB 数据的效率（将 LOB 数据直接存储在数据页面的阈值由 text in row 选项设置）。
而当 LOB 数据大于此阈值，或者所在行的大小超过了 8060 字节（单行最大 SIZE），LOB 数据将会存储在 LOB 页面，而在数据页面中保留一个指向 LOB 页面的 16 字节的指针。其访问效率当然会将低。
另外还有，恶意用户可以利用这一点“撑爆”你的磁盘。

相关文章