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...
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
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
Create a new table FKed to UserDataId to store only large DataValues > 4000?
Which--if any--should I go with?