I try to initialize my object in OnInit, however, there is always an erroe saying
"[BC30469] Reference to a non-shared member requires an object reference."
My code is very simple and it is like this :
Dim MyObj as MyClass
Protected Overrides Sub OnInit()
MyObj = New MyClass
MyObj.Init(User!Language)
End Sub
The error part is when I want to pass in User!language to my method. It seems whenever I access
a global collection object, the system will give me that error. I've tried acess User,Parameters, Globals
all give me the same error when compile. I've even tried the following and it still give me error. so it
is not realted to my class.
Protected Overrides Sub OnInit()
Dim Test as object
Test = User!Language
End Sub
According to Book online, I should be able to access User, Parameter and Globals in Oninit.
Anyone has a clue on what is happening ?
Thanks
EdchyOne of the developers took a look at this and found that the documentation
is incomplete.
You need to access global collections from the Report object, like this:
Test = Report.User!Language
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Edchy Tsoi" <edchytsoi@.techland.com.hk> wrote in message
news:uALwmlhaEHA.3480@.TK2MSFTNGP11.phx.gbl...
> I try to initialize my object in OnInit, however, there is always an erroe
saying
> "[BC30469] Reference to a non-shared member requires an object reference."
> My code is very simple and it is like this :
> Dim MyObj as MyClass
> Protected Overrides Sub OnInit()
> MyObj = New MyClass
> MyObj.Init(User!Language)
> End Sub
> The error part is when I want to pass in User!language to my method. It
seems whenever I access
> a global collection object, the system will give me that error. I've tried
acess User,Parameters, Globals
> all give me the same error when compile. I've even tried the following
and it still give me error. so it
> is not realted to my class.
> Protected Overrides Sub OnInit()
> Dim Test as object
> Test = User!Language
> End Sub
> According to Book online, I should be able to access User, Parameter and
Globals in Oninit.
> Anyone has a clue on what is happening ?
> Thanks
> Edchy
>
Showing posts with label requires. Show all posts
Showing posts with label requires. Show all posts
Thursday, March 8, 2012
Thursday, February 16, 2012
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:
>
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:
>
Clustered/Non-clustered indexes column selection
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?
> 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" <TerenceLeung@.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?
|||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:
> 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 and
> not duplicate them in the nc index?
You're welcome. :-)
Yes, SQL Server is "smart" and will not duplicate the columns. Check sysindexes.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-clustered indexes) will
take you a long way. A big thing is to ask the right questions, and of course 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 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:
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?
> 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" <TerenceLeung@.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?
|||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:
> 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 and
> not duplicate them in the nc index?
You're welcome. :-)
Yes, SQL Server is "smart" and will not duplicate the columns. Check sysindexes.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-clustered indexes) will
take you a long way. A big thing is to ask the right questions, and of course 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 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:
Clustered/Non-clustered indexes column selection
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?> 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" <TerenceLeung@.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?|||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" <TerenceLeung@.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?
>
>|||> 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?
You're welcome. :-)
Yes, SQL Server is "smart" and will not duplicate the columns. Check sysindexes.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-clustered indexes) will
take you a long way. A big thing is to ask the right questions, and of course 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...
> 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" <TerenceLeung@.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?
>>
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?> 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" <TerenceLeung@.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?|||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" <TerenceLeung@.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?
>
>|||> 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?
You're welcome. :-)
Yes, SQL Server is "smart" and will not duplicate the columns. Check sysindexes.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-clustered indexes) will
take you a long way. A big thing is to ask the right questions, and of course 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...
> 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" <TerenceLeung@.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?
>>
Subscribe to:
Posts (Atom)