Sql-server – When all you indexes have included all the other columns, do you still have the penalty for a wide clustered index

clustered-indexindexsql serverunique-constraint

So I hear sometimes that I should not use a clustered index on a wide column, mainly because it is being referenced in all other indexes.

When all my other indexes on this table include all other columns – is there still this (extra) penalty?

(This table has no external references on the clustered index by other tables)

For reference my table looks a bit like this:

PartOfNaturalKey1    int
PartOfNaturalKey2    varchar(10)
PartOfNaturalKey3    varchar(20)
PartOfNaturalKey4    int
Value                varchar(10)
LastChanged          datetime2(7)
Invalid              bit

The Natural Key is unique and will (almost never) change.

Best Answer

If you have in every index all columns and you have a PK , and another 2 indexes , one on Value and one on LastChanged , you really multiplied the table by 4. This will make the table really slow for all operations.

Add an ID column , make it PK and build indexes as you need based on the statements that run on the table.

Don't create an index on Invalid, it's a bit column , it's usually not useful .

Try to keep a rule that all indexes should be smaller in size than the table.

Related Solutions

Sql-server – Should a table have a clustered index even if it doesn’t have appropriate fields for it

1) IF PlayerId is assigned with NEWSEQUENTIALID, you could consider that as the clustered index.

2) Otherwise, you can add an IDENTITY and make that clustered (questionable benefit, since all access will be through the PK you have already established).

3) Or you can leave it as a heap - with appropriate non-clustered indexes.

My order of preference would be 1, 3, 2 assuming you can't change the uniqueidentifier to an IDENTITY instead.

Can you explain why you are using uniqueidentifier in the first place? - that may have some bearing on this.

Sql-server – Question about non-clustered index storage in SQL Server

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.

Best Answer

Related Solutions

Sql-server – Should a table have a clustered index even if it doesn’t have appropriate fields for it

Sql-server – Question about non-clustered index storage in SQL Server

Related Question