nonclustered index fields

Hi folks,
I was wondering if it should be taken as a rule of thumb to allways avoid
the us of the fields that conform the primary key when we are designing any
nonclustered index, as in reality they allready have this key in "their
inside".
For example, say we have a pk composed of the fields: date, id
and then we create an index1 with fields : field1, date
and yet another index2 with just field: field1
Then, whenever I search in this simplistic example for field 1 between a
range of dates I allways get better performance with just the simple index.
The question is, will it happen allways or will it depend on the particular
queries and or situations?
Thanks in advance,
Tristan."Tristan" <Tristan@.discussions.microsoft.com> wrote in message
news:68D78CF8-E360-4365-9AE6-7CEA2D1C6C68@.microsoft.com...
> Hi folks,
> I was wondering if it should be taken as a rule of thumb to allways avoid
> the us of the fields that conform the primary key when we are designing
> any
> nonclustered index, as in reality they allready have this key in "their
> inside".
> For example, say we have a pk composed of the fields: date, id
> and then we create an index1 with fields : field1, date
> and yet another index2 with just field: field1
> Then, whenever I search in this simplistic example for field 1 between a
> range of dates I allways get better performance with just the simple
> index.
> The question is, will it happen allways or will it depend on the
> particular
> queries and or situations?
>
It depends. Often, for instance, you will have a non-clustered index on the
trailing column of a two-column clustered primary key. in your example a
non-clustered index on id would probably be appropriate to enable lookups by
ID. Even though the ID is replicated in the index leaf data, since it is
not the leading column in the clustered primary key, the clustered primary
key does not provide an efficient access path for lookups or sorting by ID.
David|||Tristan
You are confusing PK with clustered index. They are not the same thing.
Clustered index keys are contained in every NC index, but the clustered
index might not be on the primary key column(s).
However, if we assume you meant clustered index when you said pk, then you
answered your own questions. It completely depends on your queries and your
data distributions. A very general rule of thumb is that if you do a lot of
modifications, you want to keep your indexes to a minimum, so you wouldn't
have indexes with overlapping keys. But if you do mainly SELECTs, more
indexes can be useful, and having different leading columns can be a BIG
help. However, in your specific example, your index1 and index2 will be
identical in every way, so there is little reason to have both of them.
I wrote an article for SQL Server Magazine about a year ago on the reasons
why you might want to explicitly list one of your clustered index columns in
your nc index definitions.
http://www.sqlmag.com/Article/Artic...rver_44807.html
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tristan" <Tristan@.discussions.microsoft.com> wrote in message
news:68D78CF8-E360-4365-9AE6-7CEA2D1C6C68@.microsoft.com...
> Hi folks,
> I was wondering if it should be taken as a rule of thumb to allways avoid
> the us of the fields that conform the primary key when we are designing
> any
> nonclustered index, as in reality they allready have this key in "their
> inside".
> For example, say we have a pk composed of the fields: date, id
> and then we create an index1 with fields : field1, date
> and yet another index2 with just field: field1
> Then, whenever I search in this simplistic example for field 1 between a
> range of dates I allways get better performance with just the simple
> index.
> The question is, will it happen allways or will it depend on the
> particular
> queries and or situations?
> Thanks in advance,
> Tristan.|||Thanks lot Kalen & David for your replies. Indeed I pretendet to say
clustered when I used pk, thanks por pointing that out. Your answers have
being most clarifiying, thanks again,
Tristan.
"Kalen Delaney" wrote:

> Tristan
> You are confusing PK with clustered index. They are not the same thing.
> Clustered index keys are contained in every NC index, but the clustered
> index might not be on the primary key column(s).
> However, if we assume you meant clustered index when you said pk, then you
> answered your own questions. It completely depends on your queries and you
r
> data distributions. A very general rule of thumb is that if you do a lot o
f
> modifications, you want to keep your indexes to a minimum, so you wouldn't
> have indexes with overlapping keys. But if you do mainly SELECTs, more
> indexes can be useful, and having different leading columns can be a BIG
> help. However, in your specific example, your index1 and index2 will be
> identical in every way, so there is little reason to have both of them.
> I wrote an article for SQL Server Magazine about a year ago on the reasons
> why you might want to explicitly list one of your clustered index columns
in
> your nc index definitions.
> http://www.sqlmag.com/Article/Artic...rver_44807.html
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tristan" <Tristan@.discussions.microsoft.com> wrote in message
> news:68D78CF8-E360-4365-9AE6-7CEA2D1C6C68@.microsoft.com...
>
>