25

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)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
nicholsonjf
  • 971
  • 2
  • 11
  • 21
  • 3
    Please read this [article](http://rusanu.com/2010/03/22/performance-comparison-of-varcharmax-vs-varcharn/). – Bogdan Sahlean Nov 26 '13 at 22:45
  • Another comparison: http://sqlhints.com/2013/03/10/difference-between-sql-server-varchar-and-varcharmax-data-type/ – NoChance Nov 26 '13 at 22:50
  • @EmmadKareem that article discusses varchar (8-bit codepages), my question pertains to nvarchar (unicode compatible) – nicholsonjf Nov 27 '13 at 02:49
  • 1
    Please also read this article: [What's the Point of Using VARCHAR(n) Anymore?](https://www.simple-talk.com/sql/database-administration/whats-the-point-of-using-varchar%28n%29-anymore/) on **why it really really matters** whether you use `varchar(n)` vs. `varchar(max)` all the time ... – marc_s Nov 27 '13 at 06:22
  • Also see [is there an advantage to varchar(500) over varchar(8000)?](http://stackoverflow.com/a/5654947/73226) – Martin Smith Nov 27 '13 at 16:21
  • Is there a reason everyone is referencing articles that pertain to VARCHAR when my question is clearly inquiring about NVARCHAR? Is there no storage or performance differences between the two besides one being unicode compatible? – nicholsonjf Nov 27 '13 at 18:16
  • 1
    @JamesNicholson - `nvarchar` uses twice as much space as `varchar` except if using compression but the arguments against over declaring the width of the column are exactly the same for both. – Martin Smith Nov 29 '13 at 12:53

3 Answers3

31

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.

Ryan Nigro
  • 4,389
  • 2
  • 17
  • 23
8

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.

Sujai Sparks
  • 93
  • 4
  • This is a good start. Anything else you can think of that someone should consider when deciding between nvarchar(n) and nvarchar(max)? – nicholsonjf Nov 27 '13 at 02:52
-2

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...

Theo Anderson
  • 163
  • 1
  • 1
  • 5