Index in SQL Server 2005

Question

Index in SQL Server 2005

Dung Dinh

SSCrazy

Points: 2513
More actions
September 12, 2010 at 8:13 pm

#146953

Hi all,
I have a question and want a support
Can we create a Non-Clustered Index which contain a field which is key in Clustered Index
Eg: I create a table has 3 column
Create table A
(
ReportDate
MerchantNumber
MerchantName
SaleCount
)
Then I create a Clustered Index P on ReportDate column
So, Can I create a Non-Clustered Index A on ReportDate,MerchantNUmber?
Thanks,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

Dung Dinh (9/12/2010)
I have a question and want a support
Can we create a Non-Clustered Index which contain a field which is key in Clustered Index
Eg: I create a table has 3 column
Create table A
(
ReportDate
MerchantNumber
MerchantName
SaleCount
)
Then I create a Clustered Index P on ReportDate column
So, Can I create a Non-Clustered Index A on ReportDate,MerchantNUmber?

Yes.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Dung Dinh SSCrazy Points: 2513 More actions · Answer 2

Thanks,

Another way,the key in Clustered Index will be included automatically into Non-Clustered Index (SQL 2005), right?

If YES,come back my example.

I created Clusterd Index on ReportDate.

Then create a Non-Clustered Index with 2 ways:

1: Non-Clustered Index on MerchantNumber

2: Non-Clustered Index on ReportDate,MerchantNumber

=> What difference between way1 and way2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Yes, it'll be a key column if the index is not unique and an include column if the index is unique.

Difference is in column order. If the clustering key is implicitly added to the key, it's added as the last column, and column order in an index matters. So 1 will actually be MerchantNumber, ReportDate which, depending on what kind of operations your doing, may make more sense than the ReportDate, MerchantNumber index.

http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/

Gail Shaw
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

Dung Dinh SSCrazy Points: 2513 More actions · Answer 4

Thank GilaMonster,

You mean that the key in Clusterd Index will be added implicitly into Non-Clustered Index(s),right?

Sorry if I'm missunderstanding your idea.

By the way, If I have a query

DECLARE @ReportDate datetime

,@MerchantNumber varchar(20)

SELECT SaleCount

FROM A

WHERE MerchantNumber=@MerchantNumber AND ReportDate=@ReportDate

And I have a Non-Clustered Index on MerchantNumber,ReportDate. The question is here:

How does orderly columns in the Non-Clustered Index impact to WHERE statement?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Dung Dinh (9/13/2010)
You mean that the key in Clusterd Index will be added implicitly into Non-Clustered Index(s),right?

Yes

How does orderly columns in the Non-Clustered Index impact to WHERE statement?

Did you read those two links I posted?

Gail Shaw
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