Hello sql programmers,
Probably you have taken the following error message indicating that you have used a whose type is invalid for use as a key column in an index while creating an index on a table.
I have got the following error message when I run the t-sql code on the sql query editor window.
Msg 1919, Level 16, State 1, Line 11
Column 'column_name' in table 'dbo.SampleTable' is of a type that is invalid for use as a key column in an index.
The reason for such an error message is that I was trying to build an unique index on a column whose data type is nvarchar(max) which is not a valid sql data type to build an index on
If you run the below t-sql create index command on a nvarchar(max) column you can experience the same error.
create unique index IX_SampleUniqueIndexOnInvalidTypeColumn on dbo.SampleTable (column_with_nvarchar_max)
Here is the note from MS SQL Server 2008 Books OnLine on this point :
Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index. Also, a view definition cannot include ntext, text, or image columns, even if they are not referenced in the CREATE INDEX statement.