Column is of a type that is invalid for use as a key column in an index

What it means

The columns an index is sorted by (its key columns) must have a type SQL Server can compare and store in the index’s pages. The large-value types can’t be keys: nvarchar(max), varchar(max) and varbinary(max), and the old text, ntext and image. A primary key or UNIQUE constraint is an index too, so it hits the same rule:

Msg 1919, Level 16, State 1, Server 7732422b7f56, Line 1
Column 'slug' in table 'seo_err_mssql.articles' is of a type that is invalid for use as a key column in an index.

For a constraint, a second line follows: Could not create constraint or index. See previous errors. (1750). Nothing is created.

There’s a related limit on size. A key may be at most 900 bytes for a clustered index and 1,700 bytes for a nonclustered one (SQL Server 2016 and later). A column like nvarchar(1000) (2,000 bytes) can be indexed, with a warning, and then any row whose value is too long fails to insert (error 1946).

Common causes

1. A string column created without a length. Tools that default to nvarchar(max), such as EF Core for a string property with no maximum length, or a CSV import wizard, then an index or UNIQUE constraint added by hand.
2. A long column as the primary key: a URL, an email or a file path declared (max).
3. Legacy text or ntext columns, which can’t even be included columns (error 1999).
4. An xml column, which can only have XML indexes (error 1977).

How to fix it

Give the column a real length

Check how long the values really are, then pick a length that fits them and the index limit (nvarchar takes two bytes per character, so 450 characters fill a 900-byte clustered key):

SELECT MAX(LEN(slug)) AS longest FROM dbo.articles;

ALTER TABLE dbo.articles ALTER COLUMN slug nvarchar(200) NOT NULL;
CREATE UNIQUE INDEX UX_articles_slug ON dbo.articles (slug);

If any value is longer than the new length, the ALTER fails with error 2628 and changes nothing. In EF Core, set [MaxLength(200)] or .HasMaxLength(200) on the property and add a migration.

Index a hash of a long value

When values can be longer than the limit, index a fixed-size hash in a persisted computed column:

ALTER TABLE dbo.articles
  ADD url_hash AS CAST(HASHBYTES('SHA2_256', url) AS binary(32)) PERSISTED;
CREATE INDEX IX_articles_url_hash ON dbo.articles (url_hash);

SELECT id FROM dbo.articles
WHERE url_hash = CAST(HASHBYTES('SHA2_256', N'https://example.com/a') AS binary(32))
  AND url = N'https://example.com/a';

Compare the full value as well, as in the query, since two different values can in principle share a hash. A unique index on the hash enforces uniqueness the same way. Inserts and updates on a table with an index on a computed column need the standard SET options, which most drivers use; see error 1934.

Put a (max) column in INCLUDE, not the key

If you only need the column’s value returned from an index seek, not searched by, include it:

CREATE INDEX IX_articles_author ON dbo.articles (author_id) INCLUDE (title);

varchar(max), nvarchar(max) and varbinary(max) are allowed as included columns; text, ntext and image aren’t. For searching inside long text, a full-text index is the tool, not a B-tree index.

Replace text and ntext

text and ntext are deprecated. Change them to varchar(max) or nvarchar(max) (and then to a real length, if the data allows) before indexing.

Reproduce it

On SQL Server 2022 (16.0.4295.3) with sqlcmd, a table with slug nvarchar(max), photo varbinary(max), legacy text and tags xml columns:

CREATE INDEX IX_articles_slug ON seo_err_mssql.articles (slug);
ALTER TABLE seo_err_mssql.articles ADD CONSTRAINT UQ_articles_slug UNIQUE (slug);
CREATE TABLE seo_err_mssql.pages (url nvarchar(max) PRIMARY KEY);
CREATE INDEX IX_a_tags ON seo_err_mssql.articles (tags);

Msg 1919, Level 16, State 1, Server 7732422b7f56, Line 1
Column 'slug' in table 'seo_err_mssql.articles' is of a type that is invalid for use as a key column in an index.
Msg 1919, Level 16, State 1, Server 7732422b7f56, Line 1
Column 'slug' in table 'articles' is of a type that is invalid for use as a key column in an index.
Msg 1750, Level 16, State 1, Server 7732422b7f56, Line 1
Could not create constraint or index. See previous errors.
Msg 1919, Level 16, State 1, Server 7732422b7f56, Line 1
Column 'url' in table 'pages' is of a type that is invalid for use as a key column in an index.
Msg 1750, Level 16, State 1, Server 7732422b7f56, Line 1
Could not create constraint or index. See previous errors.
Msg 1977, Level 16, State 1, Server 7732422b7f56, Line 1
Could not create index 'IX_a_tags' on table 'seo_err_mssql.articles'. Only XML Index can be created on XML column 'tags'.

pages wasn’t created. The photo and legacy columns gave 1919 too, and legacy in an INCLUDE gave Msg 1999 … is of a type that is invalid for use as included column in an index., while INCLUDE (body, title) with (max) columns worked. After ALTER COLUMN slug nvarchar(200) NOT NULL the unique index was created, and the hashed computed column took an index. On a column with a 300-character value, ALTER COLUMN … nvarchar(200) failed with 2628. An index on an nvarchar(1000) column was created with Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'IX_wide_a' has maximum length of 2000 bytes., and inserting a 1,000-character value then failed with Msg 1946 … The index entry of length 2000 bytes for the index 'IX_wide_a' exceeds the maximum length of 1700 bytes for nonclustered indexes. SQL Server 2019 and 2025 printed the same messages.

In Inlet

Inlet’s structure designer for SQL Server edits columns, indexes and constraints and shows the T-SQL before it runs, so you can give a column its length and add the index in one change. A table’s definition shows each column’s type, including which ones are (max). When SQL Server refuses, Inlet shows its message with SQL Server error 1919, state 1, severity 16 and links to this page.