Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2013
    Posts
    19

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

    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_pa ge
    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:

    Click image for larger version. 

Name:	13bbea44e1f3d623812b777b99a2045150cb55dc.png 
Views:	3 
Size:	1.5 KB 
ID:	16631

    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?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •