SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz Development Resources
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.

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

Last post 08-20-2010, 11:58 AM by eralper. 2 replies.
Sort Posts:
  •  10-12-2008, 4:39 AM 3487

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

    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.


  •  08-20-2010, 6:21 AM 21227 in reply to 3487

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

    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.
  •  08-20-2010, 11:58 AM 21228 in reply to 21227

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

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

View as RSS news feed in XML
Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems