Detail needed about varchar

I'm trying to understand past the basics of varchar...
1) Does a varchar(1000) take more storage in the actual database file than a
varchar(500)? (assume each contains a 300 byte string). If not, why wouldn't
I always use varchar(8000)?
2) Does a varchar(10) take more space than a char(10)? (is there overhead
specifying the actual length in varchar?)
3) Are there any performance penalties (and specifically, what are they)
when using varchar instead of char?
1. No, no more space. Lazy? Use only the space you need and use the
appropriate column data types and sizes.
2. Yes, every varchar in a table uses an additional 2 bytes to describe the
actual length of the data stored.
3. If you have 10 rows per page with a row size of 500 bytes. And you update
a row where the varchar that used to contain 10 bytes is now 500 bytes, the
whole page has to be re-arranged for this data to fit in. This may even
cause the page to be split and incur a lot of extra IO. SQL always has to
guess how much of the variable space will be used up when it does page
allocations. When a able is created, all the fixed length data types are
stored on the left and all the variable length data types are stored after
that.
Do yourself a favor and create a table with 1'000'000 rows in it, and then
create an index on a varchar column and check the database growth.
Do the same with a properly sized char column and compare the space
requirements of the table. Create clustered and non-clustered indexes.
If you ever want to know how well a system is written, it is easy, look at
the DB. If it is clean and very well though out with appropriate data types,
you generally know the developer knew hat he was doing and took pride in his
work.
Get yourself Kalen Delaney's book "Inside SQL 2000" (MS Press). Once you
read that, you will change your mind how you do DB design.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Lig Reffej" <Lig Reffej@.discussions.microsoft.com> wrote in message
news:1C986552-89FF-4C3B-912D-9C58C3F6466F@.microsoft.com...
> I'm trying to understand past the basics of varchar...
> 1) Does a varchar(1000) take more storage in the actual database file than
a
> varchar(500)? (assume each contains a 300 byte string). If not, why
wouldn't
> I always use varchar(8000)?
> 2) Does a varchar(10) take more space than a char(10)? (is there overhead
> specifying the actual length in varchar?)
> 3) Are there any performance penalties (and specifically, what are they)
> when using varchar instead of char?
>
|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23TvjU4DyEHA.2600@.TK2MSFTNGP09.phx.gbl...
[vbcol=seagreen]
> "Lig Reffej" <Lig Reffej@.discussions.microsoft.com> wrote in message
> news:1C986552-89FF-4C3B-912D-9C58C3F6466F@.microsoft.com...
than[vbcol=seagreen]
> a
> wouldn't

> 1. No, no more space. Lazy? Use only the space you need and use the
> appropriate column data types and sizes.
The question is whether varchar(8000) -is- actually the most appropriate.
Specifying the length of a character column is in many cases a matter of
predicting the future. E.g. what is the maximum last name that will needed
to be held by this system?
I think Lig's point might be, why try to predict it?
|||Mark Wilden wrote:
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:%23TvjU4DyEHA.2600@.TK2MSFTNGP09.phx.gbl...
>
> The question is whether varchar(8000) -is- actually the most
> appropriate.
> Specifying the length of a character column is in many cases a matter
> of predicting the future. E.g. what is the maximum last name that
> will needed to be held by this system?
> I think Lig's point might be, why try to predict it?
If only because row density is important. If only because you can only
fit 8060 bytes in a row and using a single varchar(8000) leaves 60 bytes
for other data should the column be filled.
If you can't predict the length of a character column with any accuracy,
use text/ntext.
David Gugick
Imceda Software
www.imceda.com
|||"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:#TvjU4DyEHA.2600@.TK2MSFTNGP09.phx.gbl:

> 1. No, no more space. Lazy? Use only the space you need and use the
> appropriate column data types and sizes.
> 2. Yes, every varchar in a table uses an additional 2 bytes to
> describe the actual length of the data stored.
Is the additional 2 bytes per row, page, table or what?

> 3. If you have 10 rows per page with a row size of 500 bytes. And you
> update a row where the varchar that used to contain 10 bytes is now
> 500 bytes, the whole page has to be re-arranged for this data to fit
> in. This may even cause the page to be split and incur a lot of extra
> IO. SQL always has to guess how much of the variable space will be
> used up when it does page allocations. When a able is created, all the
> fixed length data types are stored on the left and all the variable
> length data types are stored after that.
> Do yourself a favor and create a table with 1'000'000 rows in it, and
> then create an index on a varchar column and check the database
> growth. Do the same with a properly sized char column and compare the
> space requirements of the table. Create clustered and non-clustered
> indexes.
> If you ever want to know how well a system is written, it is easy,
> look at the DB. If it is clean and very well though out with
> appropriate data types, you generally know the developer knew hat he
> was doing and took pride in his work.
> Get yourself Kalen Delaney's book "Inside SQL 2000" (MS Press). Once
> you read that, you will change your mind how you do DB design.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
|||JTC ^..^ wrote:
> "Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
> news:#TvjU4DyEHA.2600@.TK2MSFTNGP09.phx.gbl:
>
>
> Is the additional 2 bytes per row, page, table or what?
>
Additional 2 for each varchar
David Gugick
Imceda Software
www.imceda.com
|||Just to be crystal clear on this, a varchar(10) with 10 characters stored
will take 2 more bytes than a char(10). Because the varchar is variable
length, we need to store its length in the record, hence the overhead is per
column.
If the varchar(10) only stores 1 char, then its total size will be 3 bytes,
less than the 10 bytes consumed by the fixed size char(10) - as fixed size
chars are padded up to their declared size always.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OSajStMyEHA.2260@.TK2MSFTNGP15.phx.gbl...
> JTC ^..^ wrote:
>
> Additional 2 for each varchar
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Paul S Randal [MS] wrote:
> Just to be crystal clear on this, a varchar(10) with 10 characters
> stored will take 2 more bytes than a char(10). Because the varchar is
> variable length, we need to store its length in the record, hence the
> overhead is per column.
> If the varchar(10) only stores 1 char, then its total size will be 3
> bytes, less than the 10 bytes consumed by the fixed size char(10) -
> as fixed size chars are padded up to their declared size always.
> Regards.
I may have mispoken. Since I don't have time right now to dig into the
technical books, I'll leave to another user to post the correct results.
David Gugick
Imceda Software
www.imceda.com