Skip to content

April 4, 2012

5

LOB Columns & sys.partitions

by Scott Newman

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.

image

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

image

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

image

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?

Read more from SQL Server
5 Comments Post a comment
  1. Aug 30 2012

    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.

    Reply
    • Aug 30 2012

      The version of sql is Sql 2008 R2 EE RTM (must patch!). The compatibility level of the database is 10.50.1600.1. I’ll patch the sucker this weekend and test it again.

      Reply
  2. Sep 5 2012

    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!!!

    Reply

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

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments

%d bloggers like this: