Clustered index vs composite index. Which one is better?

Question

I am using Microsoft SQL Server 2017. I have table called ProductMapping. Below is the table structure:

CREATE TABLE [dbo].[Accommodation_ProductMapping](
    [ProductMappingId] [uniqueidentifier] NOT NULL,
    [AccommodationId] [uniqueidentifier] NULL,
    [SupplierId] [uniqueidentifier] NULL,
    [SupplierId] [varchar](50) NULL,
    [SupplierName] [varchar](50) NULL,
    [SupplierProductReference] [nvarchar](255) NULL,
    [ProductName] [nvarchar](500) NULL,
    [CountryName] [nvarchar](255) NULL,
    [CountryCode] [nvarchar](50) NULL,
    [CityName] [nvarchar](255) NULL,
    [CityCode] [nvarchar](100) NULL
)

This table has 15 billion data. I have created the non cluster & composite indexes on this table. Below are the details:-

CREATE NONCLUSTERED INDEX [IDX_CityCode] ON [dbo].[ProductMapping]
(
    [CityCode] ASC
)

CREATE NONCLUSTERED INDEX [IDX_CountryCode] ON [dbo].[ProductMapping]
(
    [CountryCode] ASC,
)

CREATE NONCLUSTERED INDEX [IDX_CountryCityCode] ON [dbo].[ProductMapping]
(
    [CountryCode] ASC,
    [CityCode] ASC
)

CREATE NONCLUSTERED INDEX [IDX_ProductCountryCityCode] ON [dbo].[ProductMapping]
(
    [ProductName] ASC,
    [CountryCode] ASC,
    [CityCode] ASC
)

CREATE NONCLUSTERED INDEX [IDX_AccommodationCountryCityCode] ON [dbo].[ProductMapping]
(
    [AccommodationId] ASC,
    [CountryCode] ASC,
    [CityCode] ASC
)

I am able to get the data without any issue.

I just want to know is there any unused or redundant Indexes which I created above?

Also, I have created a composite index "IDX_CountryCityCode" on country and city code, so do I need an individual non-cluster index for "CityCode" and "CountryCode" (e.g. IDX_CityCode and IDX_CountryCode).

Thank you in advance.

EDITED

I just want to know if I remove all the above indexes (i.e. [IDX_CityCode], [IDX_CountryCode], [IDX_CountryCityCode], [IDX_ProductCountryCityCode] & [IDX_AccommodationCountryCityCode]) and put them all in one composite index as below. Is this going to work or is it best approach?

CREATE NONCLUSTERED INDEX [IDX_CityCountryAccommodationProduct] ON [dbo].[ProductMapping]
(
    [CityCode] ASC,
    [CountryCode] ASC,
    [AccommodationId] ASC,
    [ProductName] ASC
)

You don't need the `IDX_CountryCode` index because the `IDX_CountryCityCode` also has `CountryCode` as the first column. The table should probably have a clustered index but the best candidate depends on your queries. — Dan Guzman, Apr 23 '19 at 09:49
A composite index can also be the clustered index. The only problem here is the use of `uniqueidentifier` for the Ids. Unless the GUID values are created using a sequential algorithm (eg `NEWSEQUENTIALID()` instead of `NEWID()`), they *can't* be used for a clustered index. What indexes you use depends on the actual queries though. — Panagiotis Kanavos, Apr 23 '19 at 09:56
`I just want to know if I remove all the above indexes` depends on the *queries*. The order of the columns in an index matters a lot. An index won't help as much unless the query optimizer can use the first column to restrict rows. More selective columns should come first, which means `IDX_CountryCityCode` and `IDX_ProductCountryCityCode` have the wrong order. If you replace all indexes with `IDX_CityCountryAccommodationProduct` and try to search by `ProductName` the optimizer may ignore the index altogether — Panagiotis Kanavos, Apr 23 '19 at 10:02
@user2043071, the leftmost index key columns must be used in `WHERE` and `JOIN` clauses to be used efficiently, So again, it will depend on your queries. — Dan Guzman, Apr 23 '19 at 10:04
@PanagiotisKanavos - Thank you. I just confused why IDX_CountryCityCode and IDX_ProductCountryCityCode have the wrong order. — user2043071, Apr 23 '19 at 10:15
@user2043071 because a country code is less selective than a city code. The optimizer may decide that there are so few `countrycode` entries that seeking is pointless and just scan the entire index. Check [Role of selectivity in index scan/seek](https://stackoverflow.com/questions/12155853/role-of-selectivity-in-index-scan-seek). — Panagiotis Kanavos, Apr 23 '19 at 10:17

score 0 · Answer 1 · answered Apr 23 '19 at 11:18

Your question is quite broad. The purpose of this answer is to give you a flavor of the issues that you are bringing up, because the question does not seem broad -- just a binary choice between two different options.

Indexes are created to optimize queries (and also to enforce unique constraints, but that is a different matter).

You have not shown any queries, so it is not possible to say which set of indexes is optimal. However, they are not equivalent.

Your composite index can be used for instance on the following where clauses:

where CityCode = @CityCode
where CityCode = @CityCode and CountryCode = @CountryCode
where CityCode = @CityCode and CountryCode = @CountryCode and AccommodationId = @AccommodationId
where CityCode = @CityCode and CountryCode = @CountryCode and AccommodationId = @AccommodationId and ProductName = @ProductName

What is important is that the columns are used in the order defined in the index (and not the order they are presented in the where clause.

This index cannot be used if @CityId is not present. So, this index is not appropriate for:

where CountryCode = @CountryCode
where CountryCode = @CountryCode and AccommodationId = @AccommodationId
whereCountryCode = @CountryCode and AccommodationId = @AccommodationId and ProductName = @ProductName

With the four indexes, one of them can be used. The optimizer tries to use the "best" index when more than one can be used. Sometimes, the optimizer does not choose the best one.

The title of your question is about clustered versus non-clustered indexes. This brings up other issues -- particularly how data is inserted and updated. Clustered indexes impose constraints on how the data is stored, so they can have a significant impact on the performance of data modifications.

There are many more nuances to indexes. Fundamentally, though, they are not driven by data structure but by queries (although in some cases, such as a normalized data model, it is obvious that certain types of queries will be needed).

Clustered index vs composite index. Which one is better?

1 Answers1