0

As a starting point, is it better to use the surrogate identity column (SaleItemID) as the clustered index. Or look for a column that I expect will be used often when reading data from the table but it will not be unique (SalesCategoryID).

I tune the indexes as the tables grow, but I'm looking for a good starting point for my clustered index.

-- OPTION #1: clustered index on primary key (so, unique)
CREATE TABLE dbo.SaleItems
(
SaleItemID INT IDENTITY PRIMARY KEY CLUSTERED,
SalesCategoryID int NOT NULL,
SaleItemName varchar (100) NOT NULL,
SalesPrice smallmoney NULL
);


-- OPTION #2: clustered index on column that matches common filter (not unique)
CREATE TABLE dbo.SaleItems
(
SaleItemID INT IDENTITY PRIMARY KEY,
SalesCategoryID int NOT NULL,
SaleItemName varchar (100) NOT NULL,
SalesPrice smallmoney NULL
);
CREATE CLUSTERED INDEX IX_SaleItems_SalesCategoryID ON dbo.SaleItems (SalesCategoryID); 

1 Answer 1

1

--OPTION #2:

Check the selectivity of column SalesCategoryID

Declare @distinctcount int,@Totoalrows int
Select @distinctcount =count(distinct SalesCategoryID) from SaleItems

select @Totoalrows =count(*) from SaleItems

So Selectivity=Select @distinctcount /cast(@Totoalrows as decimal(5,2))

Ideal Selectivity =1 i.e that is unique key. So Selectivity should be very near to 1.

If there is million of record,SalesCategoryID will will be scatter here and there in many data pages. So insert/update performance will suffer.

Also in case of Cost Base Plan,it is not guaranteed that,sql optimizer will choose CLustered Index Seek.

OPTION #1:

SaleItemID as Clustered Index.

SalesCategoryID as NonClustered Index.

In this case insert/update wont suffer,since Sql optmizer will append data in last page.

NonClustered index, SalesCategoryID will be in leaf level and number of NonClustered index data pages will be less.Since data page will only contain value of INT SalesCategoryID.So optmizer might choose this plan. It will read SalesCategoryID from Non clustered index page and LOOK UP other detail directly in SaleItemID Clustered Index Data page.

One step may be more but number of data pages read will be less,so cost will be less

3
  • Thanks @KumarHarsh, especially for the Selectivity formula!
    – Craig
    Commented Jun 27, 2023 at 13:37
  • @Craig, Sorry for my language.
    – KumarHarsh
    Commented Jun 28, 2023 at 4:43
  • In my example, the PK identity column SaleItemID is guaranteed to be more selective than SalesCategoryID so that will be my clustered index. And so I'm selecting this as my answer.
    – Craig
    Commented Jul 7, 2023 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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