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

Question

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

keithy_sunny

Mr or Mrs. 500

Points: 566
More actions
November 13, 2015 at 7:57 pm

#322513

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%28v=sql.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?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

It's a whole lot easer than that. If the table has a Clustered index, the table will be based on a B-TREE. If it's a HEAP, it will be based on an IAM.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

Jeff beat me to it. Tables are either HEAPS or Clustered Tables (e.g. a table with a clustered index which means it's a B-Tree).

I would add, you could run this query for a list of all your heaps:

SELECT t.name, t.object_id, i.type_desc

FROM sys.tables t

JOIN sys.indexes i

ON t.object_id = i.object_id

AND i.type_desc = 'HEAP';

If your this query does not return your tables then they are not heaps.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

keithy_sunny (11/13/2015)
However, with the help of DBCC Page command, I find the 'cardfa' table is actually stored in heap

What, from DBCC Page, led you to conclude that?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

keithy_sunny (11/13/2015)
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%28v=sql.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?
Thanks

Please see my script at this link[/url] for more on how to get HEAP or clustered index size and info

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉