11-13-15, 22:00 #1Registered User
- Join Date
- Apr 2013
Unanswered: 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, sys.system_internals_allocation_units
where (sys.partitions.object_id = sys.objects.object_id) and (sys.system_internals_allocation_units.container_i d = 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/...ql.105%29.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?
11-14-15, 10:09 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
By definition, a table with a clustered index can't be stored as a heap.
If you explain what you did using DBCC PAGE that leads you to think that the table is stored as a heap, we can probably help you understand what is causing your confusion.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.