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

ahmed salah 3,216 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.
12,596 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points 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.

    1 person found this answer helpful.
    0 comments No comments

  2. Bert Zhou-msft 3,421 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