Clustered/Non-clustered indexes column selection

From what I've read, clustered index is particularly good for returning a se
t
of data, expecially if the result set requires a sort by on those columns. M
y
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?> If so, does it mean an index on student_id[vbcol=seagreen]
> (which is already there because of the FK) is the same as building one usi
ng
> year, session, course, and student_id which would cover many of our queries?[/vbco
l]
Yes. By explicitly naming the cl ix columns, you can control the order of th
e 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" <TerenceLeung@.discussions.microsoft.com> wrote in message
news:C30D63E9-EF56-4D6E-B683-55844C520EC8@.microsoft.com...[vbcol=seagreen]
> 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 studen
t
> 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_i
d
> (which is already there because of the FK) is the same as building one usi
ng
> year, session, course, and student_id which would cover many of our queries?[/vbco
l]|||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 an
d
see what happens.
"Tibor Karaszi" wrote:

> 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" <TerenceLeung@.discussions.microsoft.com> wrote in message
> news:C30D63E9-EF56-4D6E-B683-55844C520EC8@.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 a
nd
> not duplicate them in the nc index?
You're welcome. :-)
Yes, SQL Server is "smart" and will not duplicate the columns. Check sysinde
xes.keycnt, good source
of information for these things.
Heh, sometimes you just need to get on with your work. But an understanding
of index architecture
along with how SQL Server can use indexes (like covering a query with non-cl
ustered indexes) will
take you a long way. A big thing is to ask the right questions, and of cours
e later to verify your
thinking.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
news:8025E15E-8E25-4369-8033-40A586AD7205@.microsoft.com...[vbcol=seagreen]
> 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 a
nd
> 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:
>