add index sql server 2017.

Question

User-583959464 posted

I am using sql server 2017. how can I build a index on [aliases] column with datatype  nvarchar(max) in the following table.

for aliases column, the possible max length is 17200. the performance is poor when I search for that column, how to improve?

CREATE TABLE [dbo].[tbl_testing](
 [id] [bigint] NULL,
 [aliases] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Answer 1

User-583959464 posted

I am using sql server 2017. how can I build a index on [aliases] column with datatype  nvarchar(max) in the following table.

for aliases column, the possible max length is 17200. the performance is poor when I search for that column, how to improve?

CREATE TABLE [dbo].[tbl_testing](
 [id] [bigint] NULL,
 [aliases] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

anyone knows?

Answer 2

User-1330468790 posted

Hi 20141113,

 

I think you need to add a nonclustered index for the column 'aliases'.

The syntax is as below:

CREATE [NONCLUSTERED] INDEX index_name
ON table_name(column_list);

• First, specify the name of the index after the CREATE NONCLUSTERED INDEX clause. Note that the NONCLUSTERED keyword is optional.
• Second, specify the table name on which you want to create the index and a list of columns of that table as the index key columns.

 

More information, you could refer to below link:

Clustered and Nonclustered Indexes Described

 

Hope helps.

Best regards,

Sean