Column '*' in table '*' is of a type that is invalid for use as a key column in an index. (Microsoft SQL Server, Error: 1919)

Scenario:

TITLE: Microsoft SQL Server Management Studio

------------------------------

Create failed for Index 'tmp_IX_*_EmailSubjectSort'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000.0+((KJ_PCU_Main).120628-0827+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Index&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Column 'ColumnName' in table 'schema.TableName' is of a type that is invalid for use as a key column in an index. (Microsoft SQL Server, Error: 1919)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000&EvtSrc=MSSQLServer&EvtID=1919&LinkId=20476

------------------------------

BUTTONS:

OK

Resolution:

Recently some of my application user was facing this error and they came to me. On initial analysis I did realize that, they were trying to create an non-clustered index on NVARCHAR(MAX) field. The error has gone once I suggested them to change to fixed length field. Just thought to share on the blog, so that other can refer if face similar situation.

Make sure that datatype of the columns does not belongs to any of these, such as ntext, text, image, varchar(max), nvarchar(max), and varbinary(max). SQL Server doesn allow to create any type of indexes on columns with these datatypes.