Me and Chris are writing a little tool that we call PageBrowser. PageBrowser is supposed to help us look into SQL Servers physical storage of data. But mostly it’s supposed to teach us what the changes are in SQL Server 2005.
In this category, I’ll share these findings with you.
In SQL Server 2000, every table that has a BLOB-column (text/ntext/image) has an index with index id 255. Run this statement in an SS2000 and you’ll see the index.
[SQL]SELECT *
FROM Northwind.dbo.sysindexes
WHERE object_name(id) = ‘Employees’[/SQL]
The Employees table in the Northwind database has an photo column of type image. This data is stored in this special index. You won’t see this using sp_helpindex because SQL Server hides these indexes from mere mortals that don’t use the system tables.
Come into the SQL Server 2005 world.
If you look in sys.indexes, you will not find the index with id 255. You have to dig a little to find the information about the blob-data:
[SQL]SELECT *
FROM sys.partitions
WHERE object_id = object_id(‘Employees’)[/SQL]
Here we find the partition ids for the employees table. We want either the clustered index or the heap partition – it’s here that SQL Server stores the actual row data. The heap always has index id 0 and the clustered index always has index id 1 – this hasn’t changed. I take the partition id from the previous query and query sys.system_internals_allocation_units to get all AUs for the index:
[SQL]
SELECT *
FROM sys.system_internals_allocation_units
WHERE container_id IN (
SELECT partition_id
FROM sys.partitions
WHERE index_id < 2
AND object_id = object_id(‘Employees’)
)[/SQL]
And there we see it! The row with type description LOB_DATA is the one I’m looking for. Right there, I can see the root page of the BLOB data, the first leaf page and the first IAM page.
Things I learned working with this:
1. I really like the naming of the new system views. Calling it index_id instead of indid is great!
2. I feel cool when I query a view with the word “internals” in it.