Using indexes with NVarchar(50) vs. NVarchar(255) column?
We have an indexed column (ModelName) which has a big importance in the table and is very similar to a "Catalog Number", but it's not the PK.
a lot of ORDER by ModelName ; WHERE ModelName etc, is being used.
The column originally started as NVarchar(50), but changed size over the time to 100, and now it's needed to be 255.
I have found many posts on "NVarchar(MAX) vs. NVarChar(N)", but I can't get a conclusive answer:
Is there a any/significant performance hit using NVarchar(255) instead of NVarchar(100) instead of NVarchar(50) specially when it comes to Indexes?
Is a shorter column size (50) better than the longer (255) in terms of performance? And can there be a special settings for such Index to improve performance?
Here is another reference provided in the comments by @a_horse_with_no_name:
Best practices for SQL varchar column length
Note answer by Ariel: https://stackoverflow.com/a/8295195/1140885
Where it says:
"Specifically, when doing sorting, larger column do take up more space, so if that hurts performance, then you need to worry about it and make them smaller."
, and in the comments:
"There are issues and limitations on indexes to consider, too. You can't have a (a,b,c,d) index when all four columns are VARCHAR(255)"
No definitive conclusion/reference to docs etc.
Answer
ON a variable length datatype the indexes will suffer if you keep accumulating data. The larger the size the more chances of combinations in the B-Tree effectively increasing the index size. At some point the index size will be too big and queries will suffer. On the other hand if you have all similar set of data getting in as ModelName there will not be much of an issue
if model names are like AAABB, AAABC, AAACC etc it wont kill your performance but the standard deviation from one another becomes high the index performance will be poor due to the sheer size