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.

[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/sqlserverstora...23/644607.aspx

varchar(max) is always stored off-row.

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

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Marc Gravell" <ma**********@gmail.comwrote in message
news:11*********************@o80g2000hse.googlegro ups.com...

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

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

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

http://www.sql-server-performance.co..._space_p1.aspx
-- Peter
Recursion: see Recursion
site: http://www.eggheadcafe.com
unBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder: http://www.blogmetafinder.com

"Marc Gravell" wrote:

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

[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 news:%2******************@TK2MSFTNGP03.phx.gbl...
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.