Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table people with two non-clustered indexes.

create table people(
 id_person int,
 first_name varchar(50),
 last_name varchar(50),
 city varchar(100),
 state char(2),
 zip_code int
)

CREATE INDEX id_first_name_last_name ON people(first_name, last_name, id_person)
CREATE INDEX id_last_name_first_name ON people(id_person, last_name, first_name)

Each index refers to the same columns but in a different order.

-- Insert some rows

insert into people values(1,'joe','smith','new york', 'NY', 10701)
insert into people values(2,'john','smith','new york', 'NY', 10701)
insert into people values(3,'joyce','smith','new york', 'NY', 10701)
insert into people values(4,'jocelyn','smith','new york', 'NY', 10701)

The size of each index is as follows

TableName   IndexName            IndexID    Indexsize(KB)
people          NULL                     0           16
people          id_first_name_last_name  2           16
people          id_last_name_first_name  3           16

Each index is using 16KB.

Now if I add a clustered index on id_person

create clustered index ix_id_people on people(id_person)

I end up with the same amount of space being used by each index.

TableName   IndexName            IndexID    Indexsize(KB)
people          NULL                     1           16
people          id_first_name_last_name  2           16
people          id_last_name_first_name  3           16

The only difference is now instead of a HEAP, I have a clustered index.

I was expecting that with a clustered index on id_person, the storage engine wouldn't need to include id_person anymore in each non-clustered index.

Questions:

Are the values for each column referenced by a non-clustered index stored only for the first non-clustered index and then referenced by all additional non-clustered indexes?

If a non-clustered index references the clustered index column, are the values from that column copied physically onto each non-clustered index or just simply referenced?

Update 14:55

Here is the query being used to calculate index size

SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id
share|improve this question
    
Is ID the primary key? –  TomTom Jul 22 '13 at 12:09
    
Not in this situation –  Craig Efrein Jul 22 '13 at 12:10
    
THen the clustered index wont magically reduce storage. I suggest you make it the PK and use a unique index for the rest. Assuming you - basically - do not really have a 16kb situation ;) –  TomTom Jul 22 '13 at 12:35
4  
@TomTom - It is irrelevant whether or not it is the PK to storage. –  Martin Smith Jul 22 '13 at 12:47

2 Answers 2

up vote 7 down vote accepted

Nonclustered indexes always include a row locator.

For a heap this will be an 8 byte RID (File:Page:Slot). On a table with a clustered index it will be the clustered index key column(s). And it will always be the copied values not a pointer to the values. This duplication of CI key values into all non clustered indexes is why it is often recommended that the CI key be narrow and not frequently updated.

In the table shown in the question the Clustered index key is a 4 byte integer and potentially may also include a 4 byte uniqueifier for any duplicate key values.

In your case as the NCIs are not declared as unique the CI key will be appended to the NCI key.

For unique non clustered indexes the CI key would be added as included column(s) in the leaf pages unless explicitly made part of the key.

See Kalen Delaney: More About Nonclustered Index Keys for some additional information about how you can see this for yourself.

With these 4 rows of data all three indexes only consume a single 8KB data page.

SELECT index_id,
       index_level,
       page_count,
       record_count
FROM   sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('people'), NULL, NULL, 'DETAILED') 

Returns

+----------+-------------+------------+--------------+
| index_id | index_level | page_count | record_count |
+----------+-------------+------------+--------------+
|        1 |           0 |          1 |            4 |
|        2 |           0 |          1 |            4 |
|        3 |           0 |          1 |            4 |
+----------+-------------+------------+--------------+

The additional page shown in use by sys.allocation_units.total_pages is an IAM page. This is not used for storing data but just for tracking the pages and extents comprising the index.

share|improve this answer
    
@martin-smtih, even with each non-clustered index defined as unique, the Indexsize is still showing up as 16KB for each index (clustered and non-clustered). Is that really what's being physically used to store each one? –  Craig Efrein Jul 22 '13 at 12:36
    
@CraigEfrein - Yes. 16KB is only two pages. One data page and one IAM page. This is the smallest an index can be if it contains even a single row. Even deleting all rows will still not dealloocate these pages (but truncate does) –  Martin Smith Jul 22 '13 at 13:40
    
thank you for the answer. I will get back shortly. –  Craig Efrein Jul 22 '13 at 13:48

You need to test with a lot more data than four rows to see any true difference in space used because of the granularity of SQL Server's storage engine. 16Kb is just two of SQL Server's "native" 8Kb blocks - in this case one page (actually, less than a full page) worth of data with one tare - depending on activity before this test (did you drop the tables/indexes retesting) and properties like fill factor, an index will take more space than absolutely needed. Being just one page larger that is really needs to be is not meaningful, one extra page (8Kb) is just random noise even if you are only expecting 8Kb to start with and you will likely find once the table is much more populated that this difference becomes vanishingly small. The difference between an expectation of 8Mb and seeing 16Mb instead would be a much more meaningful discrepancy.

I was expecting that with a clustered index on id_person, the storage engine wouldn't need to include id_person anymore in each non-clustered index.

An index will always contain all the named columns, so checking a row matches the query requires comparing the one index row and only that one index row and it only has to reference other structures (the heap or clustered index) once a row matches all the predicates that the index could satisfy. That way if no rows match, only that index needs to be scanned/seeked to prove this rather than having to fiddle around with the heap/cluster to check some of the predicates that would otherwise be covered by the index.

share|improve this answer
    
I am reading both answers carefully. its what's being actually stored that is of interest in this situation, because there are a lot of indexes in the database I'm analyzing that are very similar and that are taking up a lot of space. –  Craig Efrein Jul 22 '13 at 13:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.