Re: Char and Varchar

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Steve Z (szlamany_at_antarescomputing_no_spam.com)
Date: 05/12/04 

Date: Wed, 12 May 2004 09:36:31 -0400

Hey guys, he was asking how it was stored - it's always good to know what
goes on behind the scenes.

Would you ever create a table with lets say 100 single character VARCHAR
fields? Would there be value to that, if the columns were guaranteed to
have a character in them (ie. not a space).

How would a primary clustered key behave if it was built on a VARCHAR field
vs. a CHAR field?

And, please explain to me how the 100th VARCHAR fields byte position can be
determined in a buffer of data without reading each of the prior 99 VARCHAR
control fields? Without knowing the length of each of the prior 99 data
elements in that row, how could the starting byte of the 100th element be
determined? Since the length of the VARCHAR is limited to 8000 byte - that
takes 14 of the 16 bits in the 2-byte control field. So, I can assume from
that fact that the 2-byte control field is not storing the "position" of the
element, but simply the length of each element.

Inside MS SQL Server 2000, by Kalen Delany, on page 230 states that VARCHAR
are most appropriate when you expect significant differences in the lengths
of the data in the column and when the data length in the column wont
frequently be changed. Changing the length frequently would required
rebuilding the data page - right?

If changing an element to full size causes it to no longer fit on the data
page, then page splits occur - that's alot of work for the engine to handle.

Page 233 indicates that "no one answer is right" and "If you understand the
tradeoffs; you'll be able to make the best choice".

"Jacco Schalkwijk" <NOSPAMjaccos@eurostop.co.uk> wrote in message
news:e3a8hrAOEHA.3988@tk2msftngp13.phx.gbl...
> I agree. These kind of considerations are pretty much irrelevant for
> performance compared to proper database design, query writing and
indexing.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
>
> "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
> news:dbp3a0hd0808238thks1lrjabd12umgfqv@4ax.com...
> > On Tue, 11 May 2004 17:11:16 -0400, Steve Z wrote:
> >
> > >I believe that I've also read in this forum that all the CHAR columns
are
> > >physically arranged and stored at the begining of the data block,
> regardless
> > >of there position in the DDL.
> > >
> > >After the CHAR columns, the VARCHAR columns come next - with the 2
extra
> > >bytes to store the "end byte", or basically the next starting offset of
> the
> > >next VARCHAR column.
> > >
> > >Access to the CHAR columns would then be faster than access to the
> VARCHAR
> > >columns, as the byte positions are fixed for each record. Any access
to
> > >VARCHAR columns requires "byte-offset" checking for each column, one at
a
> > >time.
> >
> > Hi Steve,
> >
> > Almost true. In fact, all fixed-length columns are stored at the
> > beginning of the data block and all variable-length columns at the
> > end. Access to a varchar column does not imply looping through all
> > other varchar columns stored before the column you need (as might be
> > concluded from your post); the end position of whatever is stored
> > before the varchar column being sought and the end position of that
> > column itself can be found directly.
> >
> > This does introduce a little bit of overhead in CPU use and reading
> > memory, but this is compensated many times by the reduced physical I/O
> > (see Alex' message)
> >
> > Best, Hugo
> > --
> >
> > (Remove _NO_ and _SPAM_ to get my e-mail address)
>
>

Relevant Pages

  • Re: Char and Varchar
    ... Would you ever create a table with lets say 100 single character VARCHAR ... determined in a buffer of data without reading each of the prior 99 VARCHAR ... takes 14 of the 16 bits in the 2-byte control field. ... Inside MS SQL Server 2000, by Kalen Delany, on page 230 states that VARCHAR ...
    (microsoft.public.sqlserver.server)
  • Re: Conversion error
    ... You canimport both types of data into a varchar column now. ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... www.SQLDTS.com - The site for all your DTS needs. ... >>character data has to be one or the other. ...
    (microsoft.public.sqlserver.dts)
  • Re: MSDE database too big
    ... It seems strange that I have so much data ... MsgId varchar 200 ... >> Tibor Karaszi, SQL Server MVP ... >>> Guoqi Zheng ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Question for Conversion
    ... SQL Server MVP ... > convert the varchar column to an integer because integer has a higher data ... > comparison rules. ... >> Error converting data type varchar to int. ...
    (microsoft.public.sqlserver.programming)
  • Re: Very slow distinct select
    ... > smalldatetime TIME_STAMP ... quite something to win by changing char to varchar. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)