0

With covering indexes in SQL Server, say your primary index is an INT, and you have include (a, b, c) where a and b are also INT's and c is an nvarchar(max). Does SQL Server store the whole nvarchar(max) in the index, or, does it store a reference pointer to the nvarchar(max)?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bizzehdee
  • 20,289
  • 11
  • 46
  • 76
  • 2
    The answer is probably "it depends". And upon what it depends is how much you actually put into that BLOB column. ie, if I put more than could ever fit on one page, it will necessarily spill. But what if I just put `N'aaa'`? But there's no need to speculate - fire up `dbcc ind` and `dbcc page` against a test table and experiment with different scenarios! – Ben Thul Mar 24 '23 at 12:36
  • `max` types such as `varchar` `nvarchar` `varbinary` and `xml` go in-row if less than the max row size of 8kb, otherwise they go into a separate LOB heap, and use a pointer reference. – Charlieface Mar 24 '23 at 16:02

0 Answers0