As I explained in "Managing Data Space," you can join sys.partitions with the sys.allocation_units view to determine the amount of space used for each rowset. Listing 2 shows the join of sys.partitions and sys.allocation_units; it turns out to be a one-to-one join because each row in sys. partitions has only one row in sys.allocation_units for the in-row data in the test1 and test2 tables. Table 2 shows the output for the join; again, the actual values you receive might be slightly different.
Also notice that Listing 2 returns four columns from sys.allocation_units: allocation_unit_id (aliased to au_id), a column called type (for which the value 1 indicates in-row data), container_id, and the number of pages in that allocation_unit. Note also the container_id matches the hobt_id from sys.partitions. Each row in sys.allocation_units has a unique allocation_unit_id.
To make the join between sys.partitions and sys.allocation_units a one-to-many join, you'll need to alter the test tables to allow them to contain additional kinds of data. The code in Listing 3 alters test1 to add a large varchar column and alters test2 to add a text column. The large varchar column will make the row size exceed the maximum of 8060 bytes, so it needs to be stored as out-of-row data, which is indicated in sys.allocation_units as type 3.The text data is also stored on its own pages and is indicated in sys.allocation_units as type 2. After running the code in Listing 3, run the code in Listing 2 again to see that the sys. partitions rows for the test1 table now join with 10 rows in sys.allocation_units. Each of the five partitions in sys.partitions joins with two allocation units, one for the in-row data and one for the off-row data. The one row for the single partition in test2 joins with two rows in sys.allocation_units, one for the in-row data and one for the text data.
Where's the Data Stored?
You've seen examples of queries that retrieve metadata about tables; the next step is to determine where in your database files the data for those tables is stored. SQL Server 2000's sysindexes table contains three columns that indicate where data is located: call first, root, and firstIAM. (For more information about these undocumented columns and how to interpret them, see "Index Internal Information," January 2005, InstantDoc ID 44572.) In SQL Server 2005, not only are these columns undocumented, the catalog view that contains them is also undocumented.
If you really want to see these columns containing page locations in a hexadecimal format, you can join sys.partitions with sys.system_internals_allocation_units instead of with sys.allocation_units. This view has the same columns as sys.allocation_units, plus three more: first_page, root_page, and first_iam_page. I'll leave it to you to write the query and examine the values on your own, if you're interested. I will point out that, just as in SQL Server 2000, it's unnecessary to retrieve page-location information in hex format because we can use the DBCC IND command to get page numbers in decimal. (I discuss DBCC IND in "Index Internal Information" and other Inside SQL Server articles.) I'll revisit this command in upcoming articles dealing with accessing physical-page information.
New and Better Metadata
As you start exploring SQL Server 2005's metadata, you might feel that accessing needed information is much more awkward than doing so in SQL Server 2000. I suspect that most of that awkwardness is merely a learning curve as you figure out new ways to perform old tasks. The fact that the data is in multiple views instead of just one system table gives SQL Server much greater flexibility and room for expansion. And the fact that almost all the metadata is available in views and property functions, instead of nontabular DBCC commands, means that you can use SQL queries to combine and filter the data any way you choose to create the queries that will be most useful for you.