Re: [SQLQueriesNoCode] Re: Varchar(max) ?? SQL Server 2005 +
- I would not worry about it, if you need the storage that is what MAX is for. I would go ahead and use it.
From: John Warner <john@...>
Sent: Thursday, May 21, 2009 6:36:45 AM
Subject: RE: [SQLQueriesNoCode] Re: Varchar(max) ?? SQL Server 2005 +
Yes, I knew about the fact I would have to apply a constraint to actually
limit things. I'm not sure my question is clear though. Assume I'm not
actually coming remotely close to 2 Billion but might on occasion exceed
8K. Is there a penalty specific to varchar(max) vs a 'regular' varchar or
perhaps with the constraint beyond the paging. Is more data in the file
consumed for the extra storage capacity or is this concern unfounded on my
Clear as mud ??
From: SQLQueriesNoCode@ yahoogroups. com
[mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of Paul Livengood
Sent: Thursday, May 21, 2009 8:01 AM
To: SQLQueriesNoCode@ yahoogroups. com
Subject: [SQLQueriesNoCode] Re: Varchar(max) ?? SQL Server 2005 +
I believe the main disadvantage would be speed. SQL 2005 has a maximum
page size of 8k. This is why in SQL 2000 you could only have
VARCHAR(8000) , or NVARCHAR(4000) . To get around this limit SQL 2005 uses
overflow pages. An overflow page is a second page with the extra data on
it. For 2,000,000 characters that is a lot of pages. The overflow pages
make it so one record is on multiple pages, not just one. This doubles (or
triples, or quadruples, etc) the amount of reads needed to query a row.
So, to keep your data to one page, and thereby help keep reads to a
minimum, the total size of your table (not just one row) should still be
below 8k. If you need to go over that I would recommend creating a second
table to store the PK and VARCHAR(MAX) fields in.
As for using VARCHAR(65535) ; that is not allowed. You can use up to 8000
or MAX. To limit above 8000 you need to create a VARCHAR(MAX) field and
then add a check constraint to make sure the data length is below your
applications max value.
ALTER TABLE [TableName
ADD CONSTRAINT [constraintName]
CHECK (DATALENGTH( [FieldName] } <= 65535)
--- In SQLQueriesNoCode@ yahoogroups. com, "John Warner" <john@...> wrote:
> What is the downside of Varchar(max) in text fields vs varchar(500) or
> some other number when the data to be stored here really is variable
> but in this case I know it will never get to 2 Billion characters or
> even remotely close?
> Adding a new table to a database and this has sort of come up moving
> an Access memo field to sql server. I think the memo field will hold
> 64K of characters so for that matter what is the harm of
> varchar(65535) v varchar(max) . Much of the data is greater than 255
> Access's text/char limit.
> Also this field will not be queried in a WHERE/JOIN type clause only
> in SELECTs
> John Warner
------------ --------- --------- ------
Yahoo! Groups Links
[Non-text portions of this message have been removed]