Are there any disadvantages to always using nvarchar(MAX)?

legacy system support. If you have a system that is using the data and it is expected to be a certain length then the database is a good place to enforce the length. This is not ideal but legacy systems are sometime not ideal. =P

If all of the data in a row (for all the columns) would never reasonably take 8000 or fewer characters then the design at the data layer should enforce this.

The database engine is much more efficient keeping everything out of blob storage. The smaller you can restrict a row the better. The more rows you can cram in a page the better. The database just performs better when it has to access fewer pages.

A reason NOT to use max or text fields is that you cannot perform [online index rebuilds][1] i.e. REBUILD WITH ONLINE= ON even with SQL Server Enterprise Edition.


[1]:

[To see links please register here]

The job of the database is to store data so that it can be used by the enterprise. Part of making that data useful is ensuring that it is meaningful. Allowing someone to enter an unlimited number of characters for their first name isn't ensuring meaningful data.

Building these constraints into the business layer is a good idea, but that doesn't ensure that the database will remain intact. The only way to guarantee that the data rules are not violated is to enforce them at the lowest level possible in the database.

The main disadvantage I can see is that let's say you have this:

Which one gives you the most information about the data needed for the UI?

This

CREATE TABLE [dbo].[BusData](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RecordId] [nvarchar](MAX) NULL,
[CompanyName] [nvarchar](MAX) NOT NULL,
[FirstName] [nvarchar](MAX) NOT NULL,
[LastName] [nvarchar](MAX) NOT NULL,
[ADDRESS] [nvarchar](MAX) NOT NULL,
[CITY] [nvarchar](MAX) NOT NULL,
[County] [nvarchar](MAX) NOT NULL,
[STATE] [nvarchar](MAX) NOT NULL,
[ZIP] [nvarchar](MAX) NOT NULL,
[PHONE] [nvarchar](MAX) NOT NULL,
[COUNTRY] [nvarchar](MAX) NOT NULL,
[NPA] [nvarchar](MAX) NULL,
[NXX] [nvarchar](MAX) NULL,
[XXXX] [nvarchar](MAX) NULL,
[CurrentRecord] [nvarchar](MAX) NULL,
[TotalCount] [nvarchar](MAX) NULL,
[Status] [int] NOT NULL,
[ChangeDate] [datetime] NOT NULL
) ON [PRIMARY]

Or This?

CREATE TABLE [dbo].[BusData](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RecordId] [nvarchar](50) NULL,
[CompanyName] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[ADDRESS] [nvarchar](50) NOT NULL,
[CITY] [nvarchar](50) NOT NULL,
[County] [nvarchar](50) NOT NULL,
[STATE] [nvarchar](2) NOT NULL,
[ZIP] [nvarchar](16) NOT NULL,
[PHONE] [nvarchar](18) NOT NULL,
[COUNTRY] [nvarchar](50) NOT NULL,
[NPA] [nvarchar](3) NULL,
[NXX] [nvarchar](3) NULL,
[XXXX] [nvarchar](4) NULL,
[CurrentRecord] [nvarchar](50) NULL,
[TotalCount] [nvarchar](50) NULL,
[Status] [int] NOT NULL,
[ChangeDate] [datetime] NOT NULL
) ON [PRIMARY]

One disadvantage is that you will be designing around an unpredictable variable, and you will probably ignore instead of take advantage of the internal SQL Server data structure, progressively made up of Row(s), Page(s), and Extent(s).

Which makes me think about [data structure alignment][1] in C, and that being aware of the alignment is generally considered to be a Good Thing ™. Similar idea, different context.

MSDN page for [Pages and Extents][2]

MSDN page for [Row-Overflow Data][3]


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

1) The SQL server will have to utilize more resources (allocated memory and cpu time) when dealing with nvarchar(max) vs nvarchar(n) where n is a number specific to the field.

2) What does this mean in regards to performance?

On SQL Server 2005, I queried 13,000 rows of data from a table with 15 nvarchar(max) columns.
I timed the queries repeatedly and then changed the columns to nvarchar(255) or less.

The queries prior to the optimization averaged at 2.0858 seconds. The queries after the change returned in an average of 1.90 seconds. That was about 184 milliseconds of improvement to the basic select * query. That is an 8.8% improvement.

3) My results are in concurrence with a few other articles that indicated that there was a performance difference. Depending on your database and the query, the percentage of improvement can vary. If you don't have a lot of concurrent users or very many records, then the performance difference won't be an issue for you. However, the performance difference will increase as more records and concurrent users increase.

Based on the link provided in the accepted answer it appears that:

1. 100 characters stored in an `nvarchar(MAX)` field will be stored no different to 100 characters in an `nvarchar(100)` field - the data will be stored inline and you will not have the overhead of reading and writing data 'out of row'. So no worries there.

2. If the size is greater than 4000 the data would be stored 'out of row' automatically, which is what you would want. So no worries there either.

However...

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


Conclusion:

If you want a kind of "universal string length" throughout your whole database, which can be indexed and which will not waste space and access time, then you could use `nvarchar(4000)`.

I checked some articles and find useful test script from this:

[To see links please register here]

Then changed it to compare between NVARCHAR(10) vs NVARCHAR(4000) vs NVARCHAR(MAX) and I don't find speed difference when using specified numbers but when using MAX. You can test by yourself. Hope This help.

SET NOCOUNT ON;

--===== Test Variable Assignment 1,000,000 times using NVARCHAR(10)
DECLARE @SomeString NVARCHAR(10),
@StartTime DATETIME;
--=====
SELECT @startTime = GETDATE();
SELECT TOP 1000000
@SomeString = 'ABC'
FROM master.sys.all_columns ac1,
master.sys.all_columns ac2;
SELECT testTime='10', Duration = DATEDIFF(ms,@StartTime,GETDATE());
GO
--===== Test Variable Assignment 1,000,000 times using NVARCHAR(4000)
DECLARE @SomeString NVARCHAR(4000),
@StartTime DATETIME;
SELECT @startTime = GETDATE();
SELECT TOP 1000000
@SomeString = 'ABC'
FROM master.sys.all_columns ac1,
master.sys.all_columns ac2;
SELECT testTime='4000', Duration = DATEDIFF(ms,@StartTime,GETDATE());
GO
--===== Test Variable Assignment 1,000,000 times using NVARCHAR(MAX)
DECLARE @SomeString NVARCHAR(MAX),
@StartTime DATETIME;
SELECT @startTime = GETDATE();
SELECT TOP 1000000
@SomeString = 'ABC'
FROM master.sys.all_columns ac1,
master.sys.all_columns ac2;
SELECT testTime='MAX', Duration = DATEDIFF(ms,@StartTime,GETDATE());
GO

As was pointed out above, it is primarily a tradeoff between storage and performance. At least in most cases.

However, there is at least one other factor that should be considered when choosing n/varchar(Max) over n/varchar(n). Is the data going to be indexed (such as, say, a last name)? Since the MAX definition is considered a LOB, then anything defined as MAX is not available for indexing. and without an index, any lookup involving the data as predicate in a WHERE clause is going to be forced into a Full Table scan, which is the worst performance you can get for data lookups.