"Terence Leung" <TerenceLe***@discussions.microsoft.com> wrote in message
news:C30D63E9-EF56-4D6E-B683-55844C520EC8@microsoft.com...
> From what I've read, clustered index is particularly good for returning a set
> of data, expecially if the result set requires a sort by on those columns. My
> company is running education services. Let's say our enrollment table
> contains the fields year, session (term), course, student_id (fk to student
> table), and marks.
>
> We do a lot of queries based on year, session, and course. I'm planning to
> put year, session, and course on clustered index. The question is we
> definitely will query a particular enrollment for a student using student id.
> I read from many articles that non-clustered index always include the
> location of the clustered index. If so, does it mean an index on student_id
> (which is already there because of the FK) is the same as building one using
> year, session, course, and student_id which would cover many of our queries?

"Tibor Karaszi" wrote:

> > If so, does it mean an index on student_id
> > (which is already there because of the FK) is the same as building one using
> > year, session, course, and student_id which would cover many of our queries?
>
> Yes. By explicitly naming the cl ix columns, you can control the order of the columns in the index.
> Just be careful, having a wide cl ix makes you nc indexes wide.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "Terence Leung" <TerenceLe***@discussions.microsoft.com> wrote in message
> news:C30D63E9-EF56-4D6E-B683-55844C520EC8@microsoft.com...
> > From what I've read, clustered index is particularly good for returning a set
> > of data, expecially if the result set requires a sort by on those columns. My
> > company is running education services. Let's say our enrollment table
> > contains the fields year, session (term), course, student_id (fk to student
> > table), and marks.
> >
> > We do a lot of queries based on year, session, and course. I'm planning to
> > put year, session, and course on clustered index. The question is we
> > definitely will query a particular enrollment for a student using student id.
> > I read from many articles that non-clustered index always include the
> > location of the clustered index. If so, does it mean an index on student_id
> > (which is already there because of the FK) is the same as building one using
> > year, session, course, and student_id which would cover many of our queries?
>
>
>

"Terence Leung" <TerenceLe***@discussions.microsoft.com> wrote in message
news:8025E15E-8E25-4369-8033-40A586AD7205@microsoft.com...
> Thanks Tibor. I'm just curious if I do create an index on all four columns.
> Is SQL Server smart enough to see the first 3 are in the clustered index and
> not duplicate them in the nc index?
>
> Thanks also for the advice on the wide cl. We were debating whether it is
> better to have year + session (5 digits) or year + session + course code (8
> digits) as our cl ix. If we have time (yeah, like that will ever happens),
> we'll go through the exec plan of our heavy queries with different set up and
> see what happens.
>
> "Tibor Karaszi" wrote:
>
>> > If so, does it mean an index on student_id
>> > (which is already there because of the FK) is the same as building one using
>> > year, session, course, and student_id which would cover many of our queries?
>>
>> Yes. By explicitly naming the cl ix columns, you can control the order of the columns in the
>> index.
>> Just be careful, having a wide cl ix makes you nc indexes wide.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>>
>> "Terence Leung" <TerenceLe***@discussions.microsoft.com> wrote in message
>> news:C30D63E9-EF56-4D6E-B683-55844C520EC8@microsoft.com...
>> > From what I've read, clustered index is particularly good for returning a set
>> > of data, expecially if the result set requires a sort by on those columns. My
>> > company is running education services. Let's say our enrollment table
>> > contains the fields year, session (term), course, student_id (fk to student
>> > table), and marks.
>> >
>> > We do a lot of queries based on year, session, and course. I'm planning to
>> > put year, session, and course on clustered index. The question is we
>> > definitely will query a particular enrollment for a student using student id.
>> > I read from many articles that non-clustered index always include the
>> > location of the clustered index. If so, does it mean an index on student_id
>> > (which is already there because of the FK) is the same as building one using
>> > year, session, course, and student_id which would cover many of our queries?
>>
>>
>>