XML Column in Index include

• Deepak Kolapkar

  SSC-Addicted

  Points: 409

  More actions

  June 18, 2012 at 11:00 am

  #276429

  Hi,

  I have a scenario where considering XML column also as a part of an index INCLUDE seems to be improving the query plan.

  I am not sure if having XML datatype column as a part of an index include is a good idea?

  Any thoughts, ideas, suggestions would be appreciated.

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 18, 2012 at 11:13 am

  #1502341

  If you include the XML column, you are duplicating the column. The XML data will be stored twice, once in the table, once in the index. If that's a couple kb of XML, that may be fine. If it's large, that may be a major problem.

  Additionally, you will no longer be able to rebuild that index online.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass