Identifying Columns with Out-Of-Row Data

In a previous post, I was talking about how changing data types from the older ntext, text, and image data types to the current nvarchar(max), varchar(max), and varbinary(max) data types doesn’t achieve the same outcome as having defined the tables that way in the first place, unless you subsequently rebuild the tables.

I also had a question about how you can find out which columns still have pointers to out of row data. Unfortunately, finding that out doesn’t seem so easy and it would vary row by row in the table.

To see this, let’s start by creating a table, populating it, then changing the data type the same way that I did in the last post:

First I ran a query to find the pages that are allocated to the table:

Note the there are in-row data pages and LOB data pages. Let’s now investigate the contents of page 2579316 from file 1:

If we scroll down through the contents of the page, we can find the row data and pointers:

Note that this shows that column 3 (RequestDetails) is a Textpointer. You can see from the RowId value where the data is located (File 1, Page 2044944, Slots 0).

I’d like to automate these steps but that’s a project for another day.

2014-11-17