CLOB / VARCHAR(MAX) is causing slow select query I have a transaction table where I am storing buy/sell transactions related information, and this information I am getting is from MQ as an XML and I am also storing XML String as VARCHAR(MAX) in SQL Server database.

The issue is that since I am storing XML String into this table, select query is running very slow.

Is there any way I can increase the performance? Any recommendation will be highly appreciated.

The solution which I can think of is to create another table of 1 -1 mapping with my transaction table to store XML String but not really sure if this is the best idea.
If you can adjust the datatype of the column to XML and your table has a clustered primary key, you can potentially improve performance, but there are a few fundamental changes you'll need to ensure occur.


Ensure your table is Clustered on the Primary Key.  Your table must be clustered and the cluster key must also be your Primary Key.
Strongly type your column as XML.  Storing XML as character data eliminates any possibility to create an XML Index(es) on the column.


If you can satisfy these requirements, you can then take advantage of XML Indexes.  I'm not going to go into the details of how these work or how you set them up, as you can easily find articles online that discuss this in more detail (ref1, ref2, ref3), but used properly these can dramatically improve performance of queries that have to interact with XML data.