are create noncluster index on cluster index already exist is wrong ?

ahmed salah 3,131 Reputation points
2022-06-23T06:24:15.817+00:00

I work on sql severver 2019 I need to aks question

suppose i have table have 10 milions rows and i have cluster index on column StrtCountriesTiersID it as below

CREATE TABLE [dbo].[StrtCountriesTiers](  
	[StrtCountriesTiersID] [int] IDENTITY(1,1) NOT NULL,  
	[CountryId] [int] NULL,  
	[TierId] [int] NULL,  
	[ModifiedDate] [datetime] NULL,  
	[CreatedDate] [datetime] NOT NULL,  
	[Modifiedby] [int] NULL,  
	[CreatedBy] [int] NULL,  
	[CountryYear] [int] NULL,  
 CONSTRAINT [PK__StrtCountriesTiers__FEFAF27527F7A1C3] PRIMARY KEY CLUSTERED   
(  
	[StrtCountriesTiersID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
ALTER TABLE [dbo].[StrtCountriesTiers] ADD  CONSTRAINT [DF__StrtCountriesTiers__ModifiedDate__215D38B9]  DEFAULT (getdate()) FOR [ModifiedDate]  
GO  
ALTER TABLE [dbo].[StrtCountriesTiers] ADD  CONSTRAINT [DF__StrtCountriesTiers__Creat__215D38B9]  DEFAULT (getdate()) FOR [CreatedDate]  
GO  

so my question

Are creating noncluster index on column StrtCountriesTiersID after that
and include on nonclustered indexes will enhance performance
or that will make issue on performance and index seek

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,565 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 68,196 Reputation points Microsoft MVP
    2022-06-23T22:06:51.567+00:00

    If you have a clustered index on a column, there is rarely any point in adding a non-clustered index on the same column.. However, if you have some really funny queries, it could actually help. As a crazy example:

       SELECT * FROM tbl WHERE StrtCountriesTiers % 19234 = 123  
    

    Is likely to run faster if you also add a non-clustered index.

    No comments

  2. Bert Zhou-msft 3,386 Reputation points
    2022-06-23T06:53:20.057+00:00

    Hi,@ahmed salah

    If I understand correctly , you want to add a non-clustered index on the clustered index column right ? The column of the clustered index acts as a row locator in this case , it is best that you add the unique key StrtCountriesTiersID as an included column, and you need to display the added column when the position does not match the query.

    Bert Zhou