Post date: Feb 16, 2011 8:21:27 AM
The results are clear: nvarchar(max) is much slower in all operations except DELETE.
Original script found in comment here: http://ask.sqlservercentral.com/questions/5566/is-there-any-drawback-to-always-use-nvarcharmax by Jeff Moden on Ask SqlServer Central
Script updated to include a few additional tests.
DECLARE @BitBucket1 NVARCHAR(4000), @BitBucket2 NVARCHAR(MAX);SET STATISTICS TIME ON;PRINT '********** Test NVARCHAR(4000) INSERT **********'; SELECT TOP 1000000 CAST(ROW_NUMBER() OVER (ORDER BY ac1.Object_ID) AS NVARCHAR(4000)) AS N INTO #Test1 FROM Master.sys.All_Columns ac1 CROSS JOIN Master.sys.All_Columns ac2;PRINT '********** Test NVARCHAR(MAX) INSERT **********'; SELECT TOP 1000000 CAST(ROW_NUMBER() OVER (ORDER BY ac1.Object_ID) AS NVARCHAR(MAX)) AS N INTO #Test2 FROM Master.sys.All_Columns ac1 CROSS JOIN Master.sys.All_Columns ac2;PRINT '********** Test NVARCHAR(4000) SELECT **********'; SELECT @BitBucket1 = N FROM #Test1;PRINT '********** Test NVARCHAR(MAX) SELECT **********'; SELECT @BitBucket2 = N FROM #Test2; PRINT '********** Test NVARCHAR(4000) UPDATE **********'; UPDATE #Test1 SET N = N;PRINT '********** Test NVARCHAR(MAX) UPDATE **********'; UPDATE #Test2 SET N = N;PRINT '********** Test NVARCHAR(4000) COMPARISON LIKE **********'; SELECT @BitBucket1 = N FROM #Test1 where N Like '9900%'PRINT '********** Test NVARCHAR(MAX) COMPARISON LIKE **********'; SELECT @BitBucket2 = N FROM #Test2 where N Like N'9900%'PRINT '********** Test NVARCHAR(4000) COMPARISON EQUALS **********'; SELECT @BitBucket1 = N FROM #Test1 where N = '9900'PRINT '********** Test NVARCHAR(MAX) COMPARISON EQUALS **********'; SELECT @BitBucket2 = N FROM #Test2 where N = N'9900' PRINT '********** Test NVARCHAR(4000) DELETE **********'; DELETE #Test1 PRINT '********** Test NVARCHAR(MAX) DELETE **********'; DELETE #Test2 SET STATISTICS TIME OFF;--===== Cleanup DROP TABLE #Test1, #Test2;
********** Test NVARCHAR(4000) INSERT ********** SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 609 ms, elapsed time = 1527 ms. (1000000 row(s) affected) ********** Test NVARCHAR(MAX) INSERT ********** SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 1750 ms, elapsed time = 1889 ms. (1000000 row(s) affected)
********** Test NVARCHAR(4000) SELECT ********** SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 203 ms, elapsed time = 193 ms. ********** Test NVARCHAR(MAX) SELECT ********** SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 328 ms, elapsed time = 328 ms.
********** Test NVARCHAR(4000) UPDATE ********** SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 922 ms, elapsed time = 957 ms. (1000000 row(s) affected) ********** Test NVARCHAR(MAX) UPDATE ********** SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 2344 ms, elapsed time = 2357 ms. (1000000 row(s) affected)
********** Test NVARCHAR(4000) COMPARISON LIKE ********** SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 219 ms, elapsed time = 219 ms. ********** Test NVARCHAR(MAX) COMPARISON LIKE ********** SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 500 ms, elapsed time = 509 ms.
********** Test NVARCHAR(4000) COMPARISON EQUALS ********** SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 125 ms, elapsed time = 129 ms. ********** Test NVARCHAR(MAX) COMPARISON EQUALS **********
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 516 ms, elapsed time = 511 ms.
********** Test NVARCHAR(4000) DELETE ********** SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 1687 ms, elapsed time = 1776 ms. (1000000 row(s) affected) ********** Test NVARCHAR(MAX) DELETE ********** SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 1641 ms, elapsed time = 1779 ms. (1000000 row(s) affected)