SQL Server Performance Forum – Threads Archive
Size of varchar
Is there a difference, in performance terms, between defining acolumn of type varchar(10) or varchar(1000), for example, (storing
data less than 10 characters in it, in both cases)?.
Sorry, if my english is not good. Thanks. Gabriel
VARCHAR data type has slightly more overhead than the CHAR data type, the amount of space saved by using VARCHAR over CHAR on variable length columns can greatly reduce I/O reads, improving overall SQL Server performance. _________
Satya SKJ
ok, I’m agree with you, but my comparison was between varchar(10) or
varchar(1000), not between varchar and char. I’m not sure if the
limit is just for validation purposes or have performance
implications too.
Sometimes I don’t want to restrict too much the length of a field,
but in the other hand, I don´t want to have a varchar(4000)
column, if it has an overhead over a varchar(100) column. Gabriel
Don’t see any implications in such case. _________
Satya SKJ
The slight storage overhead associated with varchar is associated with the presence of a varchar column or columns and not the defined size of that column. There are of course performance implications in having rows with 1000 bytes as opposed to 10 bytes with regard to the number of rows that fit on a page and thus the IO required to read those rows.
HTH Jasper Smith
I agree, my understanding is that you do not pay any performance penalty for varchars defined larger than the actual content they store. I suppose you can just think of a varchar(1000) as a ‘varchar’ type, with a constraint of 1000 on its length.
]]>