Indexes

I have cluster index created on a composite primary key (acct_key,period).
I would like to create a non-cluster index on the acct_key field, since
there are numerous sql statements that extract single value from this field.
Please let me know if cluster index has a composite primary key neither
field should be in a non-cluster index?A) If you already have a clustered index on (acct_key, period), there is no
reason to create a non-clustered index on acct_key.
B) Non-clustered indexes always contain the columns from the clustered
index.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
> I have cluster index created on a composite primary key (acct_key,period).
> I would like to create a non-cluster index on the acct_key field, since
> there are numerous sql statements that extract single value from this
> field.
> Please let me know if cluster index has a composite primary key neither
> field should be in a non-cluster index?
>|||> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
> non-clustered index on acct_key.
... unless you do it to cover queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23VCUMJ49FHA.3980@.TK2MSFTNGP14.phx.gbl...
> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
> non-clustered index on acct_key.
> B) Non-clustered indexes always contain the columns from the clustered index.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
>> I have cluster index created on a composite primary key (acct_key,period).
>> I would like to create a non-cluster index on the acct_key field, since
>> there are numerous sql statements that extract single value from this field.
>> Please let me know if cluster index has a composite primary key neither
>> field should be in a non-cluster index?
>>
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
>> A) If you already have a clustered index on (acct_key, period), there is
>> no reason to create a non-clustered index on acct_key.
> ... unless you do it to cover queries.
You'd have to have a pretty wide table for that to make a difference --
the clustered index already covers every possible query...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||* every possible query that uses acct_key, that is.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
>> A) If you already have a clustered index on (acct_key, period), there is
>> no reason to create a non-clustered index on acct_key.
>> ... unless you do it to cover queries.
> You'd have to have a pretty wide table for that to make a difference --
> the clustered index already covers every possible query...
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||Hmm, yes, assuming this is the only column to be in the nc index (which I now see was the case,
re-reading the OP). I was thrown off a bit by this:
>> I would like to create a non-cluster index on the acct_key field, since
>> there are numerous sql statements that extract single value from this field.
I have difficulties understanding what "extract single values from this field" means. My thinking
was that the NC index could cover queries where the restriction is for some other column than the
first column in the CL index (to enable nc ix scan instead of cl ix scan).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bXUAbC%23FHA.4004@.TK2MSFTNGP14.phx.gbl...
>* every possible query that uses acct_key, that is.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
>> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
>> non-clustered index on acct_key.
>> ... unless you do it to cover queries.
>> You'd have to have a pretty wide table for that to make a difference -- the clustered index
>> already covers every possible query...
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>