When I ran Database Engine Tuning Advisor, it suggests to create a non-clustered index on the column where a clustered index is already present.
Can somebody give me some explanation why it is saying like that?
Because the database tuning advisor always suggests the absolute best index for the query, that's why it's so dangerous to blanket accept its recommendations.
The clustered index is the largest index on the table, so the most IO to seek and read. Hence if the query wants just the clustered index column, the clustered index, while a very good index to use is not the absolute best and hence DTA will recommend a nonclustered index on tha same column.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass