Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Unanswered: SQL EXPRESS - varchar v. varchar(max)

    I'm having issues with being able to use varchar(max) in a sqlexpress table. I can use varchar up to 100 characters and have no issues limiting it to less characters but nothing above 100 characters. I've never had this issue before. I've checked the table set up and can't find any constraints (but I may not be looking in the right places).

    Any ideas? Any help is very appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "Issues" is not a description of a problem.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am betting that (s)he is looking at the SQL Express GUI. Max needs to be used only when truly needed. By default the data will not be stored on the same page as the rest of the record. The will be a pointer to another page. That sounds like 2 reads to me instead of 1. Have'nt confirmed that though.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    By default varchar(max) data is stored in the same data page as the rest of the record. Only if the record spills over the 8K limit is the data moved to an overflow page.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I stand corrected...

    Row-Overflow Data Exceeding 8 KB

    However I have a question. Does the whole column turn to pointers or just records that exceed the limit? If the former, I have to ask what is the point of using max if you are not going to exceed the limit?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Just the individual fields go from varchar content to a pointer.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •