Does size of a VARCHAR column matter when used in

问题:

Possible Duplicate:
is there an advantage to varchar(500) over varchar(8000)?

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

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:

• 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: I am using SQL Server 2000 but will upgrade to later versions of SQL anytime soon.

回答1:

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 two tables:

create table varLenTest1
(
    a varchar(100)
)

create table varLenTest2
(
    a varchar(8000)
)

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. 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) ;

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

回答2:

Here's a blog post that explains under what circumstances and why there are performance differences when using different column sizes (with tests and technical details):

Advanced TSQL Tuning: Why Internals Knowledge Matters

回答3:

It does matter for the query optimiser when it will evaluate the best query path to perform your query. When more than one path will be available, it will calculate an I/O cost and other various parameters based on your query and from these, chose the one that will appears to him as the least costly.

This is not an absolute calculation, it's only an approximation process. Therefore, it can easily be thrown off if the apparent mean size required to manipulate the records from one table in memory is much bigger then what will be really necessary and the optimiser might chose a less performing path based on what it thinks would have be necessary for the others paths.

Having a realistic max size is also usefull to any other programmer that will come along looking at your code. If I have a variable that I want to display in a GUI, I might allocate much more space than neededd if I see that is backed by something like nvarchar(200) or nvarchar(2000) instead of nvarchar(20) if its size is never greater than that.

回答4:

Size matters

Always use the smallest data size that will accommodate the largest possible value. If a column is going to store values between 1 and 5, use tinyint instead of int.

This rule also applies to character columns. The smaller the data size, the less there is to read, so performance, over all, benefits. In addition, smaller size reduces network traffic. With newer technology, this tip seems less relevant, but don’t dismiss it out of hand. You’ll won’t regret being efficient from the get-go.

For more info visit http://www.techrepublic.com/blog/10-things/10-plus-tips-for-getting-the-best-performance-out-of-your-sql-server-data-types/