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.

I had this error because I was trying to put an index on a column that was of format "text".  After reading this post, I changed this column to nvarchar(4000) and was fine.  Thanks.

Hi Navy, I'm happy that my case helped you solve your problem too. Thanks for feedback :)