1

I always design my tables with appropriate length varchar fields for the data I expect to be contained within them. I also generally define my keys to be of INT type and use these for JOINS etc as it's always given me the best performance.

I have to work on an existing project where a lot of the key columns are varchars, hence there are many indexes on such fields, which is not an issue by itself. However there are several instances where these columns are defined as varchar(255) whereas for example a varchar(10) would actually do fine for the data contained in the column. (either laziness, or perhaps the table was created via an import originally (see above - laziness!))

My reasoning has always been that having appropriately sized columns, means that SQL Server will perform better as indexes will be tighter, and more likely execution plans will be more optimised, and actual query execution will be more memory efficient. This is more based off gut feel than anything concrete and trying to find written evidence to present to allow my recommendations of changing the tables to have appropriate sized columns hasn't turned up anything I'd be happy to stand by.

Am I correct? And if so can anyone point me at some documented evidence?

chilluk
  • 217
  • 2
  • 17
  • 2
    I am voting to migrate this question to the [dba stackexchange](https://dba.stackexchange.com) site. – Igor Mar 12 '18 at 21:08
  • 1
    In short, no. There should be no impact joining a varchar(100) to a varchar(10). There will be no implicit conversions and they both take the same amount of space when storing a string like 'test'. As long as you don't use varchar(max), you should be ok. – dfundako Mar 12 '18 at 21:10
  • 1
    There is a difference (page reservations, mainly)... but it's not always as big as you might expect. – Joel Coehoorn Mar 12 '18 at 21:12
  • 1
    VARCHARs act like CHARs in the index pages (because index entries are always fixed-size). Consequently an index on a VARCHAR(255) that *could* be a VARCHAR(10) will take up significantly more space and will be significantly slower (though only when using that index) than it could be. Worse (much worse) is if it is also your table's clustered index. This answer has some deatils: https://stackoverflow.com/a/9780527/109122 – RBarryYoung Mar 12 '18 at 21:31
  • RBarryYoung - that was also my assumption regarding taking up space and the speed hit - I will follow that other thread you have linked to – chilluk Mar 12 '18 at 21:48

0 Answers0