Would adding nvarchar(max) to the included columns of an NC index that has one nchar(16) column key column cause rebuild issues with 20+ million rows?
By "rebuild issues," I mean taking a very long time to rebuild the index. I thought that the "included" columns were treated differently from key columns and would not have as big of an impact on the cluster.
Yes, rebuilding that index will probably be a lot slower.
Baseline it will be an entire extra copy of this data on your disks. If the column is updated frequently, then it could cause the index to fragment more quickly. And the more data is in the index, the longer it will take to drop and recreate ("rebuild").
It's generally not recommended to add large data types as included columns in a nonclustered index. It causes fewer rows to fit on a page (or pointers to off row storage), which causes more pages to have to be read whenever you query this index. And as you can imagine it will require more RAM to keep these index pages in memory.
You would want to do performance testing to make sure that the benefits of this included column (presumably to avoid a key lookup?) outweigh the costs mentioned above.
Of course, the level of impact will depend greatly on how much data is actually stored in this column in practice.
You're right that included columns are treated differently, in that they don't affect the order that index data is stored. But they are still included at the leaf level of the index, so they will definitely cause your rebuilds to take longer. Included columns will generally cause less overhead than key columns when it comes to updates, since they won't cause the rows to be shuffled around if the order changes. But rebuilds are really just dropping and recreating the whole index.
