Introduction to Indexes

Question

Post reply

Introduction to Indexes

Viewing 15 posts - 46 through 60 (of 124 total)

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

Charles Kincaid SSChampion Points: 13593 More actions · Answer 1

GilaMonster (11/3/2009)
What are you waiting for?

Methinks he is waiting for your next installment.

ATBCharles Kincaid

Allister Reid SSCrazy Points: 2666 More actions · Answer 2

Charles Kincaid (11/3/2009)
GilaMonster (11/3/2009)
What are you waiting for?
Methinks he is waiting for your next installment.

Me too!

Thanks for the great article.

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

Charles Kincaid (11/3/2009)
GilaMonster (11/3/2009)
What are you waiting for?
Methinks he is waiting for your next installment.

Next week.

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

bwilliams-1049831 Mr or Mrs. 500 Points: 532 More actions · Answer 4

Good introduction to indexes!

I hope in the 2nd or 3rd you will talk about 2 of my favorite index terms Cardinality and Distribution.

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

I do, I think (wrote them a week ago and am currently brain-dead tired after PASS). Probably not in the detail hyou want. Any specific questions, ask them in the comments for the articles.

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

AndrewJacksonZA SSCarpal Tunnel Points: 4143 More actions · Answer 6

Thanks for the article Gail.

Correction:

In the "Seeks" section, you have "...there must be a SARGable2 predicate..." but then as a reference you have "(1) SARGable is a made-up word, constructed from..."

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

Blame Steve. 😀 The footnotes were correctly numbered in the original word doc. There should be 3 footnotes, will go in and edit later.

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

Douglas Osborne-456728 SSCommitted Points: 1569 More actions · Answer 8

Gail,

I have a simple index question I cannot Google an answer on.

If I have a table MyData with a PK of MyDataID, should I ever have an index the has MyDataID in it, eg

CREATE NONCLUSTERED INDEX [IX_MyData_MyDataID_LastName] ON [dbo].[MyData]

(

[MyDataID] ASC,

[LastName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

I always thought that every non-PK index has the PK data kind of built in to it as a pointer.

Is this true? Do I never need to have the PK Column in any of the indexes I build?

Thanks,

Doug

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

Maybe.

By primary key, do you mean clustered index? The pk is only clustered by default, it can be enforced by a nonclustered index. I'm going to assume you meant clustered index.

Do you have any queries that filter on both MyDataID and LastName? If so, that index is useful (though I'd probably switch the order of the columns around, since the cluster's there to handle any queries that filter on just MyDataID)

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

bwilliams-1049831 Mr or Mrs. 500 Points: 532 More actions · Answer 10

The primary key field should never be used as the first field of another index.

For this index to be used you will have to supply the primary key field and the lastname.

The primary key field is the unique identifier so any field behind it is redundant.

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

There's a discussion on whether a nonclustered index should explicitly contain the clustering key in the discussion for part 3 - http://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx

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

Douglas Osborne-456728 SSCommitted Points: 1569 More actions · Answer 12

Gail,

Ok - I'm looking at our system.

It seems as though the people before me included the base PK column in the rest of their indexes.

So I'D see a PK of MyDataID INT PRIMARY KEY NONCLUSTERED

I'd have an indexes of

LastName + MyDataID CLUSTERED

SSN + MyDataID NONCLUSTERED

City + MyDataID NONCLUSTERED

so I would think the MyDataID is redundant in this case, and not a necessary column for the indexes.

Thoughts?

Doug

Douglas Osborne-456728 SSCommitted Points: 1569 More actions · Answer 13

Gail,

Ok - so those indexes which have the PK attached to them are fine if they end up being covering indexes?

Doug

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

Do you have queries that filter on the combination of SSN + MyDataID or City + MyDataID?

p.s. That's a poor choice for a clustered index. See part 2 of this indexing series.

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

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

Douglas Osborne-456728 (11/19/2009)
Ok - so those indexes which have the PK attached to them are fine if they end up being covering indexes?

It has nothing to do with the primary key. The question is, is it necessary to have the clustered index key specified in nonclustered indexes. The primary key can (and in your case is) a nonclustered index.

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