I have the following statement that takes quite a long time. Longest
of any of my SQL statment updates.

UPDATE F_REGISTRATION_STD_SESSION
SET PREVIOUS_YEAR_SESSION_ID = (
SELECT s.previous_year_session_id
FROM F_REGISTRATION_STD_SESSION R
,DS_V4_TARGET.dbo.D_H_Session_By_SessQtr S
WHERE
r.STUDENT_ID = F_REGISTRATION_STD_SESSION.STUDENT_ID
and s.previous_year_SESSION_ID = r.SESSION_ID
and s.session_id = F_REGISTRATION_STD_SESSION.SESSION_ID
)
STUDENT_ID varchar(20) and SESSION_ID char(10) and are indexed
What I want to accomplish:
I want to know if there was a student registration from the prior year
of a registration.
Example, if there is a registration for Fall 2004, was there also a
registration for the same student Fall 2003?
Maybe there is a better way to approach this?
TIA
Rob

R Camarda (rcamarda@cablespeed.com) writes:
> I have the following statement that takes quite a long time. Longest
> of any of my SQL statment updates.
>
> UPDATE F_REGISTRATION_STD_SESSION
> SET PREVIOUS_YEAR_SESSION_ID = (
> SELECT s.previous_year_session_id
> FROM F_REGISTRATION_STD_SESSION R
> ,DS_V4_TARGET.dbo.D_H_Session_By_SessQtr S
> WHERE
> r.STUDENT_ID = F_REGISTRATION_STD_SESSION.STUDENT_ID
> and s.previous_year_SESSION_ID = r.SESSION_ID
> and s.session_id = F_REGISTRATION_STD_SESSION.SESSION_ID
> )
> STUDENT_ID varchar(20) and SESSION_ID char(10) and are indexed
> What I want to accomplish:
> I want to know if there was a student registration from the prior year
> of a registration.
> Example, if there is a registration for Fall 2004, was there also a
> registration for the same student Fall 2003?
> Maybe there is a better way to approach this?

It's difficult to tell without knowledge of the tables. It would help a
lot if you posted:

o CREATE TABLE statements for the two tables.
o All constraints and indexes for the table (As SQL statemets).
o INSERT statements with sample data.
o The desired output for the sample.
o The number of rows in each table.

The sample does not help to be big enough to demonstrate the problem,
only so that it helps to test a rewrite of the query.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Hi

Posting your ddl (Create table statements etc..) and example data (as insert
statements) would help in the solution of this problem. I think your
statement can be simplyfied to:

UPDATE R
SET PREVIOUS_YEAR_SESSION_ID = s.previous_year_session_id
FROM F_REGISTRATION_STD_SESSION R
JOIN DS_V4_TARGET.dbo.D_H_Session_By_SessQtr S ON s.session_id =
R.SESSION_ID
JOIN F_REGISTRATION_STD_SESSION P ON s.previous_year_session_id =
P.session_id
AND R.STUDENT_ID = P.STUDENT_ID

Look at the query plan to see if your indexes are being used, but you have
not given any indication on what is indexed on D_H_Session_By_SessQtr.

If your session_ids do not change between years then you should not need
D_H_Session_By_SessQtr but you will need to hold the year in
F_REGISTRATION_STD_SESSION.

John



"R Camarda" <rcamarda@cablespeed.com> wrote in message
news:d7938492.0409031701.1d54f021@posting.google.c om...
> I have the following statement that takes quite a long time. Longest
> of any of my SQL statment updates.
>
> UPDATE F_REGISTRATION_STD_SESSION
> SET PREVIOUS_YEAR_SESSION_ID = (
> SELECT s.previous_year_session_id
> FROM F_REGISTRATION_STD_SESSION R
> ,DS_V4_TARGET.dbo.D_H_Session_By_SessQtr S
> WHERE
> r.STUDENT_ID = F_REGISTRATION_STD_SESSION.STUDENT_ID
> and s.previous_year_SESSION_ID = r.SESSION_ID
> and s.session_id = F_REGISTRATION_STD_SESSION.SESSION_ID
> )
> STUDENT_ID varchar(20) and SESSION_ID char(10) and are indexed
> What I want to accomplish:
> I want to know if there was a student registration from the prior year
> of a registration.
> Example, if there is a registration for Fall 2004, was there also a
> registration for the same student Fall 2003?
> Maybe there is a better way to approach this?
> TIA
> Rob

// Connection: 3-Staging

CREATE TABLE "dbo"."F_Registration_STD_Session"
(
"REGISTRATION_KEY" VARCHAR(41) NULL,
"STUDENT_ID" VARCHAR(20) NULL,
"SESSION_ID" VARCHAR(20) NULL,
"LOAD_DT" DATETIME NULL,
"CLASS_ADD_COUNT" INTEGER NULL,
"CLASS_DROP_COUNT" INTEGER NULL,
"BEFORE_D0_CLASS_COUNT" INTEGER NULL,
"DAY0_CLASS_COUNT" INTEGER NULL,
"AFTER_D0_CLASS_COUNT" INTEGER NULL,
"ALL_CLASS_COUNT" INTEGER NULL,
"BEFORE_D0_ONLINE_CLASS_COUNT" INTEGER NULL,
"DAY0_ONLINE_CLASS_COUNT" INTEGER NULL,
"AFTER_D0_ONLINE_CLASS_COUNT" INTEGER NULL,
"ALL_ONLINE_CLASS_COUNT" INTEGER NULL,
"CLASS_NOSHOW_COUNT" INTEGER NULL,
"CLASS_WITHDRAW_COUNT" INTEGER NULL,
"COMPLETE_WITHDRAW_COUNT" INTEGER NULL,
"COMPLETE_NOSHOW_COUNT" INTEGER NULL,
"STUDENT_WITHDRAW_COUNT" INTEGER NULL,
"BEFORE_D0_ONLINE_ONLY_CLASS_CNT" INTEGER NULL,
"DAY0_ONLINE_ONLY_CLASS_COUNT" INTEGER NULL,
"AFTER_D0_ONLINE_ONLY_CLASS_COUNT" INTEGER NULL,
"ALL_ONLINE_ONLY_CLASS_COUNT" INTEGER NULL,
"SESSION_SKEY" INTEGER NULL,
"NEXT_REGISTERED_SESSION_SKEY" INTEGER NULL,
"PREV_REGISTERED_SESSION_SKEY" INTEGER NULL,
"SESSIONS_SKIPPED_NEXT" INTEGER NULL,
"SESSIONS_SKIPPED_PREV" INTEGER NULL,
"CLASS_ADD_DT" DATETIME NULL,
"CLASS_DROP_DT" DATETIME NULL,
"STUDENT_SKEY" INTEGER NULL,
"PREVIOUS_YEAR_SESSION_ID" VARCHAR(10) NULL,
"NEXT_YEAR_SESSION_ID" VARCHAR(10) NULL,
"CAMPUS_SKEY" INTEGER NULL,
"ACTIVITY_DT" DATETIME NULL,
"CAMPUS_ID" VARCHAR(2) NULL
)

CREATE INDEX ix1F_Registration_STD_Session ON
"dbo"."F_Registration_STD_Session" ( "STUDENT_ID" );
CREATE INDEX ix2F_Registration_STD_Session ON
"dbo"."F_Registration_STD_Session" ( "SESSION_ID" );
CREATE INDEX ix3F_Registration_STD_Session ON
"dbo"."F_Registration_STD_Session" ( "NEXT_REGISTERED_SESSION_SKEY" );
CREATE INDEX ix4F_Registration_STD_Session ON
"dbo"."F_Registration_STD_Session" ( "PREV_REGISTERED_SESSION_SKEY" );
CREATE INDEX ix5F_Registration_STD_Session ON
"dbo"."F_Registration_STD_Session" ( "STUDENT_SKEY" );
CREATE INDEX ix6F_Registration_STD_Session ON
"dbo"."F_Registration_STD_Session" ( "PREVIOUS_YEAR_SESSION_ID" );
CREATE INDEX ix7F_Registration_STD_Session ON
"dbo"."F_Registration_STD_Session" ( "NEXT_YEAR_SESSION_ID" );
CREATE INDEX ix8F_Registration_STD_Session ON
"dbo"."F_Registration_STD_Session" ( "CAMPUS_SKEY" );
CREATE INDEX STUDENT_ID ON "dbo"."F_Registration_Session_Lookup" (
"STUDENT_ID" );
CREATE INDEX SESSION_ID ON "dbo"."F_Registration_Session_Lookup" (
"SESSION_ID" )

CREATE TABLE "dbo"."D_H_Session_By_SessQtr"
(
"SESSION_SKEY" INTEGER NULL,
"SESSION_CAPTION" VARCHAR(19) NULL,
"SESSION_ID" VARCHAR(20) NULL,
"SESSION_YEAR" VARCHAR(20) NULL,
"SESSION_START_DT" DATETIME NULL,
"SESSION_END_DT" DATETIME NULL,
"SESSION_AddDrop_DT" DATETIME NULL,
"SESSION_DROP_DT" DATETIME NULL,
"SESSION_PNOTE_DUE1_DT" DATETIME NULL,
"SESSION_PNOTE_DUE2_DT" DATETIME NULL,
"SESSION_PNOTE_DUE3_DT" DATETIME NULL,
"SESSION_QTR" VARCHAR(20) NULL,
"SESSION_QTR_CAPTION" VARCHAR(23) NULL,
"All_id" VARCHAR(3) NULL,
"All_caption" VARCHAR(21) NULL,
"SESSION_ID_CAPTION" VARCHAR(50) NULL,
"PREVIOUS_SESSION_ID" VARCHAR(10) NULL,
"NEXT_SESSION_ID" VARCHAR(10) NULL,
"PREVIOUS_SESSION_SKEY" NUMERIC(19) NULL,
"NEXT_SESSION_SKEY" NUMERIC(19) NULL,
"PREVIOUS_YEAR_SESSION_ID" VARCHAR(10) NULL,
"NEXT_YEAR_SESSION_ID" VARCHAR(10) NULL,
"PREVIOUS_YEAR_SESSION_SKEY" NUMERIC(19) NULL,
"NEXT_YEAR_SESSION_SKEY" NUMERIC(19) NULL
)
;
CREATE INDEX ix1D_H_Session_By_SessQtr ON
"dbo"."D_H_Session_By_SessQtr" ( "SESSION_SKEY" );
CREATE INDEX ix2D_H_Session_By_SessQtr ON
"dbo"."D_H_Session_By_SessQtr" ( "SESSION_QTR" );
CREATE INDEX ix3D_H_Session_By_SessQtr ON
"dbo"."D_H_Session_By_SessQtr" ( "All_id" );
CREATE INDEX PREVIOUS_YEAR_SESSION_ID ON
"dbo"."D_H_Session_By_SessQtr" ( "PREVIOUS_YEAR_SESSION_ID" );
CREATE INDEX NEXT_YEAR_SESSION_ID ON "dbo"."D_H_Session_By_SessQtr" (
"NEXT_YEAR_SESSION_ID" )

[EXECUTEDSQ - 07:23:54] data insert

insert into "dbo"."F_Registration_STD_Session" ("REGISTRATION_KEY",
"STUDENT_ID", "SESSION_ID", "LOAD_DT", "CLASS_ADD_COUNT",
"CLASS_DROP_COUNT", "BEFORE_D0_CLASS_COUNT", "DAY0_CLASS_COUNT",
"AFTER_D0_CLASS_COUNT", "ALL_CLASS_COUNT",
"BEFORE_D0_ONLINE_CLASS_COUNT", "DAY0_ONLINE_CLASS_COUNT",
"AFTER_D0_ONLINE_CLASS_COUNT", "ALL_ONLINE_CLASS_COUNT",
"CLASS_NOSHOW_COUNT", "CLASS_WITHDRAW_COUNT",
"COMPLETE_WITHDRAW_COUNT", "COMPLETE_NOSHOW_COUNT",
"STUDENT_WITHDRAW_COUNT", "BEFORE_D0_ONLINE_ONLY_CLASS_CNT",
"DAY0_ONLINE_ONLY_CLASS_COUNT", "AFTER_D0_ONLINE_ONLY_CLASS_COUNT",
"ALL_ONLINE_ONLY_CLASS_COUNT", "SESSION_SKEY",
"NEXT_REGISTERED_SESSION_SKEY", "PREV_REGISTERED_SESSION_SKEY",
"SESSIONS_SKIPPED_NEXT", "SESSIONS_SKIPPED_PREV", "CLASS_ADD_DT",
"CLASS_DROP_DT", "STUDENT_SKEY", "PREVIOUS_YEAR_SESSION_ID",
"NEXT_YEAR_SESSION_ID", "CAMPUS_SKEY", "ACTIVITY_DT", "CAMPUS_ID")
values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

[EXECUTEDSQ - 07:26:20] checking existing Dimension table rows

select "SESSION_SKEY", "SESSION_CAPTION", "SESSION_ID",
"SESSION_YEAR", "SESSION_START_DT", "SESSION_END_DT",
"SESSION_AddDrop_DT", "SESSION_DROP_DT", "SESSION_PNOTE_DUE1_DT",
"SESSION_PNOTE_DUE2_DT", "SESSION_PNOTE_DUE3_DT", "SESSION_QTR",
"SESSION_QTR_CAPTION", "All_id", "All_caption", "SESSION_ID_CAPTION",
"PREVIOUS_SESSION_ID", "NEXT_SESSION_ID", "PREVIOUS_SESSION_SKEY",
"NEXT_SESSION_SKEY", "PREVIOUS_YEAR_SESSION_ID",
"NEXT_YEAR_SESSION_ID", "PREVIOUS_YEAR_SESSION_SKEY",
"NEXT_YEAR_SESSION_SKEY" from "dbo"."D_H_Session_By_SessQtr"

R Camarda (rcamarda@cablespeed.com) writes:
> // Connection: 3-Staging
>
> CREATE TABLE "dbo"."F_Registration_STD_Session"

Thanks for the tables and indexes. Unfortunately the INSERT statment
had only placeholders, so it was not useful. I would have need a couple
(2-3) rows in each table. And of course the desired result.

One thing which is missing are the PRIMARY KEY constraints. Don't you
have any? Given the index you posted, I would not expect anyone of them
to be used, since they are all non-clustered. I would expect the optimizer
to scan all three tables and then perform a hash-join.

If you add a composite, non-clustered, index on (previous_year_SESSION_ID,
SESSION_ID) on D_H_Session_By_SessQtr, that index would be a covering index
for the query. In the same vein a non-clustered index (SESSION_ID,
STUDENT_ID) for F_REGISTRATION_STD_SESSION could also help. Note here that I
have listed the potential index columns in arbitrary order. It may be better
to flip them; you will have to test.

Did you try the rewrite that John Bell suggested? (That too would benefit
from the covering indexes.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

John, I tried your example; it worked, kind of.
student_id session_id previous_session_id
000004561 200403 000000
000004561 200404 000000
000005131 200002 000000
000005131 200003 000000
000005131 200004 000000
000005131 200102 200002
000005131 200103 200003
000005131 200104 200004
000005131 200202 200102
000005131 200203 200103
000005131 200204 200104
000005131 200302 200202
000005131 200303 200203
000005131 200304 200204
000005131 200402 200302
000005131 200403 200303
000005131 200404 200304
000005131 200502 200402

I have updates that I will post..
Thanks

"John Bell" <jbellnewsposts@hotmail.com> wrote in message news:<413a3a1b$0$21652$afc38c87@news.easynet.co.uk >...
> Hi
>
> Posting your ddl (Create table statements etc..) and example data (as insert
> statements) would help in the solution of this problem. I think your
> statement can be simplyfied to:
>
> UPDATE R
> SET PREVIOUS_YEAR_SESSION_ID = s.previous_year_session_id
> FROM F_REGISTRATION_STD_SESSION R
> JOIN DS_V4_TARGET.dbo.D_H_Session_By_SessQtr S ON s.session_id =
> R.SESSION_ID
> JOIN F_REGISTRATION_STD_SESSION P ON s.previous_year_session_id =
> P.session_id
> AND R.STUDENT_ID = P.STUDENT_ID
>
> Look at the query plan to see if your indexes are being used, but you have
> not given any indication on what is indexed on D_H_Session_By_SessQtr.
>
> If your session_ids do not change between years then you should not need
> D_H_Session_By_SessQtr but you will need to hold the year in
> F_REGISTRATION_STD_SESSION.
>
> John
>
>
>

The orignal message took awhile to post, so I did more work in the
mean time.
For testing, I added
WHILE SESSION_ID = '200502'

to restrict the amount of rows it had to process. I turned on
EXECUTION PLAN in Query Analyzer. I recall it spent a ton of time in
the subquery, so I decided to create a "lookup" tables that contained
just STUDENT_ID and SESSION_ID.
This is the query I got to work:

UPDATE F_REGISTRATION_STD_SESSION
SET PREVIOUS_YEAR_SESSION_ID = (
SELECT s.previous_year_session_id
FROM F_Registration_Session_Lookup R, D_Session S
WHERE
r.STUDENT_ID = F_REGISTRATION_STD_SESSION.STUDENT_ID
and s.previous_year_SESSION_ID = r.SESSION_ID
and s.session_id = F_REGISTRATION_STD_SESSION.SESSION_ID
)
;
I also made the necessary changed to the D_SESSION table by adding
PREVIOUS_SESSION_ID. At one point, I thought using two different
tables might be impacting me. Once I made the table change and ran my
original query, it did not change the time, so I moved ahead with the
lookup table.

D_SESSION and dbo.D_H_Session_By_SessQtr are nearly the same.
D_SESSION is just session information and dbo.D_H_Session_By_SessQtr
adds hierarchy of Session quarter's.


rcamarda@cablespeed.com (R Camarda) wrote in message
news:<d7938492.0409031701.1d54f021@posting.google. com>...
> I have the following statement that takes quite a long time. Longest
> of any of my SQL statment updates.
>
> UPDATE F_REGISTRATION_STD_SESSION
> SET PREVIOUS_YEAR_SESSION_ID = (
> SELECT s.previous_year_session_id
> FROM F_REGISTRATION_STD_SESSION R
> ,DS_V4_TARGET.dbo.D_H_Session_By_SessQtr S
> WHERE
> r.STUDENT_ID = F_REGISTRATION_STD_SESSION.STUDENT_ID
> and s.previous_year_SESSION_ID = r.SESSION_ID
> and s.session_id = F_REGISTRATION_STD_SESSION.SESSION_ID
> )
> STUDENT_ID varchar(20) and SESSION_ID char(10) and are indexed
> What I want to accomplish:
> I want to know if there was a student registration from the prior year
> of a registration.
> Example, if there is a registration for Fall 2004, was there also a
> registration for the same student Fall 2003?
> Maybe there is a better way to approach this?
> TIA
> Rob

Erland,
I am usings a Data warehouse tool from Cognos called DecisionStream. I
only delve into SQL when I need to do something that cant be done
inside the tool.
I checked SQL Server Enterprise Manager to look for the primary keys,
and I did not find any. (I looked in DESIGN TABLE and did not find the
Key icon on my primary key). The primary key for D_SESSION is
SESSION_SKEY. This is used instead of SESSION_ID. The table,
F_Registration_STD_Session key's are STUDENT_ID (or STUDENT_SKEY) and
SESSION_ID ( or SESSION_SKEY), I'm not sure how a composite key or
index would work.
My basic approach is to make sure anything I'm join by, has an index.
I've not used or set primary keys nor used composite keys outside of
DecisionStream. (Notice there is REGISTRATION_KEY which is
STUDENT_ID*SESSION_ID. I use this inside the tool)
I feel that I have an opportunity to expand my knowledge of SQL based
on this thread that will help me in the future.
Comments welcome.
Thanks
Rob
Erland Sommarskog <esquel@sommarskog.se> wrote in message news:<Xns955BF41E917BAYazorman@127.0.0.1>...
> R Camarda (rcamarda@cablespeed.com) writes:
> > // Connection: 3-Staging
> >
> > CREATE TABLE "dbo"."F_Registration_STD_Session"
>
> Thanks for the tables and indexes. Unfortunately the INSERT statment
> had only placeholders, so it was not useful. I would have need a couple
> (2-3) rows in each table. And of course the desired result.
>
> One thing which is missing are the PRIMARY KEY constraints. Don't you
> have any? Given the index you posted, I would not expect anyone of them
> to be used, since they are all non-clustered. I would expect the optimizer
> to scan all three tables and then perform a hash-join.
>
> If you add a composite, non-clustered, index on (previous_year_SESSION_ID,
> SESSION_ID) on D_H_Session_By_SessQtr, that index would be a covering index
> for the query. In the same vein a non-clustered index (SESSION_ID,
> STUDENT_ID) for F_REGISTRATION_STD_SESSION could also help. Note here that I
> have listed the potential index columns in arbitrary order. It may be better
> to flip them; you will have to test.
>
> Did you try the rewrite that John Bell suggested? (That too would benefit
> from the covering indexes.)

There are many columns for D_SESSION, so I'll just post the fields I'm
using

Data for D_SESSION
session_skey session_id previous_year_session_id
------------ -------------------- ------------------------
291 200002 199902
292 200003 199903
293 200004 199904
294 200005 199905
295 200102 200002
296 200103 200003
297 200104 200004
298 200105 200005
299 200202 200102
300 200203 200103

session_skey is int
session_id is varchar(10)
previous_year_session_id is varchar(10)
Indexes on session_skey, session_id and previous_year_session_id

Here is a complete single row:
SESSION_SKEY SESSION_ID SESSION_CAPTION SESSION_YEAR
SESSION_QTR SESSION_START_DT
SESSION_END_DT
SESSION_AddDrop_DT SESSION_DROP_DT
SESSION_PNOTE_DUE1_DT
SESSION_PNOTE_DUE2_DT
SESSION_PNOTE_DUE3_DT eff_date
end_date
cre_date
udt_date
curr_ind SESSION_ID_CAPTION
PREVIOUS_YEAR_SESSION_ID NEXT_YEAR_SESSION_ID
------------ -------------------- -------------------
-------------------- --------------------
------------------------------------------------------
------------------------------------------------------
------------------------------------------------------
------------------------------------------------------
------------------------------------------------------
------------------------------------------------------
------------------------------------------------------
------------------------------------------------------
------------------------------------------------------
------------------------------------------------------
------------------------------------------------------ --------
--------------------------------------------------
------------------------ --------------------
291 200002 Fall Quarter 1999 2000
02 1999-09-27 00:00:00.000
1999-12-19 00:00:00.000
1999-10-04 00:00:00.000 1999-11-12
00:00:00.000 1999-09-13 00:00:00.000
1999-10-13 00:00:00.000
1999-11-12 00:00:00.000
2004-07-20 00:00:00.000 NULL
2004-07-20 11:45:39.000
2004-09-03 22:10:33.000
Y 200002 Fall Quarter 1999
199902 200102

Everthing is either DATETIME, varchar(). SESSION_SKEY is INT

Sample of f_registration_std_session
registration_key student_id student_skey session_id session_skey
previous_year_session_id
---------------- ---------- ------------ ---------- ------------
------------------------
999970104*200202 999970104 408515 200202 299
200102
999970104*200105 999970104 408515 200105 298
200005
999970104*200104 999970104 408515 200104 297
200004
999970104*200103 999970104 408515 200103 296
200003
999970104*200102 999970104 408515 200102 295
200002
999970104*200005 999970104 408515 200005 294 NULL
999970104*200004 999970104 408515 200004 293 NULL
999970104*200003 999970104 408514 200003 292 NULL
999970104*200002 999970104 408514 200002 291 NULL

only session_skey is int, everything else is char.

R Camarda (rcamarda@cablespeed.com) writes:
> I checked SQL Server Enterprise Manager to look for the primary keys,
> and I did not find any. (I looked in DESIGN TABLE and did not find the
> Key icon on my primary key). The primary key for D_SESSION is
> SESSION_SKEY. This is used instead of SESSION_ID. The table,
> F_Registration_STD_Session key's are STUDENT_ID (or STUDENT_SKEY) and
> SESSION_ID ( or SESSION_SKEY), I'm not sure how a composite key or
> index would work.

CREATE INDEX composite_index ON tbl(SESSION_ID, STUDENT_ID)

> My basic approach is to make sure anything I'm join by, has an index.

But that is not a very constructive strategy. Just because there is an
index, does not mean that it is useful. For simplicity consider
the question

SELECT * FROM tbl WHERE col = 10

There is a non-clustered index on col, and about 10% of the rows has
the value 10. Is tnis index useful for the query? Probably not. For
every hit in the index, SQL Server has to go the data page and get
the data. Since there can be many rows on the a data page, it may mean
that SQL Server reads the same row more than once. So it concludes it's
better to scane the table instead.

With a clustered index, the story is completely different. Here the
leaf level of the index is the data, so using the index is very
effecient.

Now consider the query:

SELECT SUM(clust) FROM tbl WHERE col = 10

Again, there is a non-clustered index on col and clustered index on clust.
In this case, SQL Server will use the non-clustered index. This is because
in a non-clustered index, the clustered index serves as row locator. So
clust is in fact in that index, and there is no need to access the data
pages. This is a covered query.

Your tables does not have any clustered indexes, and neither any primary
keys, so they are definitely difficult to deal with. (With a clustered
index, an internal rowid serves as row locator.)

What is the best index depends from situation to situation. It is not
always an idea to add a covering index (for instance because table
columns are being accessed anyway). In this case, it seems very useful,
becaue the tables has many columns, but the query only access a few.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp