char and varchar!

  • VM-723206

    SSCrazy

    Points: 2964

    Comments posted to this topic are about the item char and varchar!

  • Carlo Romagnano

    SSC-Insane

    Points: 21906

    This a question for very beginner!

  • EdVassie

    SSC Guru

    Points: 60270

    I got is right but imho all the answers as given are wrong.

    Varchar does not just use the space required for the number of characters in the row. It is important to remember that varchar has a length prefix, and that a varchar field uses the data entered plus 2 bytes. I have seen some poor database designs that have ignored this fact, with varchar(1), varchar(2), etc fields.

    Another important thing to remember is that varchar uses more space in an index than does a char column. Ther are also limitation about using varchar columns in an index if the varchar data has gone into overflow pages.

    So, there is a lot more to the differences between char and varchar than allowed for in the answers in the question. If the question had included a 'none of the above' option I would have chosen that as the answer.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • Carlo Romagnano

    SSC-Insane

    Points: 21906

    EdVassie (7/2/2009)


    I have seen some poor database designs that have ignored this fact, with varchar(1), varchar(2), etc fields.

    Everybody always should use the RIGHT type.

    varchar(1) and char(1) are not similar nor interchangeable.

    Try this:

    declare @vc varchar(1)

    ,@c char(1)

    SET @vc = ''

    SET @C = ''

    print 'varchar[' + @vc + ']'

    print 'char [' + @C + ']'

    the result is very different:

    varchar[]

    char [ ]

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    EdVassie (7/2/2009)


    Another important thing to remember is that varchar uses more space in an index than does a char column.

    This is not correct. Space usage of char and varchar in indexes is equal as space usage of char and varchar in tables. So if a varchar saves space in a table, it saves space in an index as well.

    I do agree with the rest of your post, though!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • EdVassie

    SSC Guru

    Points: 60270

    Hugo, 'calculating index key size' in BOL disagrees with what you said. I hesitate to say which source is wrong 🙂

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Hi Ed,

    I checked the BOL article you mention, but I failed to see any mention of varying length columns taking up more space. Maybe you can quote the relevant paragraph(s)? Also, what version of Books Online are you using? (Me, I'm using the April 2006 version of SQL 2005's BOL, but I also double-checked in the online SQL 2008 version of BOL at MSDN).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • EdVassie

    SSC Guru

    Points: 60270

    I'm sure I clicked in 'calculating index key size' when I looked at BOL, but the info is actually in 'Estimating the size of a nonclustered index'. I am using the SQL 2008 May 2009 BOL.

    BOL is saying there is extra overhead if any varying length fields appear in an index, plus overhead for every varying length column.

    Variable_Key_Size = 2 + (Num_Variable_Key_Cols x 2) + Max_Var_Key_Size

    Max_Var_Key_Size also has its own calculation

    The overhead is not much, but more than the 'correct' answer to the question implied.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Hi Ed,

    Your calculation is correct. And you'll find the exact same calculation in the topics on estimating the size of clustered indexes and heaps (except that in those, ALL columns are counted, not just those that are in the index). This confirms my point that space usage of varchar is the same in tables and indexes.

    Note that this formula gives the maximum length. The actual length will be the actual amount of characters plus 2 (*). For non-Unicode characters, it's safe to say that if the average actual length is less than the maximum length by 2 or more characters, you'll save space in both table and indexes by using varchar rather than char.

    (*) For simplicity sake, I leave out the extra 2 bytes of overhead for a row that includes any varying length columns, since they are not per column but per row.

    Please note that I only disagreed with one specific part of your post, that seemed to imply that varchar takes more space in an index than it does in the table. I explicitly added that I do agree with the rest!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Alvin Ramard

    SSC-Forever

    Points: 41190

    Carlo Romagnano (7/2/2009)


    This a question for very beginner!

    I agree, but at least 9 people (at the time of this posting) said there was no difference between the 2.

    You'd be surprised how many people have been working with SQL Server for quite a while and still do not understand the difference.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • SanjayAttray

    SSChampion

    Points: 13157

    Very basic. But, still 16 people got it wrong as of now. Amazing. :hehe:

    SQL DBA.

  • Toreador

    SSChampion

    Points: 11248

    SanjayAttray (7/2/2009)


    Very basic. But, still 16 people got it wrong as of now. Amazing. :hehe:

    34 and counting...

  • Bhavesh_Patel

    SSCrazy

    Points: 2259

    Easy points for taking.



    [font="System"]Bhavesh Patel[/font]

    http://bhaveshgpatel.wordpress.com/
  • kalyani.k478

    Default port

    Points: 1400

    +1:) thanks

Viewing 14 posts - 1 through 14 (of 14 total)

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