char or varchar?

[Jay]

My understanding is that char stores a fixed number of characters even if
the string stored in the char has fewer characters, whereas varchar stores
the string with the number of characters that the string contains.

So, varchar sounds more efficient, but are there any disadvanages of using
varchar over char (eg speed and size)?

If I wish to store ISBNs (which can be 10 or 13 characters, using characters
0-9 and X) would char(13) be a good choice? I don't need to store them as
unicode, so am ignoring nchar and nvarchar, but let me know if there are any
problems with this.

 

[Nicholas Paldino [.NET/C# MVP]]

Jay,

Actually, when it comes to database operations, char fields are more
efficient, as the the character fields can be stored contiguously in memory,
and it is easy to get the data. With varchar fields, you have a redirection
in order to find the data, as the data can not be stored contiguously in
memory due to the changing size of the data.

Generally though, I wouldn't worry about this unless you have already
created your application and you know that this is a bottleneck.

As for what your ISBN numbers should be, if you are exposing the results
from the database directly without any processing, then I would say that the
varchar is better, as it return the data trimmed appropriately.

If there is a layer of indirection, then I would say to use char(13),
and then have the layer processing the data trim it before it is returned to
the user.

 

[Marc Gravell]

Can you expand on "redirection"? My understanding is that varchar is
still stored "in row"... but with a variable number of records per
page; the downside here being that this can necessitate page-shuffling
if the contents change and would overflow other records on the page
(exhausting the padding).

Marc

 

[Nicholas Paldino [.NET/C# MVP]]

Sorry, I should elaborate. In the case of SQL Server 2005, if you have
a varchar type, then the data is stored in the field with a two byte prefix
indicating the length of the varchar string. However, if the size of the
row is greater than 8060 bytes, then SQL Server might store variable length
data off-row:

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/23/644607.aspx

varchar(max) is always stored off-row.

I'm not sure how it works in SQL Server 2000 and before though.

 

[Guest]

Although this is a bit old, it still should be helpful:

http://www.sql-server-performance.com/articles/per/saving_space_p1.aspx


-- Peter
Recursion: see Recursion
site: http://www.eggheadcafe.com
unBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder: http://www.blogmetafinder.com

 

[Marc Gravell]

varchar(max) is always stored off-row.

...
I'm not sure how it works in SQL Server 2000 and before though.

Well, [n]varchar(max) didn't exist, so you had either
[n]varchar(len<=8000) or [n]text. The former is in-row, the latter is
off-row by default, but short values can be brought in-row via
TEXT_IN_ROW, essentially the same as "row-overflow" in your cited doc;
with TEXT_IN_ROW specified, short values (your choice of length within
limits) can be brought in-row without any coding changes.

I've a suspicion (without any real grounds) that [n]varchar(max) can
work similar to TEXT_IN_ROW on [n]text.

Thanks for the info,

Marc

 

[Jay]

Thanks everyone for your answers - that was very helpful, as was the
discussion that followed.

Sorry about my very late reply...

Jay

"Jay" <-> wrote in message My understanding is that char stores a fixed number of characters even if
the string stored in the char has fewer characters, whereas varchar stores
the string with the number of characters that the string contains.

So, varchar sounds more efficient, but are there any disadvanages of using
varchar over char (eg speed and size)?

If I wish to store ISBNs (which can be 10 or 13 characters, using characters
0-9 and X) would char(13) be a good choice? I don't need to store them as
unicode, so am ignoring nchar and nvarchar, but let me know if there are any
problems with this.