Fast alter column NVARCHAR(4000) to NVARCHAR(260)

I have a performance problem with very large memory grants handling this table with a couple of NVARCHAR(4000) columns. Thing is these columns are never larger than NVARCHAR(260).

Using

ALTER TABLE [table] ALTER COLUMN [col] NVARCHAR(260) NULL

results in SQL Server rewriting the entire table (and using 2x table size in log space), which is billions of rows, only to change nothing, isn't an option. Increasing the column width doesn't have this problem, but decreasing it does.

I have tried creating a constraint CHECK (DATALENGTH([col]) <= 520) or CHECK (LEN([col]) <= 260) and SQL Server still decides to re-write the entire table.

Is there any way to alter the column data type as a metadata-only operation? Without the expense of rewriting the entire table? I'm using SQL Server 2017 (14.0.2027.2 and 14.0.3192.2).

Here is a sample DDL table to use to reproduce:

CREATE TABLE [table](
    id INT IDENTITY(1,1) NOT NULL,
    [col] NVARCHAR(4000) NULL,
    CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED (id ASC)
);

And then run the ALTER.

I don't know of a way to directly accomplish what you're looking for here. Note that the query optimizer isn't smart enough at this time to factor in constraints for memory grant calculations, so the constraint wouldn't have helped anyway. A few methods that avoid rewriting the table's data:

• CAST the column as NVARCHAR(260) in all codes that uses it. The query optimizer will calculate the memory grant using the casted data type instead of the raw one.

• Rename the table and create a view that does the cast instead. This accomplishes the same thing as option 1 but may limit the amount of code you need to update.

• Create a non-persisted computed column with the right data type and have all of your queries select from that column instead of the original one.

• Rename existing column and add computed column with the original name. Then adjust all of your queries making updates or inserts to the original column to use new column name instead.

StackExchange Database Administrators Q#248749, answer score: 16