Is the type invalid for use as a key column in an index?

Question

I want to create one table named tbl_Ticket_Mail_Address . When creating a table, an error is displayed.

Table:

 CREATE TABLE [dbo].[tbl_Ticket_Mail_Address] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Designation] NVARCHAR (MAX) NULL, [Emp_ID] NVARCHAR (MAX) NOT NULL, [Emp_Name] NVARCHAR (MAX) NULL, [Mobile] NVARCHAR (MAX) NULL, [Emp_Email] NVARCHAR (MAX) NULL, [Category] NVARCHAR (MAX) NULL, [Created_By] NVARCHAR (MAX) NULL, [Created_Date] DATE NULL, PRIMARY KEY CLUSTERED ([Id] ASC), CONSTRAINT [uc_tbl_Ticket_Mail_Address] UNIQUE NONCLUSTERED ([Emp_ID] ASC, [Category] ASC) ); 

58.1): SQL72014: .Net SqlClient data provider: Msg 1919, level 16, state 1, row 1 The column "Emp_ID" in the table "tbl_Ticket_Mail_Address" has a type that is not valid for use as a key column in the index. (58.0): SQL72045: Script runtime error. Executed script:

 CREATE TABLE [dbo].[tbl_Ticket_Mail_Address] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Designation] NVARCHAR (MAX) NULL, [Emp_ID] NVARCHAR (MAX) NOT NULL, [Emp_Name] NVARCHAR (MAX) NULL, [Mobile] NVARCHAR (MAX) NULL, [Emp_Email] NVARCHAR (MAX) NULL, [Category] NVARCHAR (MAX) NULL, [Created_By] NVARCHAR (MAX) NULL, [Created_Date] DATE NULL, PRIMARY KEY CLUSTERED ([Id] ASC), CONSTRAINT [uc_tbl_Ticket_Mail_Address] UNIQUE NONCLUSTERED ([Emp_ID] ASC, [Category] ASC) ); 

(58.1): SQL72014: .Net SqlClient data provider: Msg 1750, level 16, state 0, line 1 Could not create constraint or index. See Previous Errors. (58.0): SQL72045: Script runtime error. Executed script: An error occurred while executing a batch.

Answer 1

SQL Server does not allow you to create a unique index using the nvarchar(max) column. As D_T_U commented, choose a smaller size, for example. nvarchar(100) and you can create an index.

Naturally, the column size should fit your requirements, so 100 is just an example.

Information about the bonus . When creating an index, keep in mind that the size of combined index values ​​should not exceed 900 bytes. Each nvarchar character will use 2 bytes, so you can have no more than the combined size of nvarchar(450) . For example, in your case, you could

 [Emp_ID] NVARCHAR (50) NOT NULL, [Category] NVARCHAR (400) 

It is acceptable, but dangerous, to declare large sizes for nvarchar columns if the data in any given row does not exceed 900 bytes. For example, if you declare both columns as nvarchar(300) , you will receive the following message:

Attention! The maximum key length is 900 bytes. The uc_tbl_Ticket_Mail_Address index has a maximum length of 1200 bytes. For some combination of large values, the insert / update operation will fail.

Answer 2

Mark the CREATE INDEX statement on MSDN.

Columns containing LOB data types ntext, text, varchar (max), nvarchar (max), varbinary (max), xml or image cannot be specified as key columns for the index.

To resolve the issue, refer to @Damien_The_Unbeliever's comment.

Answer 3

This error can also occur if your index column has been identified as SPARSE.

 [hash] VARBINARY(32) SPARSE NULL -- Fail [hash] VARBINARY(32) NULL -- Success 

(597.1): SQL72014: .Net data provider SqlClient: Msg 1919, level 16, state 2, row 1 The "hash" of the column in the table "Document_JSON" has an invalid type to use as the key column in the index,

(597.0): SQL72045: Script execution error.