Post reply

VARCHAR indexes

  • December 9, 2010 at 8:40 am

    #228320

    Hi,

    We have a table that has been set up incorrectly and has since been indexed on the columns. The columns in question were both set up as VARCHAR(50), but should have been VARCHAR(15) & VARCHAR(5) respectively.

    We deal with large amounts (60 million per table) of data and there is a lot of data moving in and out of the table.

    My question, is if we alter the columns to the correct size, will it improve performance on lookups/inserts/deletes? Or is SQL server smart enough that the empty space does not affect the size/performance of the index?

    I'd personally leave it alone, but it is proving a bottleneck so we are trying to resolve the performance issue on the table.

    Thanks,

    Tom

  • December 9, 2010 at 9:31 am

    #1260632

    It's unlikely to change anything. Varchar data type is variable length - it is only as large as the data in it (plus 2 bytes), so a Varchar(50) that contains 5 characters only occupies 7 bytes. A varchar(15) that contains 5 bytes also occupies 2 bytes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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