Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    Unanswered: What is the necessity for specifying a length when configuring a varchar field?

    I did a test where I created two tables--both tables had a single, varchar field.

    In one of the tables, the field length was set to 750 characters and in the other table the field length was set to 4,000 characters.

    I then wrote a small looping batch which inserted 1,000,000 identical records in both tables.


    The result was that both tables were occupying identical storage space (as viewed on the Storage page on the Properties view of the table).


    So, the question is, if you are going to specify varchar, what difference does it make that you specify the field 10 characters in length versus 4,000 characters in length, as long as whatever you specify is long-enough to hold your data?


    Are there issues here, relating to indexing, that I am overlooking?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In terms of indexing, an index cannot be longer than 9000 bytes, since that is the size of a page.

    In answer to your question it is really just a question of constraining the data. Remember that data types are just the most simple of the data constraints, and the size is a property of variable length data constraints. As such, it is predominantly to enforce logical constraints rather than an implementation issue. Of course, there would be physical consequences if you allowed 4000 characters for the country name and these were regularly all used, but the logical consequences would be more of a worry than bits on a disk.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Good points.

    Concerning the indexing, because of the 9,000 byte limit, having a compound index made of of, say, three varchar fields, if they were all sized as 4,000 characters, that would be a problem.

    In general, the main reason for constraining a varchar field is to place a database-level size constraint reflecting the applications true needs.

    Thanks.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In the scenario you describe SQL Server will create the index but also issue a warning.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Er...The limit on an index is 900 bytes per row. The page limit is 8060 bytes, I believe.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh rly? I always thought it was about the size of a page. So you can have up to 9 values per index page? Is 900 an arbitrary value they picked then?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    ... but the logical consequences would be more of a worry than bits on a disk.
    i've always thought this to be the most important criterion

    let's say you have a compound index, consisting of multiple columns

    and the first column is "large" (e.g. 4000 bytes, or whatever)

    under what circumstances will you be needing to do a WHERE column = value search? that's right, none

    in other words, wouldn't this index only find usefulness when doing WHERE column LIKE [%]value[%]

    okay, given this first "large" column as the first column in the compound index on the table rows, under what circumstances would you want the second column of the index to be "large" (e.g. 4000 bytes, or whatever)

    i can't think of a practical application, but misapplications come all too quickly to mind, and the implications of this leave me feeling a bit uneasy...



    WHERE attrname LIKE '%touchy%'
    AND attrvalue LIKE '%feely%'


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Rly

    er...I mean Really. Here it is right here. It's not all that bad, though. You were only off by an order of 10 ;-)

Posting Permissions

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