Loading ...
Sorry, an error occurred while loading the content.
 

Re: [SQLQueriesNoCode] Re: Varchar(max) ?? SQL Server 2005 +

Expand Messages
  • Paul Livengood
    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
    Message 1 of 10 , May 21, 2009
      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@...>
      To: SQLQueriesNoCode@yahoogroups.com
      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
      part?

      Clear as mud ??

      John Warner

      -----Original Message-----
      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 +

      John;

      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)

      HTH

      Paul

      --- 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
      >
      > Thanks.
      >
      >
      > John Warner
      >

      ------------ --------- --------- ------

      Yahoo! Groups Links




      [Non-text portions of this message have been removed]
    Your message has been successfully submitted and would be delivered to recipients shortly.