I have a table with about 100MM rows that stores information about a user.

CREATE TABLE [dbo].[UserData](
        [UserDataID] [int] IDENTITY(1,1) NOT NULL,
        [UserID] [int] NOT NULL,
        [DataId] int NOT NULL,
        [DataValue] [nvarchar](4000) NOT NULL,
        [EncryptedDataValue] [varbinary](max) NULL)

I need to be able to store data in DataValue that is > nvarchar(4000). But, only a very small percentage of the rows actually need this column as nvarchar(max). I know that as soon as DataValue is over nvarchar(4000), internally, sql will store the data as a blob[?], substantially increasing the time it takes to make this change. (not sure how read/write time will be affected later).

I thought of a few potential options...

  1. Change DataValue from nvarchar(4000) to nvarchar(max) and just eat the time it takes to make the change; not worrying that only 1% of the rows are using MAX?

  2. Alongside DataValue, add a DataValueXL column that is nvarchar(max) and introduce application logic to save in the appropriate column depending on the size of the data? (Marking both as NULL)

  3. Create a new table FKed to UserDataId to store only large DataValues > 4000?

Which--if any--should I go with?


  • What is your main worry here? The one off impact of changing the schema? – Martin Smith Feb 7 '12 at 22:54
  • yes--the impact of changing when 'MAX' wouldn't be useful for most rows. – brian Feb 7 '12 at 23:02

as soon as DataValue is over nvarchar(4000), internally, sql will store the data as a blob

This is incorrect. SQL Server will use off-row storage for both max and non-max types. In other words your NVARCHAR(4000) may also be stored off-row. The actual storage location will depend on whether the row fits or not on a page and on the exact combination of sp_tableoption values for the large value types out of row and text in row settings.

Your best bet is to change it to max. SQL will store the value in-row whenever possible, offering fast access. This makes application programing much simpler as you don't have to deal with the actual location (Column) based on size. You should only be concerned if you have an objection on adding the first max type column to a table (eg. it will prevent online rebuild operations), but you are already willing to add one so that should not be an issue.

  • Currently all data in DataValue is < 4000, how come it takes so long to change it to nvarchar(max)? (the change is instant when changing from 2000 to 4000) – brian Feb 7 '12 at 23:05
  • @bluevoodoo1 - From 2000 to 4000 is just a metadata change. I've wondered before why this isn't the case for a move up to max too but it seems to add a new variable length column and copy the previously existing data – Martin Smith Feb 7 '12 at 23:14
  • @MartinSmith I've not eyeballed it in detail but I assume this is due to NVARCHAR(MAX) being a LOB, so data must be moved from the in-row pages to an entirely new set of pages allocated from the LOB IAM. – Mark Storey-Smith Feb 7 '12 at 23:18
  • 3
    Also in SQL Server 2012 online rebuild with LOB columns will be possible, so that objection will go away for some users quicker than others. – Aaron Bertrand Feb 7 '12 at 23:22
  • 4
    switch from non-max type to a max type must update every row and copy the data from dropped column to newly added column, since the non-max and max types do not share the storage (Row-Overflow vs. LOB allocation units). Change from 2000 to 4000 can be done in place (metadata only) because both nvarchar(2000) and nvarchar(4000) share the same storage (either in-row or in the row-overflow allocation unit). Also, see rusanu.com/2011/10/20/sql-server-table-columns-under-the-hood – Remus Rusanu Feb 7 '12 at 23:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.