varchar actual size

  • I create a table: create table xyz (field1 varchar(8000))

    Insert a record: insert into xyz values('a')

    How much storage space have I used? I have read the description of a varchar, but still am a bit confused. I would think I just ate at least 8000 bytes + misc stuff. But 'Help' seems to indicate it may only store the actual 'a' + misc stuff. There's a bit difference after a million of records or so.

    My second question is how does SQL keep track of where records are? I don't need to know, but I'm the curious sort.

    Thanks for your time, everyone!

  • Varchars only store that space that you are actually using.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung (12/30/2008)


    Varchars only store that space that you are actually using.

    + 2 bytes to store the length of data;)

    P.

    ...and your only reply is slàinte mhath

  • Thanks.

    I've meticulously tried to get my varchars to match what I expect the data to look like. I'll now just set everything to 8000 and save myself a few seconds of thinking.

    I suppose that may be extreme, but I shouldn't get upset with other table-creators that do similar, eh?

  • klini (12/30/2008)


    Thanks.

    I've meticulously tried to get my varchars to match what I expect the data to look like. I'll now just set everything to 8000 and save myself a few seconds of thinking.

    I suppose that may be extreme, but I shouldn't get upset with other table-creators that do similar, eh?

    So, you won't mind when you get an error that you can't save data to the table because it exceeds the maximum length allowed.

    And yes, I would get upset with other table-creators that do similar. If you don't need 8000 characters to store the data, don't size the column that large. Remember, whatever size you allow is the size that is going to be used - whether or not it actually makes sense.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Piotr Rodak (12/30/2008)


    RBarryYoung (12/30/2008)


    Varchars only store that space that you are actually using.

    + 2 bytes to store the length of data;)

    P.

    Also don't forget that if you have other columns or multiple varchars and you have varchars that exceed the 8KB page limit, you'll start storing the varchar in row overflow pages.

    Interestingly, you can't store more than 8KB of fixed char columns, but you can store more than 8KB variable columns.

    Random Technical Stuff[/url]

  • klini (12/30/2008)


    Thanks.

    I've meticulously tried to get my varchars to match what I expect the data to look like. I'll now just set everything to 8000 and save myself a few seconds of thinking.

    I suppose that may be extreme, but I shouldn't get upset with other table-creators that do similar, eh?

    If that's what you are trying to achieve, have a look at VARCHAR(MAX). It might be what you are looking for.

    Random Technical Stuff[/url]

  • What the heck... no table design needed... just store it all in one big ol' nasty EAV and call it a day... just think how easy indexing will be. :P;):hehe:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • RBarry says "Varchars only store that space that you are actually using"

    but

    Jeffrey says "whatever size you allow is the size that is going to be used"

    Could I have some clarification?

  • Jeffrey meant that if you give room for a mistake to happen, it will happen. You shouldn't make VARCHAR any bigger than necessary or you stand a chance or rows being rejected when the 8060 max byte count is violated. And, no, you shouldn't use VARCHAR(MAX) if you don't have to because it has some "off page" overhead.

    Neither Barry nor Jeffrey contradicted each other.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (12/30/2008)


    Jeffrey meant that if you give room for a mistake to happen, it will happen. You shouldn't make VARCHAR any bigger than necessary or you stand a chance or rows being rejected when the 8060 max byte count is violated. And, no, you shouldn't use VARCHAR(MAX) if you don't have to because it has some "off page" overhead.

    Neither Barry nor Jeffrey contradicted each other.

    I just want to state for the record that I'm not recommending varchar(MAX) if you don't need it. However, varchar(MAX) has a number of excellent features that make it good for storing large amounts of text data.

    However, I'm not sure what is meant by "You shouldn't make VARCHAR any bigger than necessary or you stand a chance or rows being rejected when the 8060 max byte count is violated." Jeff, can you clarify? If you store more than 8KB of varchars (for instance a varchar(6000) and a varchar(5000) on one table) then you won't get that error. Instead, SQL Server will store the data in the row overflow pages. Of course if you try define more than approx. 8KB of fixed chars in a table you'll get that error...

    Note that again, for the record, I don't recommend storing greater than what you need. Having to read more than one page at a time could hurt performance.

    As they say, store only the amount that you need to store. If you can store less than 8KB of data per row, it would be better.

    Random Technical Stuff[/url]

  • ta.bu.shi.da.yu (12/30/2008)


    If you store more than 8KB of varchars (for instance a varchar(6000) and a varchar(5000) on one table) then you won't get that error. Instead, SQL Server will store the data in the row overflow pages.

    Not true... run this and see.

    CREATE TABLE #TooBig

    (

    ColA VARCHAR(5000),

    ColB VARCHAR(6000)

    )

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (12/31/2008)


    ta.bu.shi.da.yu (12/30/2008)


    If you store more than 8KB of varchars (for instance a varchar(6000) and a varchar(5000) on one table) then you won't get that error. Instead, SQL Server will store the data in the row overflow pages.

    Not true... run this and see.

    CREATE TABLE #TooBig

    (

    ColA VARCHAR(5000),

    ColB VARCHAR(6000)

    )

    Ack... crud... my bad... I was running this in SQL Server 2000... not 2005. Sorry. You don't get the same error in 2005. (Man, I gotta get out of the habit ;))

    Still, like VARCHAR(MAX), there is a bit of overhead involved with "overflow" pages. It is best to stay within the page limits of 8060 bytes.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Even though SQL Server 2005/2008 will store excess values past the 8060 limit when dealing with varchar,etc., you will get performance overhead to go and do that extra retrieve. I'd be sure I needed it rather than toss the idea of database design out the window & store everything in flipping huge, unformed, strings.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks everyone!

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply