Included Column with nvarchar(max) type have impact to Index fragmentation or it maybe raise issues performance?

Truong, Tam 20 Reputation points

hi Team,

I have one issue need to help of team. I have one index and it include one column with type nvarchax(max), the included column is select/update/insert frequently so I included it to the index And I confuse if including this column maybe cause index fragmentation or some problems impact to performance. pls, help give me some recommendation with this case. Thank so much.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,553 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 92,801 Reputation points

    Disclaimer: I have not actually tested this. Nor have I actually investigated exactly how an nvarchar(MAX) value is stored as an included column in an index. What follows is a theoretical discussion based on general assumptions based on my general knowledge about how data is stored on disk.

    An nvarchar(MAX) value can be stored in-row or out-of-row. If it is more than 8000 bytes in length it will always be stored out-of-row, and all that is stored in-row is a 16-byte pointer. If the value is smaller, it may be stored in-row, but there is a table option to force it to always be out of row.

    If the value is stored out-of-row, the 16-byte pointer is all there is in the index. Thus updates to the value itself will not affect the index, so there will be no frragmentation.

    If the value is stored in-row, this means that updates needs to write both to the data pages and the index page. And if the value changes in size, and increases in length, this can lead to that there is no space on the page, and therefore the page must be split, which can lead to fragmentation.

    To sum up: what will happen in your case depends largely on the size of the data in the nvarchar(MAX) column.

    1 person found this answer helpful.
    0 comments No comments

  2. PercyTang-MSFT 11,726 Reputation points Microsoft Vendor

    Hi @Truong, Tam

    For the possibility of impacting performance, I recommend using tools like sys.dm_db_index_physical_stats or DBCC SHOWCONTIG to monitor and maintain index fragmentation levels on a regular basis.

    Best regards,

    Percy Tang

    0 comments No comments