Sql-server – Is the table is stored as heap or b-tree

sql server

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:

table result

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(the data pages in the leaf level is in linked list).

However, in the result table, the first row has first_page value (filenum, pagenum) = (1, 0x6F2A8) which is an invalid pointer.

The second row has first_page value (filenum, pagenum) = (1, 0x5941) = (1, 22849).

After I using the following commands:

DBCC TRACEON(3604);
DBCC page(CR_YPLS8_NEW_QB, 1, 22849, 3);

I get the following result:

After checking each column in each slot, I find the data is completely different from the actual data stored in 'cardfa' table. So the first_page for the second row is also invalid.

And I then find the 'cardfa' table is actually stored in heap and can be referenced by first_iam_page instead of first_page, which is inconsistent with the search result. Why?

Thanks

Best Answer

If your table has a clustered index it is stored as a b-tree otherwise it is a heap.

Related Solutions

Sql-server – Aren’t two writes required to update a clustered index record

I think the problem here is a difference in terminology.

The "number of writes" that's usually referred to is the number of object accesses, rather than the number of pages that get touched by the physical operation.

The reason why that's usually used as a metric in discussion is because it's a more "stable" and meaningful number to talk about. As we're getting into here, the number of pages touched by an INSERT statement for even a single row depends on many factors, so it's not a very useful quantity outside your own environment and situation.

The one thing I would pick at from the article quote is this (emphasis mine):

One write for inserting the row, and one write for updating the non-clustered index.

This may be confusing. Inserting a row into the base table would involve an insert to the base table, and also an insert into each nonclustered index (ignoring special index features), not an update.

So if a record has to be updated, say the value 1 has to be updated to 7, won't the update need to be applied to both the key in the clustered index top node (this may, in cases, cause a re-structuring of the entire structure) and the corresponding value in the record in the leaf-page?

Yes, assuming the column that was updated is in the index key. However, this is still a single object access, and hence a "single write."

Sql-server – SQL Insert Statement Persists Two Records When I Need Just One

From what I can see you have two problems. The first is the doubling. At a guess it is because of the SELECT statement in your INSERT statement.

       SELECT @NewMetricID, 
            NULL, 
            sm.sortorder, 
            ISNULL(sm.MetricOrder, 1), 
            ISNULL(sm.CategoryOrder, 1), 
            sm.RptCurrentGroup, 
            'System', 
            GetDate(), 
            NULL, 
            NULL, 
            'N', 
            NULL,
            NULL
        FROM Metric_Instance mi 
        INNER JOIN Shared_Metrics sm on mi.MetricID = sm.MetricID
        WHERE mi.MetricID = @CurrentMetricID 
            AND mi.MetricDisposition <> 'Suspended' 
            AND mi.isDeleted <> 'Y'
            AND sm.isDeleted <> 'Y';

I would be willing to bet your query is returning more than one row. Any time you have a join like that you run a risk of creating duplicate rows. The easy way to fix it is to do this:

       SELECT DISTINCT @NewMetricID, 
            NULL, 
            sm.sortorder, 
            ISNULL(sm.MetricOrder, 1), 
            ...
            ...
            ...

The only real change is that that I'm putting a DISTINCT at the top of the query. This means that if there are any VALID duplicates you are still going to pick them up. You are not actually referencing any columns from Metric_Instance so you could change it from a JOIN to a WHERE EXISTS (SELECT 1 ...

On to problem two. In your code you are using scope_identity(). This is only going to return the last identity value created. If you insert more than one row the previous rows are going to be missed. The best solution here is to get rid of the cursor and change this into a batch process. INSERT all possible rows initially using the OUTPUT clause to dump a list of identity values and CurrentMetricIDs into a temp table. Then do your update using that temp table to update all of the values at once. Not only will this fix your problem but will probably run quite a bit faster as well.

Best Answer

Related Solutions

Sql-server – Aren’t two writes required to update a clustered index record

Sql-server – SQL Insert Statement Persists Two Records When I Need Just One

Related Question