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,791 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,556 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.6K 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
  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