how to build xml column and add index on xml column 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,

    [xml_aliases] xml null              ----add one more xml_aliasess extracted from aliases column


    the following is aliases column raw data
    ID aliases
    1 aaaaaaaaaa;bbbbbbbbbb;cccccccccccccccccccccccccc
    2 ddddddddddd;iiiiiii;i
    3 nnnnnnnnnnnnnnnnnnnnn

    the aliases is separated by semi-colon ';'
    how can i build xml schema for [aliases] column and add a xml column to table?

    for aliases column which contain multiple record per id and sepearate by ';'  

    finally, how to add index on xml column to improve the performance by searching xml_aliases column?
    for above example

    id  xml_aliases
    --  --------------------------
    1   aaaaaaaaaa
    1   bbbbbbbbbb
    1   cccccccccccccccccccccccccc
    2   ddddddddddd
    2   iiiiiii
    2   i
    3   nnnnnnnnnnnnnnnnnnnnn

    Tuesday, March 9, 2021 3:22 AM

All replies