Using indices with an NVarchar (50) column versus NVarchar (255)?

Question

We have an indexed column ( ModelName ), which is of great importance in the table and is very similar to the "Directory Number", but it is not a PC.

a lot of ORDER by ModelName ; WHERE ModelName etc.

The column originally started as NVarchar(50) , but resized in a time to 100, and now it needed 255.

I found many posts on "NVarchar(MAX) vs. NVarChar(N)" , but I can’t get the final answer:

Is there any / significant success when using NVarchar(255) instead of NVarchar(100) instead of NVarchar(50) specifically when it comes to Indexes ?

Is shorter column size (50) better than longer (255) in terms of performance? Could there be special settings for such an Index to improve performance?

---

Here is another link provided in the comments of @a_horse_with_no_name:

Varchar SQL column length guidelines

Pay attention to Ariel's answer: stack overflow

Where does he say:

"In particular, when sorting, a larger column takes up more space, so if this affects performance, then you need to worry about it and make them smaller."

and in the comments:

"There are also problems and limitations on indexes. An index (a, b, c, d) cannot have it when all four columns are VARCHAR (255)."

There is no final conclusion / link to documents, etc.

Answer 1

In a variable variable length, indexes will suffer if you continue to accumulate data. The larger the size, the greater the chance of a combination in B-Tree effectively increasing the size of the index. At some point, the size of the index will be too large, and queries will suffer. On the other hand, if you have a similar set of data included in ModelName, there will be no problems.

if the model names are similar to AAABB, AAABC, AAACC, etc., they will not kill your performance, but the standard deviation from each other becomes high, index performance will be poor due to the huge size