【indexing-1】Unique and non-unique SQL Server indexes on a heap table

In the upcoming weblog postings I want to work out the differences between unique and non-unique indexes in SQL Server. I assume that you already know the concepts about clustered- and non clustered indexes and how they are used in SQL Server.

In the past I’ve done a lot of trainings and consulting regarding SQL Server performance tuning and it seems that some people doesn’t know the differences and implications(n.含义; 含蓄，含意，言外之意; ) between unique and non-unique indexes. And as you will see in the upcoming postings there are really big differences how SQL Server stores those two variants( n. 变种; 变型; 变体) that impact the size and the efficiency( n. 功效; 效率，效能; ) of your indexes.

Let’s start today with unique and non unique non clustered indexes on a table without a clustered index, a so-called heap table in SQL Server. The following listing shows how to create our test table and populate it with 80.000 records. Each record needs 400 bytes, therefore SQL Server can put 20 records on each data page. This means that our heap table contains 4.000 data pages and 1 IAM page.

-- Create a table with 393 length + 7 bytes overhead = 400 bytes 
-- Therefore 20 records can be stored on one page (8.096 / 400) = 20,24 
CREATE TABLE CustomersHeap 
( 
    CustomerID INT NOT NULL, 
    CustomerName CHAR(100) NOT NULL, 
    CustomerAddress CHAR(100) NOT NULL, 
    Comments CHAR(189) NOT NULL 
) 
GO

-- Insert 80.000 records 
DECLARE @i INT = 1 
WHILE (@i <= 80000) 
BEGIN 
    INSERT INTO CustomersHeap VALUES 
    ( 
        @i, 
        'CustomerName' + CAST(@i AS CHAR), 
        'CustomerAddress' + CAST(@i AS CHAR), 
        'Comments' + CAST(@i AS CHAR) 
    )     
    SET @i += 1 
END 
GO

-- Retrieve physical information about the heap table 
SELECT * FROM sys.dm_db_index_physical_stats 
( 
    DB_ID('NonClusteredIndexStructureHeap'), 
    OBJECT_ID('CustomersHeap'), 
    NULL, 
    NULL, 
    'DETAILED' 
) 
GO

After the creation of the heap table and the data loading, you can now define a unique and non-unique non-clustered index on the column CustomerID of our heap table. We will define both indexes on the same column so that we can analyze the differences between unique- and non-unique non-clustered indexes.

-- Create a unique non clustered index 
CREATE UNIQUE NONCLUSTERED INDEX IDX_UniqueNCI_CustomerID 
ON CustomersHeap(CustomerID) 
GO  

-- Create a non-unique non clustered index 
CREATE NONCLUSTERED INDEX IDX_NonUniqueNCI_CustomerID 
ON CustomersHeap(CustomerID) 
GO

If you want to define a unique non-clustered index on a column that doesn’t contain unique data, you will get back an error message from SQL Server. Important to know is that SQL Server creates a non-unique non-clustered index if you don’t specify the UNIQUE property when creating a non-clustered index. So by default you will always get a non-unique non-clustered index!

 After the creation of both indexes you can analyze their size, their index depth, their size etc( adv.等等及其他; 诸如此类;). with the DMV sys.dm_db_index_physical_stats. You can to pass in as the 3rd parameter the index-id. The IDs of all non-clustered indexes starts at 2, therefore the first non-clustered index gets the ID 2 and the second one the ID 3.

-- Create a unique non clustered index 
-- Retrieve physical information about the unique non-clustered index 
SELECT * FROM sys.dm_db_index_physical_stats 
( 
     DB_ID('PersonalSystem'), 
    OBJECT_ID('CustomersHeap'), 
    2, 
    NULL, 
    'DETAILED'
) 
GO

-- Retrieve physical information about the non-unique non-clustered index 
SELECT * FROM sys.dm_db_index_physical_stats 
( 
    DB_ID('PersonalSystem'), 
    OBJECT_ID('CustomersHeap'), 
    3, 
    NULL, 
    'DETAILED'
) 
GO

As you can see from both outputs, the index root page of the unique non-clustered index is occupied( adj.已占用的，在使用的，无空闲的;) of around 24%, where the index root page of the non-unique non-clustered index is occupied of around 39%, so there must be a difference in the storage format of unique/non-unique non-clustered indexes on a heap table! In the next step we create a simple helper table that stores the output of the DBCC INDcommand. The structure of this helper table is directly taken from the excellent book SQL Server 2008 Internals.

-- Create a helper table 
CREATE TABLE sp_table_pages 
( 
   PageFID TINYINT, 
   PagePID INT, 
   IAMFID TINYINT, 
   IAMPID INT, 
   ObjectID INT, 
   IndexID TINYINT, 
   PartitionNumber TINYINT, 
   PartitionID BIGINT, 
   iam_chain_type VARCHAR(30), 
   PageType TINYINT, 
   IndexLevel TINYINT, 
   NextPageFID TINYINT, 
   NextPagePID INT, 
   PrevPageFID TINYINT, 
   PrevPagePID INT, 
   PRIMARY KEY (PageFID, PagePID) 
) 
GO

After the creation of this helper table we can dump out all pages that are belonging to our non-clustered indexes to this helper table with the following two calls to DBCC INC in combination with the INSERT INTOstatement:

-- Write everything in a table for further analysis 
INSERT INTO sp_table_pages 
EXEC('DBCC IND(PersonalSystem, CustomersHeap, 2)') 
GO

-- Write everything in a table for further analysis 
INSERT INTO sp_table_pages 
EXEC('DBCC IND(PersonalSystem, CustomersHeap, 3)') 
GO

Now we can start analyzing our non-clustered indexes by using the undocumented DBCC PAGE command. You can find more information about this great command on Paul Randal’s weblog. To get some information back from DBCC PAGE you have to enable the flag 3604 of DBCC:

DBCC TRACEON(3604) 
GO

Let’s dump out the index root page of our unique non-clustered index by the following command:

posted @ 2017-06-26 11:11  FH1004322  阅读(124)  评论(0)    收藏  举报