On the table and index organization page in the sql server documentation link here, under the Allocation Units section, the documentation states:

Each table that has at least one LOB column also has a row in sys.partitions with index_id > 250.

Whilst looking for sparsely populated image columns in one of my tables that’s taking up waay too much room I noticed that said table does indeed contain a LOB column (image data type), yet has no index listed in sys.partitions with an index_id > 250.


Here is the results from querying the sys.partitions table:


Here’s the allocations from the sys.system_internals_allocation_units table.  Obviously a bunch of unused space in this table.


Is the documentation wrong?  Why you sys.partitions no have index_id > 250 for image column?

Y U No - sys.partitions why u no idx gt 250?

5 thoughts on “LOB Columns & sys.partitions

  1. whats the version of SQL and compatibility level of the database where this table is? maybe because “image” is to be deprecated, you may have fallen into a bug due to some weird versions conflict.

  2. Sorry, haven’t tried yet. I wasn’t able to take the server down early for maintenance. I will be 100% sure to post if it did though. Won’t be for a couple weeks yet though, vacation! Wooooo!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.