由于varchar总是动态地分配空间,我的问题是与使用varchar(5000)相比,使用varchar(255)是否更有效或节省更多空间.如果是,为什么？
解决方法
是的,如果所有值都适合后者,则varchar(5000)可能比varchar(255)更差.原因是sql Server将根据表中列的声明(非实际)大小来估计数据大小,进而估计内存授权.当你有varchar(5000)时,它会假设每个值都是2,500个字符长,并根据它保留内存.
这是一个来自my recent GroupBy presentation on bad habits的演示,可以很容易地为自己证明(对于某些sys.dm_exec_query_stats输出列需要sql Server 2016,但仍应使用SET STATISTICS TIME ON或早期版本的其他工具来证明);它针对相同的数据显示了相同查询的更大内存和更长的运行时间 – 唯一的区别是列的声明大小：
-- create three tables with different column sizes CREATE TABLE dbo.t1(a nvarchar(32),b nvarchar(32),c nvarchar(32),d nvarchar(32)); CREATE TABLE dbo.t2(a nvarchar(4000),b nvarchar(4000),c nvarchar(4000),d nvarchar(4000)); CREATE TABLE dbo.t3(a nvarchar(max),b nvarchar(max),c nvarchar(max),d nvarchar(max)); GO -- that's important -- Method of sample data pop : irrelevant and unimportant. INSERT dbo.t1(a,b,c,d) SELECT TOP (5000) LEFT(name,1),RIGHT(name,ABS(column_id/10),ABS(column_id%10) FROM sys.all_columns ORDER BY object_id; GO 100 INSERT dbo.t2(a,d) SELECT a,d FROM dbo.t1; INSERT dbo.t3(a,d FROM dbo.t1; GO -- no "primed the cache in advance" tricks DBCC FREEPROCCACHE WITH NO_INFOMSGS; DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; GO -- Redundancy in query doesn't matter! Just has to create need for sorts etc. GO SELECT DISTINCT a,d,DENSE_RANK() OVER (PARTITION BY b,c ORDER BY d DESC) FROM dbo.t1 GROUP BY a,d ORDER BY c,a DESC; GO SELECT DISTINCT a,c ORDER BY d DESC) FROM dbo.t2 GROUP BY a,c ORDER BY d DESC) FROM dbo.t3 GROUP BY a,a DESC; GO SELECT [table] = N'...' + SUBSTRING(t.[text],CHARINDEX(N'FROM ',t.[text]),12) + N'...',s.last_dop,s.last_elapsed_time,s.last_grant_kb,s.max_ideal_grant_kb FROM sys.dm_exec_query_stats AS s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t WHERE t.[text] LIKE N'%dbo.'+N't[1-3]%' ORDER BY t.[text];
所以,是的,请调整正确的列数.
此外,我使用varchar(32),varchar(255),varchar(5000),varchar(8000)和varchar(max)重新运行测试.类似的结果(click to enlarge),虽然32和255之间,以及5,000到8,000之间的差异可以忽略不计：
0700207003
这是另一项测试,TOP(5000)更改为更完全可重复的测试我正在不断纠结(click to enlarge)：
0700507006
因此,即使有5,000行而不是10,000行(并且sys.all_columns中至少有5,000行,至少早于sql Server 2008 R2),观察到相对线性的进展 – 即使使用相同的数据,定义的大小也是如此.在列中,需要更多的内存和时间来满足完全相同的查询(即使它确实具有无意义的DISTINCT).
