Not invalid type of index when creating non-clustered index-CodePudding

I have this table Test:

ID - int primary key not null, 
CODE - nvarchar(max), 
NAME - nvarchar(max)

It has clustered index on ID.

But in an execution plan, the search uses ID and CODE columns.

How can I add non-clustered index to the table? When I try, I get an error:

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

Could you recommend what actions must be done?

I need to add the index for the query fast processing.

There is already clustered index which is not effective in query processing.

In SSMS, for creating NON-CLUSTERED INDEX - the only option was to add ID as key column and then to add CODE as Include in the index. But it means that table will have both CLUSTERED INDEX on ID column and NON-CLUSTERED INDEX on ID with CODE include. Is that right?

What is the best practice:

If table can have both clustered and non-clustered indexes basing on same id key column?
How should you do for fixing the problem?

CodePudding user response：

You can't do it directly, but you can use INCLUDE

As Lamu points out, such a NON Clustered Index would not be used, as every search that includes ID, would use the clustered Primary KEY, to get its wated rows and ignore the code.

The PRIMARY KEY means that the every row has a UNIQUE ID.

CREATE TABLe test_table (
ID  int primary key not null, 
CODE  nvarchar(max), 
NAME  nvarchar(max)
)

CREATE NONCLUSTERED INDEX IDX_NC_test_table_CODE ON test_table(ID) INCLUDE(CODE)

CodePudding user response：

You can't index MAX columns, they are too large as you are stating the value is likely to be between 8,000 and 2 billion bytes in size; so you are stating here that the name is likely over 4,000 characters.

Seems highly unlikely that the CODE column would need to be MAX. Is there a specific reason for this - or has it been created automatically via an ORM with no length specified? If not make it a sensible length and create a non-clustered index on it.