CHAR vs. VARCHAR

What are the pros & cons of each datatype (char and varchar)?
I have several reference (lookup) tables that use the varchar. Would there
be any reason to convert these to char datatypes?Hi Wes,
It depends what you are doing, if your data is of fixed length, say a 8
letter code then use CHAR, there isn't the overhead (abeit small) of keeping
track of the varying length.
Varchar is good for text that is of varying length, for instance comments,
subject, titles etc... and can save significant space, if you made a title
char(500) then all the rows would be 500 bytes for that column (a lot of
wasted space).
Enter nvarchar and nchar; these are the recommended types to use in SQL
Server now and some things in Integration Services like the text extraction
require them. nchar/nvarchar stores 2 bytes per character and is for unicode
character sets. Personally, i dislike it as the systems i use aren't going
to require the 2 bytes, but for big multi-national stuff its the way
forward.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:06343322-86C2-444F-8149-7AA05F4A32DA@.microsoft.com...
> What are the pros & cons of each datatype (char and varchar)?
> I have several reference (lookup) tables that use the varchar. Would
> there
> be any reason to convert these to char datatypes?
>|||http://www.aspfaq.com/2354
http://tinyurl.com/cvtjm
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:06343322-86C2-444F-8149-7AA05F4A32DA@.microsoft.com...
> What are the pros & cons of each datatype (char and varchar)?
> I have several reference (lookup) tables that use the varchar. Would
> there
> be any reason to convert these to char datatypes?
>|||Tony,
You say that nchar & nvarchar are the recommended types now. Does this have
something to do with Sql Server 2005? If not, then why is this the
recommendation?
"Wes" wrote:

> What are the pros & cons of each datatype (char and varchar)?
> I have several reference (lookup) tables that use the varchar. Would ther
e
> be any reason to convert these to char datatypes?
>|||To add, fixed length datatypes internally consume the whole defined size
regardless of what you actually store in them. Variable length datatypes
physically consume only what you store in them, plus 2 bytes per column used
as an offset.
When you modify a value of a fixed type value, there will never be a need
for the storage space to expand. When you modify a variable type value, to a
longer one, it will need to physically expand the storage space, which might
result in a page split if the row resides in an index (clustered or
nonclustered), and there's no room for the expanded row in the page. If the
table is a heap (no clustered index), SQL Server will need to move the row
to a new location and leave a forwarding pointer in the original slot.
So generally speaking, in terms of modifications, fixed length types are
more appropriate.
On the other hand, fixed length types typically consume more space because
the always utilize the defined size. So retrieval of data typically results
in less I/O with variable length types.
So generally speaking, in terms of retrieval, variable length columns are
more appropriate.
Of course, in mixed systems where you do both modifications and retrievals
you need to prioritize what's more important to you, and in which types of
activities the systems suffers more.
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:06343322-86C2-444F-8149-7AA05F4A32DA@.microsoft.com...
> What are the pros & cons of each datatype (char and varchar)?
> I have several reference (lookup) tables that use the varchar. Would
> there
> be any reason to convert these to char datatypes?
>|||> You say that nchar & nvarchar are the recommended types now. Does this
> have
> something to do with Sql Server 2005? If not, then why is this the
> recommendation?
Because people are finally realizing that not all data is American, and does
not fit nicely in the character set support by non-Unicode data types.|||examnotes <Wes@.discussions.microsoft.com> wrote in
news:CE34A109-71C6-40B2-BEB1-1EE1F524E14E@.microsoft.com:

> You say that nchar & nvarchar are the recommended types now. Does
> this have something to do with Sql Server 2005? If not, then why is
> this the recommendation?
nchar and nvarchar is Unicode, and thus allows for storing character data
from other languages than English without any trouble. For instance, most
of you guys (Except Sommarskog) could possible have troble saving my
surname using char or varchar :)
When using unicode you can save information with different character sets,
as for instance nordic (my surname), gr and cyrillic. Of course, at the
cost of some extra bytes.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||I should also add that since these are lookup tables which are very small
anyway there are special considerations. If the typical types of access
methods against those are index s operations, read performance won't
really be affected by the choice of fixed/dynamic columns.
Also, comparing the physical I/O against the data tables vs. the lookup
tables, the lookups' part is typically very small.
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:OaSbj1y3FHA.128@.tk2msftngp13.phx.gbl...
> To add, fixed length datatypes internally consume the whole defined size
> regardless of what you actually store in them. Variable length datatypes
> physically consume only what you store in them, plus 2 bytes per column
> used as an offset.
> When you modify a value of a fixed type value, there will never be a need
> for the storage space to expand. When you modify a variable type value, to
> a longer one, it will need to physically expand the storage space, which
> might result in a page split if the row resides in an index (clustered or
> nonclustered), and there's no room for the expanded row in the page. If
> the table is a heap (no clustered index), SQL Server will need to move the
> row to a new location and leave a forwarding pointer in the original slot.
> So generally speaking, in terms of modifications, fixed length types are
> more appropriate.
> On the other hand, fixed length types typically consume more space because
> the always utilize the defined size. So retrieval of data typically
> results in less I/O with variable length types.
> So generally speaking, in terms of retrieval, variable length columns are
> more appropriate.
> Of course, in mixed systems where you do both modifications and retrievals
> you need to prioritize what's more important to you, and in which types of
> activities the systems suffers more.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> Join us for the SQL Server 2005 launch at the SQL W in Israel!
> [url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
>
> "Wes" <Wes@.discussions.microsoft.com> wrote in message
> news:06343322-86C2-444F-8149-7AA05F4A32DA@.microsoft.com...
>|||Great feedback from everyone. Thanks.
"Itzik Ben-Gan" wrote:

> To add, fixed length datatypes internally consume the whole defined size
> regardless of what you actually store in them. Variable length datatypes
> physically consume only what you store in them, plus 2 bytes per column us
ed
> as an offset.
> When you modify a value of a fixed type value, there will never be a need
> for the storage space to expand. When you modify a variable type value, to
a
> longer one, it will need to physically expand the storage space, which mig
ht
> result in a page split if the row resides in an index (clustered or
> nonclustered), and there's no room for the expanded row in the page. If th
e
> table is a heap (no clustered index), SQL Server will need to move the row
> to a new location and leave a forwarding pointer in the original slot.
> So generally speaking, in terms of modifications, fixed length types are
> more appropriate.
> On the other hand, fixed length types typically consume more space because
> the always utilize the defined size. So retrieval of data typically result
s
> in less I/O with variable length types.
> So generally speaking, in terms of retrieval, variable length columns are
> more appropriate.
> Of course, in mixed systems where you do both modifications and retrievals
> you need to prioritize what's more important to you, and in which types of
> activities the systems suffers more.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> Join us for the SQL Server 2005 launch at the SQL W in Israel!
> [url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
>
> "Wes" <Wes@.discussions.microsoft.com> wrote in message
> news:06343322-86C2-444F-8149-7AA05F4A32DA@.microsoft.com...
>
>|||Ole,
But the non-Unicode Latin1 datatypes *does* support the Nordic characters, a
long with the "western
European" characters.
(But you will of course run into problems when you get into eastern Europe,
and of course Russia,
Asia etc.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns9701E9167880Folekristianbangaas@.
207.46.248.16...
> examnotes <Wes@.discussions.microsoft.com> wrote in
> news:CE34A109-71C6-40B2-BEB1-1EE1F524E14E@.microsoft.com:
>
> nchar and nvarchar is Unicode, and thus allows for storing character data
> from other languages than English without any trouble. For instance, most
> of you guys (Except Sommarskog) could possible have troble saving my
> surname using char or varchar :)
> When using unicode you can save information with different character sets,
> as for instance nordic (my surname), gr and cyrillic. Of course, at the
> cost of some extra bytes.
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging