SQL Server error 1919: 'Column is of a type that is invalid for use as a key column in an index'

Column 'Title' in table 'dbo.Articles' is of a type that is invalid for use as a key column in an index. (error 1919) usually means the column is nvarchar(max) or varchar(max). Give the column a real length, such as nvarchar(200), and the index can be created. If the column must stay max, it can only be an included column, not a key.

Most titles, names, emails and codes have a natural maximum length. Declaring it is the fix, and it also documents the data.

The error

CREATE INDEX IX_Articles_Title ON dbo.Articles (Title);

Msg 1919, Level 16, State 1, Server MACHINE\LOCALDB#EAD42A01, Line 2
Column 'Title' in table 'dbo.Articles' is of a type that is invalid for use as a key column in an index.

Why it happens

The table declared Title nvarchar(max) NOT NULL. A max column can hold very large values, and SQL Server does not allow such types as index key columns, so the CREATE INDEX is refused before any data is looked at. The table in the test was empty.

The error tends to surface late: a table works for months with a max column, and the refusal arrives the first time someone tries to make lookups on that column faster with an index.

The fix

-- fix A: give the column a real length
ALTER TABLE dbo.Articles ALTER COLUMN Title nvarchar(200) NOT NULL;
CREATE INDEX IX_Articles_Title ON dbo.Articles (Title);
SELECT name FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.Articles') AND name = N'IX_Articles_Title';

The index was created, and the check returned IX_Articles_Title. On a table with data, look at MAX(LEN(Title)) before choosing the new length and leave room to spare.

When a long text column must stay max and you only want it available to queries that use the index, put it in INCLUDE and key the index on a shorter column:

CREATE TABLE dbo.Notes (
    Id     int PRIMARY KEY,
    Author nvarchar(100) NOT NULL,
    Body   nvarchar(max) NOT NULL
);
CREATE INDEX IX_Notes_Author ON dbo.Notes (Author) INCLUDE (Body);

That index was created too and returned IX_Notes_Author. The rows are found by Author, and Body is stored with the index, so a query that finds rows by Author can read Body without going back to the table.

Of the two, fix A is the one to reach for first. A bounded length states what the column is allowed to hold, documents the data for the next reader of the schema, and lets the index be used to find rows by title. Keep max for genuinely long text such as article bodies or notes, and key indexes on the short columns next to it.

How it was reproduced

One script run with sqlcmd -S "(localdb)\MSSQLLocalDB" -d fix8_lab -I -i e1919.sql -W on SQL Server 2022 LocalDB 16.0.1210.3 and sqlcmd 16.0.1000.6, in a throwaway database fix8_lab created for the test and dropped afterwards. Two empty tables with nvarchar(max) columns. The server's computer name in the message is replaced by MACHINE; nothing else changed.

Frequently asked

Why can't I create an index on an nvarchar(max) column?

SQL Server does not allow max types as index key columns and raises error 1919. Change the column to a bounded length such as nvarchar(200), then create the index.

How do I fix 'is of a type that is invalid for use as a key column in an index'?

Alter the column to a bounded length that fits the data, or keep it as max and add it only in the INCLUDE list of an index keyed on another column.

Can an nvarchar(max) column be part of an index at all?

Yes, as an included column. CREATE INDEX IX_Notes_Author ON dbo.Notes (Author) INCLUDE (Body) worked with Body as nvarchar(max).

More decoded errors in the Fixes category.