Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 680
ZigiZ Avatar asked Jan 23 '14 13:01

ZigiZ


People also ask

What is the difference between nvarchar 50 and nvarchar Max?

nvarchar max is for columns up to 2GB. So essentially it takes up more resources. You are better off using the nvarchar(50) if you know you aren't going to need that much space. each character is about 2 bytes so with 2 GB thats 1 billion characters...

What does nvarchar 50 mean?

1. Hmmm. On another MS site I did see this comment from an MS MVP which seems to contradict what you're saying: Note that nvarchar(50) does not mean 50 characters - it means 50 double-bytes. Thus, you can only store 25 characters that requires 4 bytes in nvarchar(50).

When should we use nvarchar data type for a column?

Use nvarchar when the sizes of the column data entries vary considerably. Use nvarchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 4,000 byte-pairs.

What nvarchar 255?

nvarchar(255) (in SQL Server) stores 255 Unicode characters (in 510 bytes plus overhead).


1 Answers

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

like image 155
Lin Avatar answered Nov 01 '22 15:11

Lin