在查询中使用时，VARCHAR 列的大小是否重要 [英] Does size of a VARCHAR column matter when used in queries

问题描述

可能的重复:
varchar(500) 比 varchar(8000) 有优势吗?

我知道包含 10 个字符的 VARCHAR(200) 列与包含相同数据的 VARCHAR(20) 列占用的空间量相同.

I understand that a VARCHAR(200) column containing 10 characters takes same amount of space as a VARCHAR(20) column containing same data.

我想知道将特定表的十几个 VARCHAR(200) 列更改为 VARCHAR(20) 是否会使查询运行得更快，尤其是在以下情况下:

I want to know if changing a dozen VARCHAR(200) columns of a specific table to VARCHAR(20) would make the queries run faster, especially when:

• 这些列包含的字符永远不会超过 20 个
• 这些列经常用于ORDER BY子句
• 这些列经常用在 WHERE 子句中
  • 其中一些列已编入索引，以便它们可以在 WHERE 子句中使用
  • These columns will never contain more than 20 characters
  • These columns are often used in ORDER BY clause
  • These columns are often used in WHERE clause
    • Some of these columns are indexed so that they can be used in WHERE clause

    PS:我使用的是 SQL Server 2000，但很快就会升级到更高版本的 SQL.

    PS: I am using SQL Server 2000 but will upgrade to later versions of SQL anytime soon.

    推荐答案

    是的，varchar 的长度会影响查询的估计、将分配给内部操作(例如用于排序)的内存以及 CPU 资源.您可以使用以下简单示例重现它.

    Yes, the length of varchar affects estimation of the query, memory that will be allocated for internal operation (for example for sorting) and as consequence resources of CPU. You can reproduce it with the following simple example.

    1.创建两个表:

    create table varLenTest1
    (
        a varchar(100)
    )
    
    create table varLenTest2
    (
        a varchar(8000)
    )
    

    2.用一些数据填充它们:

    2. Fill both of them with some data:

    declare @i int
    set @i = 20000
    
    while (@i > 0)
    begin 
        insert into varLenTest1 (a) values (cast(NEWID() as varchar(36)))
        set @i = @i - 1
    end 
    

    3.使用包含实际执行计划"执行以下查询:

    3. Execute the following queries with "include actual execution plan":

    select a from varLenTest1 order by a OPTION (MAXDOP 1) ;
    select a from varLenTest2 order by a OPTION (MAXDOP 1) ;
    

    如果你检查这些查询的执行计划，你会发现估计的 IO 成本和估计的 CPU 成本是非常不同的:

    If you inspect execution plans of these queries, you can see that estimated IO cost and estimated CPU cost is very different:

    这篇关于在查询中使用时，VARCHAR 列的大小是否重要的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！