nvarchar / varchar(MAX) question

Hi all,
I'm curious about the efficiency of the varchar datatype in general. I
understand the difference between varchar and nvarchar (unicode), and
I understand what the new nvarchar(max) is in sql 2005. What I don't
understand is exactly how varchar uses server resources. Here's an
example to explain my confusion.
Let's say I have a database with a column that is varchar(200). Let's
say that users cannot enter in data over 100 characters long due to a
restriction from a client web app. This means I could have used
varchar(100). Since varchar (unlike char) can grow and shrink as
needed, have I lost anything by using varchar(200) instead of
varchar(100)? Similarly, what about nvarchar(MAX). Am I loosing
performance/storage by using this data type if my data is not going to
be that large?
Thank you!I would not use nvarchar(max) on an nvarchar(200) column. It adds 2 bytes
per row. If you have "large value types out of row" turned off, it should
act the same as normal nvarchar but there may be other internal factors.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<mattdaddym@.gmail.com> wrote in message
news:b61b9ed4-7e6d-48f0-a533-ae2d2879cb46@.e4g2000hsg.googlegroups.com...
> Hi all,
> I'm curious about the efficiency of the varchar datatype in general. I
> understand the difference between varchar and nvarchar (unicode), and
> I understand what the new nvarchar(max) is in sql 2005. What I don't
> understand is exactly how varchar uses server resources. Here's an
> example to explain my confusion.
> Let's say I have a database with a column that is varchar(200). Let's
> say that users cannot enter in data over 100 characters long due to a
> restriction from a client web app. This means I could have used
> varchar(100). Since varchar (unlike char) can grow and shrink as
> needed, have I lost anything by using varchar(200) instead of
> varchar(100)? Similarly, what about nvarchar(MAX). Am I loosing
> performance/storage by using this data type if my data is not going to
> be that large?
> Thank you!|||> Let's say I have a database with a column that is varchar(200). Let's
> say that users cannot enter in data over 100 characters long due to a
> restriction from a client web app. This means I could have used
> varchar(100). Since varchar (unlike char) can grow and shrink as
> needed, have I lost anything by using varchar(200) instead of
> varchar(100)?
No. However, keep in mind that your client app is not the only way to get
data into that table! If you only want 100 characters, then enforce it by
using the correct data type (or at the very least a check constraint). You
can update both database and client app(s) later if you need to expand the
data type to accommodate more characters.
> Similarly, what about nvarchar(MAX). Am I loosing
> performance/storage by using this data type if my data is not going to
> be that large?
I would never use a MAX type unless I *knew* I was going to require capacity
beyond the 4K or 8K limit. By default these values are stored off-row, so
that can affect efficiency and I/O issues if the query has to go all over
the place to assemble rows. You can override this setting but, as Jason
points out, there may very well be other internal factors, in addition to
the fact that you can't index, etc.|||MAX columns can be in indexes as INCLUDED columns.
Not that I'm recommending it, but I want to make sure we're clear here.
I do agree with what Aaron said.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eOdbHM3ZIHA.5208@.TK2MSFTNGP04.phx.gbl...
...
> I would never use a MAX type unless I *knew* I was going to require
> capacity beyond the 4K or 8K limit. By default these values are stored
> off-row, so that can affect efficiency and I/O issues if the query has to
> go all over the place to assemble rows. You can override this setting
> but, as Jason points out, there may very well be other internal factors,
> in addition to the fact that you can't index, etc.
>|||Sounds good. Thank you everyone.
On Feb 4, 5:45=A0pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> MAX columns can be in indexes as INCLUDED columns.
> Not that I'm recommending it, but I want to make sure we're clear here.
> I do agree with what Aaron said.
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://blog.kalendelan=
ey.com
> "Aaron Bertrand [SQL Server MVP]" <ten...@.dnartreb.noraa> wrote in messag=enews:eOdbHM3ZIHA.5208@.TK2MSFTNGP04.phx.gbl...
> ...
>
>
> > I would never use a MAX type unless I *knew* I was going to require
> > capacity beyond the 4K or 8K limit. =A0By default these values are store=d
> > off-row, so that can affect efficiency and I/O issues if the query has t=o
> > go all over the place to assemble rows. =A0You can override this setting=
> > but, as Jason points out, there may very well be other internal factors,=
> > in addition to the fact that you can't index, etc.- Hide quoted text -
> - Show quoted text -|||Sure, I should have been more explicit, can't index in the traditional way
(e.g. CREATE INDEX foo ON table.column).
> MAX columns can be in indexes as INCLUDED columns.sql