Is of a type that is invalid for use as a key column in an index?

Question

I want to create one table named as tbl_Ticket_Mail_Address . While creating table showing error.

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, Line 1 Column 'Emp_ID' in table 'tbl_Ticket_Mail_Address' is of a type that is invalid for use as a key column in an index. (58,0): SQL72045: Script execution error. The 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 execution error. The executed script: An error occurred while the batch was being executed.

Answer 1

SQL Server doesn't allow you to create a Unique index using a nvarchar(max) column. As D_T_U commented, choose a smaller size, e.g. nvarchar(100) and you will be able to create the index.

Naturally, the column size needs to fit in with your requirements, so 100 is just an example.

Bonus info: When creating the index, keep in mind that the size of the combined index values must not exceed 900 bytes. Each nvarchar character will use 2 bytes, so you can at most have a combined size of nvarchar(450). For example, in your case you could have

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

It is permissible, but dangerous, to declare larger sizes for nvarchar columns as long as the data on any given row does not exceed 900 bytes. For example, if you declare both columns as nvarchar(300), you will get the following message:

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