Whether my table is stored as heap or b-tree?


I have a SQL Server 2005 database. It’s .MDF & .LDF can
be downloaded at https://www.dropbox.com/s/uj2mw410jm2m078/MyDB.rar?dl=0

Now I want to know whether two table are stored as heap or
b-tree(linked list), so I run the following commands:








from sys.objects, sys.partitions,

where (sys.partitions.object_id = sys.objects.object_id) and
(sys.system_internals_allocation_units.container_id =
sys.partitions.partition_id) and

((name = 'cardfa') or (name = 'pcspkc'));

Where 'cardfa' and 'pcspkc' are the two user tables I want
to investigate.

The result is below:

Based on
https://technet.microsoft.com/en-us/library/ms189051(v=sql.105).aspx, since
both table’s index_id is 1, they are all stored as b-tree(linked list).
However, with the help of DBCC Page command, I find the 'cardfa' table is
actually stored in heap, which is inconsistent with the search result. Why?


why not just look at the table in object explorer and see if it has a clustered index? Sure you can do it programatically, but for a one-off, the GUI is good

btw a b-tree is not stored as a linked list. though pages are linked together at the same level in the hierarchy

GUI is OK but cannot be performed automatically.

Sorry for my mistake. Yes, I just mean the leaf-level data pages in the b-tree are linked list.

You don't need anything nearly that complex to determine if a table is a heap or instead it has a clustered index.

FROM sys.indexes
WHERE object_id IN (OBJECT_ID('dbo.cardfa', 'dbo.pcspkc') AND index_id IN (0, 1)

If its index_id is 0, it's a heap, if 1, it has a clustered index. A clustered index is a modified form of b-tree (b-tree+ and then some) to improve overall efficiency.