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

SQL Server Administration
1

Hi,

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:

USE CR_YPLS8_NEW_QB;

select

sys.objects.name,

sys.objects.object_id,

sys.partitions.index_id,

sys.system_internals_allocation_units.first_page,

sys.system_internals_allocation_units.first_iam_page

from sys.objects, sys.partitions,
sys.system_internals_allocation_units

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?

Thanks

2

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

3

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.

4

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

SELECT *
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.