Views VS Temp Tables...which to use...or is there another solution?

SQL Apprentice Question
I have to retreive data that is somewhat complicated. This data in the
past has required two views and a final ASP SQL Query. I am in the
process of updating everything to ASP.Net 2.0 (VB) and would love some
advice as to making this more efficient.

What I am doing is collecting all of the data from a table and grouping
it. I am then taking this grouped data and counting the number of
times a true bit comes up within each group. Finaly the asp code
breaks this data down for a specific person's ID. Here is the code
somewhat changed.


(View One)
TestsCompletedCount


SELECT ConsumerID, SessionCode, TypeOfTest, NumberOfQuestionsChosen,
StartDateTime, COUNT(*) AS NumberOfQuestionsAnswered, CategoryOfTest,
Deleted, LearningMethod, ExamGeneralMathOrDefinition, RandomOrExam,
ExamNumber, MaterialChosen
FROM QuestionsAnswered
WHERE (IsPreviouslyDoneTest <> 1)
GROUP BY ConsumerID, SessionCode, TypeOfTest, NumberOfQuestionsChosen,
StartDateTime, CategoryOfTest, Deleted, LearningMethod,
ExamGeneralMathOrDefinition, RandomOrExam, ExamNumber, MaterialChosen


(View Two)
TestsCompletedCountWithCountOfAnswersWrong


SELECT TestsCompletedCount.ConsumerID, TestsCompletedCount.SessionCode,
TestsCompletedCount.TypeOfTest,
TestsCompletedCount.NumberOfQuestionsChosen,
TestsCompletedCount.StartDateTime,
TestsCompletedCount.NumberOfQuestionsAnswered,
COUNT(QuestionsAnswered.Correct) AS NumberOfQuestionsAnsweredWrong,
TestsCompletedCount.CategoryOfTest, TestsCompletedCount.Deleted,
TestsCompletedCount.LearningMethod,
TestsCompletedCount.ExamGeneralMathOrDefinition,
TestsCompletedCount.RandomOrExam, TestsCompletedCount.ExamNumber,
QuestionsAnswered.MaterialChosen
FROM TestsCompletedCount INNER JOIN QuestionsAnswered ON
TestsCompletedCount.StartDateTime = QuestionsAnswered.StartDateTime
WHERE (QuestionsAnswered.Correct = 0)
GROUP BY TestsCompletedCount.ConsumerID,
TestsCompletedCount.SessionCode, TestsCompletedCount.TypeOfTest,
TestsCompletedCount.NumberOfQuestionsChosen,
TestsCompletedCount.StartDateTime,
TestsCompletedCount.NumberOfQuestionsAnswered,
TestsCompletedCount.CategoryOfTest, TestsCompletedCount.Deleted,
TestsCompletedCount.LearningMethod,
TestsCompletedCount.ExamGeneralMathOrDefinition,
TestsCompletedCount.RandomOrExam, TestsCompletedCount.ExamNumber,
QuestionsAnswered.MaterialChosen


And the current asp query goes like this


SELECT * FROM TestsCompletedCountWithCountOfAnswersWrong WHERE
(NumberOfQuestionsAnswered >= NumberOfQuestionsChosen) and ConsumerID
= " & ConsumerID & " AND (CategoryOfTest ='XXX') And (Deleted = 0) and
MaterialChosen = '" & MaterialChosen & "' ORDER BY StartDateTime


Any suggestions as to how to redo this would be greatly appreciated.
Would a temp table be a better choice? Can I use a stored procedure?


Celko Answers

>> Any suggestions as to how to redo this would be greatly appreciated. <<


Just from the column names and vague narrative description, it sounds
like you need an "exam header " table and a questions table for the
details of each questions from which a "question status" (ansered
right , wrong not answered, etc.) on each answer gives you all of the
data you need.

I hope you really do not use an assembly language style
"IsPreviouslyDoneTest" flag when you should have a test date, a
"NumberOfQuestionsAnswered" which should be computed from a COUNT( CASE
WHEN question_status = 100 THEN 1 ELSE 0 END) kind of thing, etc.


Nobody should write with BITs and those silly 1950's flags in SQL! And
you should ALWAYS avoid temp tables. VIEWs are standard SQL; the SQL
Server model of temp tables is proprietary, so you r code will not port
easily. Temp tables can be replced by derived tables or CTEs which cna
be used by the optimize s well as ported.


Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.