VARCHAR vs. CHAR performance
Schroeder, Colin
I'm trying to determine whether to make extensive use of VARCHAR columns
in a set of data interface tables. Some of these tables will comprise
up to 100 columns, half of them potentially VARCHAR(35) to VARCHAR(80).
I'm concerned about the possible performance impact of this design.
Do you have any direct experience with this type of VARCHAR usage,
or know any specifics about VARCHAR vs. CHAR storage?
TIA,
Colin
Roy Harvey
Colin,
If the columns are really going to have varying length data, use
varchar. The difference in overhead is small, and the saving in disk
space - and therefor I/O - over char can be significant.
What concerns me more is the "up to 100 columns" part. SQL Server
currently has a maximum row size of 1962 bytes.
Roy
Brian Moran
If the data is changed regulary... varchar might contribute to deferred
updates being used rather than a more effecient strategy and could
contribute to excessive page splits during update processes...
--
Brian Moran
Check out my monthly SQL column
in Windows NT Magazine!
Roy Harvey <76300...@compuserve.com> wrote in message
<3552f2dd....@207.68.144.15>...
Neil Pike
Colin,
Most of the issues with varchars and updates are long since resolved.
Unless you change the length of a row by more than 50% then SQL won't
delete/insert to do the update.
So if the data in the field really is very variable, then yes, use
varchars and save space - pack more data on the page and improve
performance that way.
Neil Pike MVP/MCSE
Protech Computing Ltd
Schroeder, Colin
Thanks for the info on overhead and I/O...also,
thanks for pointing out the row size limit! I had overlooked it.
Tom Scheeler
Brian Moran wrote in message ...
>If the data is changed regulary... varchar might contribute to deferred
>updates being used rather than a more effecient strategy and could
>contribute to excessive page splits during update processes...
>
>--
How would this be? I can't figure how this would occur.
Thanks,
Tom Scheeler