SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Covered index on primary key?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aiken
Aged Yak Warrior

USA
525 Posts

Posted - 04/29/2002 :  21:04:41  Show Profile  Send aiken an ICQ Message  Reply with Quote
Say I've got a table

Users
-----
UserID (int, identity, Primary key, Clustered)
IsHappy (tinyint)
x
y
z



...for any given user, IsHappy will be either 0 or 1 (yes, I know about bits but there's a good reason for this).

Now, my primary key is userID, of course. But I've got a slow-running query that does something like "select UserID,x,y,z from users where isHappy=1".

Right now, in addition to that clustered primary key, I have a nonclustered index on IsHappy, which may be a waste, as there are only two possible values. Out of about 300,000 rows, maybe 5,000 have IsHappy set to 1, the rest are 0. (Yeah, not a cheery place).

Can/should I make my primary key a covering index on UserID and IsHappy even though the only real unique value is UserID? I see that it would increase the size of my clustered index references from 4 bytes to 7, which would affect about 20 foreign key relationships to users on UserID.

Thanks for any insight
-b

aiken
Aged Yak Warrior

USA
525 Posts

Posted - 04/30/2002 :  20:36:05  Show Profile  Send aiken an ICQ Message  Reply with Quote
Nobody has any ideas on this? I've found a few more situations like this in my DB, and would love to hear if anyone has experimented or knows offhand why this is a good/bad idea.

Thanks
-b

Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 04/30/2002 :  20:45:50  Show Profile  Reply with Quote
quote:

Can/should I make my primary key a covering index on UserID and IsHappy even though the only real unique value is UserID? I see that it would increase the size of my clustered index references from 4 bytes to 7, which would affect about 20 foreign key relationships to users on UserID.



No, you should not..

What is the problem anyway? Is the query taking too long, table scans etc...

DavidM

Tomorrow is the same day as Today was the day before.
Go to Top of Page

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 04/30/2002 :  21:41:40  Show Profile  Reply with Quote
Like my good friend David above, I'd like to hear what your definition of "slow running query" is. I have almost identical scenarios which perform very respectably...

more info pls

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

aiken
Aged Yak Warrior

USA
525 Posts

Posted - 05/01/2002 :  13:43:48  Show Profile  Send aiken an ICQ Message  Reply with Quote
My issue is that the table in question sees a *lot* of inserts, so I'm trying to minimize the number of indexes on it. I was hoping to get away with using my primary key as a covering index rather than creating a nonclustered index on a column that only has two values, which BOL says does limited good and is a bad practice in general.

Cheers
-b

Go to Top of Page

royv
Constraint Violating Yak Guru

455 Posts

Posted - 05/01/2002 :  13:57:23  Show Profile  Reply with Quote
Since your table is subject to a lot of inserts, get rid of the non-clustered index and make the clustered primary key a covering index. For a table subject to many inserts, it is good to have a clustered index.

*************************
Just trying to get things done
Go to Top of Page

izaltsman
A custom title

USA
1139 Posts

Posted - 05/01/2002 :  14:13:31  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
I agree with royv on the fact that nonclustered index should be dropped -- it simply isn't selective enough, so it will never be used.

However, I don't think that adding IsHappy to the primary key would do any good if you typically run queries with only isHappy as a WHERE criteria. Here is my reasoning:
If you add isHappy as the last key in the clustered index, the WHERE clause would still have to scan the entire index for your record (kinda like searching a phonebook for some first name)...
If you add isHappy as the first key to the clustered index, it would probably speed up this specific query... But due to the low selectivity of this field, I suspect the foreign key lookups may slow down (and you mentioned there are 20 foreign keys referencing this table!)

So to summarize: loose the non-clustered index and don't do anything else.

---------------
Strong SQL Developer wanted in the Boston area. Please e-mail if interested.


Edited by - izaltsman on 05/01/2002 14:17:26
Go to Top of Page

Webern
Starting Member

1 Posts

Posted - 05/30/2013 :  18:00:00  Show Profile  Reply with Quote
I recommend

CREATE INDEX IX_YourIndexNamingConvention
ON thistbl
(
isHappy
,UserID
)
INCLUDE
(
x
,y
,z
,...
)
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/31/2013 :  00:31:54  Show Profile  Visit russell's Homepage  Reply with Quote
Wow! Possibly a record here!

Necro'd an 11 year old thread

Nice work. Welcome to the forums Webern. Might want to try reading some questions that were posted this decade
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000