9

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.

Community
  • 1
  • 1
ZigiZ
  • 2,480
  • 4
  • 25
  • 41
  • There is no difference between `nvarchar(42)`, `nvarchar(255)` or `nvarchar(639)` in terms of performance. See also here: http://stackoverflow.com/q/8295131/330315 –  Jan 23 '14 at 14:05
  • @a_horse_with_no_name, Thanks for the link, but, does `NVarchar` and `Varchar` perform the same? My question is specific to `NVarchar`. – ZigiZ Jan 23 '14 at 14:15
  • @a_horse_with_no_name, Also, the question/answer you provided (which is very interesting +1) does not address Indexes at all. Just in comments where it suggested that `"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)"`, and `"Specifically, when doing sorting, larger column do take up more space"`... – ZigiZ Jan 23 '14 at 14:26
  • Indexes are only affected if you do store more data in the column. An index on a `varchar(10)` and `varchar(100)` column will be the same if it stores the same data. You should see the length restriction on a varchar/nvarchar column more like a constraint than a technical configuration. –  Jan 23 '14 at 14:30
  • 3
    @a_horse_with_no_name: but on a `nvarchar(639)` column, you wouldn't be able to create an index, since the max. size of that column is larger than the **900 byte** limit on an index entry. Just saying. ... :-) – marc_s Jan 23 '14 at 14:38
  • @marc_s, exactly what I was thinking... – ZigiZ Jan 23 '14 at 14:40
  • @marc_s: good point (I'm more used to Oracle and Postgres where this isn't a problem) –  Jan 23 '14 at 14:46

1 Answers1

2

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

Lin
  • 633
  • 8
  • 26