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.
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...
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).
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.
nvarchar(255) (in SQL Server) stores 255 Unicode characters (in 510 bytes plus overhead).
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With