I am working with a table that has all character types set to nvarchar
some of them are nvarchar(max)
. We are converting all these to varchar
and specifying a character width based upon the actual usage in production. The production data uses a range of 2 characters up to 900 characters of actual used width for any given column. We are going to add padding of 10% when applicable.
-- Insert statements for procedure here
UPDATE Listings WITH (ROWLOCK)
SET [SubType] = 'S'
WHERE @idSettings = idSettings AND
(@idRetsClass = 0 OR idRetsClass = @idRetsClass)
AND (@idRetsSetting = 0 OR idRetsSetting = @idRetsSetting)
AND IsNew = 1 AND ([SubType] LIKE '%Single Family Home%' OR [SubType] LIKE '%Modular%' OR [SubType] LIKE '%Mobile Home%'
OR [SubType] LIKE '% Story%' OR [SubType] = '' OR [SubType] = 'residential - S' OR [SubType] = '1 House on Lot' OR [SubType] = '2 Houses on Lot'
OR [SubType] = 'Detached' OR [SubType] LIKE '%single family%' OR [SubType] = 'ranch' OR [SubType] = 'Semi-Detached' OR [SubType] = 'single' OR [SubType] = 'one family' OR [SubType] = 'Residential'
OR [SubType] = 'Ranch Type' OR [SubType] = '2 or More Stories' OR [SubType] = 'Cape Cod' OR [SubType] = 'Split Level' OR [SubType] = 'Bi-Level' OR [SubType] = 'Detached Single'
OR [SubType] = 'Single-Family Homes' OR [SubType] = 'house' OR [SubType] = 'detached housing' OR [SubType] = 'det')
A large overhaul of this table that consists literally 140 (nvarchar
) columns, 11 being MAX. I am dropping 30 indexes and recreating them afterwards.
My question is in what situations is varchar(max)
preferred?
Only when you expect to have 4k or more characters?
What should I learn and prepare for when doing this?
Will this improve performance when a clustered index update that affects the clustered key has to update the all non-clustered indexes?
We are having update procedures timing out that are using 75% to 95% of the query execution plan & displayed plan for an a clustered index update.