Menu
What are the general storage and performance differences between the below two tables if their only difference is nvarchar(50) vs. nvarchar(max) and the strings in each field range from 1 to 50 characters? This is in SQL Server 2005.
TABLE 1
firstname nvarchar (50) lastname nvarchar (50) username nvarchar (50) TABLE 2
firstname nvarchar (max) lastname nvarchar (max) username nvarchar (max) 
461
You cannot create an index on an nvarchar(MAX) column. You can use full-text indexing, but you cannot create an index on the column to improve query performance. For me, this seals the deal...it is a definite disadvantage to always use nvarchar(MAX).
Strictly speaking the MAX types will always be a bit slower than the non-MAX types, see Performance comparison of varchar(max) vs. varchar(N). But this difference is never visible in practice, where it just becomes noise in the overall performance driven by IO. Your main concern should not be performance of MAX vs.
An nvarchar datatype use 2 bytes per character. So if you use nvarchar(50) it uses a maximum of 100 bytes according to the size of your data.while declaring the parameteres you are specifying the length of your character variable and not it's actual storage size. So you should put 50.
Please, note that: nvarchar data type is variable-length unicode string data, but max indicates that the maximum storage size is 2^31-1 bytes (2 GB).
If you are guaranteed to have strings between 1 and 50 characters, then the same query run across strings of up-to-length X will run faster using varchar(X) vs. varchar(MAX). Additionally, you can't create an index on a varchar(MAX) field.
Once your rows have values above 8000 characters in length, then there are additional performance considerations to contend with (the rows are basically treated as TEXT instead of varchar(n)). Though this isn't terribly relevant as a comparison since there is no varchar(N) option for strings of length over 8000.
109
First and foremost, you won't be able to create indexes on the (max) length columns. So if you are going to store searchable data, ie., if these columns are going to be part of the WHERE predicate, you may not be able to improve the query performance. You may need to consider FullText Search and indexes on these columns in such a case.
33
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
