Covering Index for Primary Key

  • Thread starter Thread starter Bob Day
  • Start date Start date
B

Bob Day

Using VS.2003, VB.net, MSDE...

Using Server Explorer, Design Table, to create a simply table with a
primary key and 1 column as below:
"Primary Key" as integer, autonumbered
"Primary_Key_String" as string

I want Primary_Key_String to be a string representation of Primary_Key. The
Primary_Key_String will be an index. The reason I want to use it as an
index is so it can be part of a covering index (a series of other string
columns used as indexes), so I can throw "%" in it when I don't care about
it.

Since Primary_Key is an autonumber column, I have put in the formula field
for Primay_Key_String "str(Primary_Key)". This works as expected, putting
the autonumber generated during an UPDATE in both Primary_Key and
Primary_Key_String. The problem is, when you try to create an index on
Primary_Key_String (using Server Explorer Design Table), since it is a
calculated field via a formula, it will not let you, thus making it
undersirable as an index.

Is there a way to create a string index of the autonumber Primary_Key? Is
there another approach?

Thanks!
Bob
 
Bob,

I'm probably missing something, but I'm a bit confused here as to why you
are converting the autonumber(identity) column to a string - in MSDE (and
Sql Server) there is no requirement for all the datatypes in an index to be
the same, so making the covering index up from the primary key int column
along with the rest of the string columns would seem to be the way to go...
If you want to miss that column out of the query occasionally then that's
not a problem - you can just omit the WHERE clause for that column when you
want to do that. Like I said, I'm probably missing something here...

Steve
 
And another thing [sorry I didn't mention this before but it's been a long
day :-)] -

If the primary key is clustered (it normally should be) then there's no
benefit to including it in a covering index - just add the other string
columns to a separate index - the performance will be about the same as the
rows are already physically ordered according to the clustered index.

Steve
 
Thanks for your help.

Bob

Steve Willcock said:
And another thing [sorry I didn't mention this before but it's been a long
day :-)] -

If the primary key is clustered (it normally should be) then there's no
benefit to including it in a covering index - just add the other string
columns to a separate index - the performance will be about the same as the
rows are already physically ordered according to the clustered index.

Steve

Bob,

I'm probably missing something, but I'm a bit confused here as to why you
are converting the autonumber(identity) column to a string - in MSDE (and
Sql Server) there is no requirement for all the datatypes in an index to be
the same, so making the covering index up from the primary key int column
along with the rest of the string columns would seem to be the way to go...
If you want to miss that column out of the query occasionally then that's
not a problem - you can just omit the WHERE clause for that column when you
want to do that. Like I said, I'm probably missing something here...

Steve

Primary_Key.
The
Click to expand...
Click to expand...
 
Back
Top