Implications of nvarchar (50) vs nvarchar (max)

Question

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) 

Answer 1

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.

Answer 2

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.