Post reply

Why do people use CHAR(n) to put variable length strings into the column?

  • June 8, 2016 at 2:37 pm

    #327436

    I see this a lot where I work now. People defining columns as CHAR(n) (or NCHAR(n)). Then they store variable length strings into those fields. Why do people do that? Why not use VARCHAR(n) (or NVARCHAR(N))?

    Kindest Regards, Rod Connect with me on LinkedIn.

  • June 9, 2016 at 5:22 am

    #1884082

    A CHAR will always be the same length. That may not be what they really want, most likely it is a hold over from previous (older) way of doing it.

  • June 9, 2016 at 5:25 am

    #1884083

    If n is small, it can be more efficient to use char(n). The storage requirement for varchar(n) includes a two-byte overhead to handle the variable length.

    John

  • June 9, 2016 at 7:53 am

    #1884106

    Multiple possible reasons:

    1. They don't know better and don't have a good reason

    2. If the length variation falls in certain patterns, fixed-width stores more efficiently because of the 2-byte variable-length metadata overhead

    3. Fixed-width columns reduce page-splits and clustered index fragmentation because they don't suddenly get updated to a length that requires moving the row to a fresh page

    There may be other reasons, but those are the ones I can think of easily.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 4 posts - 1 through 3 (of 3 total)

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