Indexes with datatype char

  • Hi guys!

    I have an question, in my environment I have one table that the most of columns is char datatype.

    But some columns store numbers and one of this columns have a index.

    I need know the negative points about a index with datatype char.

    Att,

    Douglas.

    []'s
    Douglas R. Oliveira

  • If the column is a char, the index is a char.

    The downside is that you can't work with the values as numbers without casting them, which takes resources. You also may have issues with some functions if you are expecting numbers and have characters in there.

  • I'm not worried one way or another about an index on a CHAR column. However, storing numbers in string columns is very concerning. You're going to hit issues when you go to manipulate or retrieve those values if you attempt to treat them as numbers. Further, if you treat what's stored as a string as a number instead, you will get index scans where you should see index seeks. These will be caused by either your explicit or SQL Server's implicit conversion. That may make the index seem useless when, in fact, it's the code.

    "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

  • Hi Steve,

    Thanks for your reply.

    I understand, but what I need know is in a column where store just numbers if that is more performative if it was int datatype. Also, the size store in the disk is more if I utilize char.

    []'s
    Douglas R. Oliveira

  • The size of the index really depends on the size of the CHAR field and the size of the number field you're comparing. If the CHAR is CHAR(2) and you're only ever storing '12' in it, it'll be smaller than a BIGINT storing 12 or '12' because the CHAR(2) is 2 bytes in size but the BIGINT is 8. However, if you make a CHAR field big enough to store a BIGINT it would have to be CHAR(19) in order to hold the same size number, but the BIGINT would still only be 8.

    In general practice, always store data as the data type it should be. If it's a character field, store it as one. If it's something else, such as a number, store it as a number field.

    "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

  • I'd follow Grant's recommendations and I'd agree with his explanations.

Viewing 6 posts - 1 through 5 (of 5 total)

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