locked
add index sql server 2017. RRS feed

  • 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

    Tuesday, March 9, 2021 3:09 AM

All replies

  • 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?

    Tuesday, March 9, 2021 8:59 AM
  • 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

    Wednesday, March 10, 2021 8:52 AM