Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2010
    Posts
    28

    Unanswered: Storage of Text Data Types

    Hi All

    I trying to fully understand when to use different data types in sql server.

    I want to know what Microdoft means when they say

    "Varchar is the actual length of the data entered plus 2 bytes"

    Can someone explain this by giving an example e.g. what would the storage of varchar (50) be?

    any additional information would be great.

    Thanks
    Last edited by shamas21; 01-02-14 at 16:12.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    depends on the size of the text. plus 2 bytes.
    “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.

  3. #3
    Join Date
    Jun 2010
    Posts
    28
    So what would varchar(50) be?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do you have access to either PluralSight or to Microsoft SQL Server 2012 Internals? This is trick question, since the answer for both of them ought to be "yes" without a second thought!

    The reason that I ask is because you are asking deeper and deeper questions, and you will get a lot farther a LOT faster if you follow a "well charted course" than if you randomly follow what catches your attention at the moment. It is great that you are progressing and that you've reached the point where random exploration isn't as productive as it used to be... It just means that you'll do better if you switch learning modes to allow you to absorb a lot more content faster than you would find it by following what interests you at the moment.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jun 2010
    Posts
    28
    Hi Pat

    Yes im currently on plural sight and find it very helpful. But sometimes something comes up and need further clarification. But yes plural sight is very good.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The "var" in "varchar" stands for "variable length".
    So the length is different depending on the actual data values stored.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Bear in mind that this is a SORELY abbreviated discussion of a much more complicated topic. What I'm writing is true for this specific case to the best of my knowledge, but it is only the tip of the iceberg... There is a lot more material, so you can only treat this as an overview.

    If you think about how a VARCHAR is stored in memory, it is a contiguous block of bytes. These can be as long as the VARCHAR can possibly be, plus two additional bytes but they can also be a lot smaller. For instance, if a VARCHAR(50) variable contains 'Hello, World!' then the values would be:
    Code:
    +----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+
    | 0d | 00 | 48 | 65 | 6c | 6c | 6f | 2c | 20 | 77 | 6f | 72 | 6c | 64 | 21 | ?? | ?? |
    +----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+
    The first two bytes contain the length of the string (low byte, then high byte) followed by each byte of the string. If you replace a string with another string that is the same length or smaller, the odds are pretty good that it won't move and that the remainder of the string will just sit there. If you replace a string with a string that is longer than the space allocated, then SQL Server will allocate a new string that is at least as large as the space required. Note that I'm completely ignoring how SQL Server knows how big the buffer is, etc... Those are WAY beyond what I'm willing to write here!

    When writing a row to disk, SQL Server puts all of the data into a page frame. These are tricky devils, since they accommodate things like compressed pages, etc. In the most vanilla form, these are 8060 data payloads within an 8192 byte page. There are more variations than I can easily count, each with a few dozen subtle twists.

    There are MANY "gotchas" that I've just completely danced around in this topic. You really need to visit one of the more comprehensive sources of information like the two I cited in a previous post in order to have a good understanding of this topic.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jun 2010
    Posts
    28
    Quote Originally Posted by Pat Phelan View Post
    Bear in mind that this is a SORELY abbreviated discussion of a much more complicated topic. What I'm writing is true for this specific case to the best of my knowledge, but it is only the tip of the iceberg... There is a lot more material, so you can only treat this as an overview.

    If you think about how a VARCHAR is stored in memory, it is a contiguous block of bytes. These can be as long as the VARCHAR can possibly be, plus two additional bytes but they can also be a lot smaller. For instance, if a VARCHAR(50) variable contains 'Hello, World!' then the values would be:
    Code:
    +----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+
    | 0d | 00 | 48 | 65 | 6c | 6c | 6f | 2c | 20 | 77 | 6f | 72 | 6c | 64 | 21 | ?? | ?? |
    +----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+
    The first two bytes contain the length of the string (low byte, then high byte) followed by each byte of the string. If you replace a string with another string that is the same length or smaller, the odds are pretty good that it won't move and that the remainder of the string will just sit there. If you replace a string with a string that is longer than the space allocated, then SQL Server will allocate a new string that is at least as large as the space required. Note that I'm completely ignoring how SQL Server knows how big the buffer is, etc... Those are WAY beyond what I'm willing to write here!

    When writing a row to disk, SQL Server puts all of the data into a page frame. These are tricky devils, since they accommodate things like compressed pages, etc. In the most vanilla form, these are 8060 data payloads within an 8192 byte page. There are more variations than I can easily count, each with a few dozen subtle twists.

    There are MANY "gotchas" that I've just completely danced around in this topic. You really need to visit one of the more comprehensive sources of information like the two I cited in a previous post in order to have a good understanding of this topic.

    -PatP
    Many thanks Pat. Thats very helpful.

Posting Permissions

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