What is the most efficient way in T-SQL to compare answer strings to answer keys for scoring an exam

Question

These exams typically have about 120 questions. Currently, they strings are compared to the keys and a value of 1 or 0 assigned. When complete, total the 1's for a raw score.

Are there any T-SQL functions like intersect or diff or something all together different that would handle this process as quickly as possible for 100,000 examinees?

Thanks in advance for your expertise.

-Steven

Answer 1

Try selecting the equality of a question to its correct answer. I assume you have the student's tests in one table and the key in another; something like this ought to work:

select student_test.student_id, 
   student_test.test_id, 
   student_test.question_id, 
   (student_test.answer == test_key.answer OR (student_test.answer IS NULL AND test_key.answer IS NULL))
from student_test
INNER JOIN test_key
   ON student_test.test_id = test_key.test_id
      AND student_test.question_id = test_key.question_id
WHERE student_test.test_id = <the test to grade>

You can group the results by student and test, then sum the last column if you want the DB to give you the total score. This will give a detailed "right/wrong" analysis of the test.

EDIT: The answers being stored as a continuous string make it much harder. You will most likely have to implement this in a procedural fashion with a cursor, meaning each student's answers are loaded, SUBSTRINGed into varchar(1)s, and compared to the key in an RBAR (row by agonizing row) fashion. You could also implement a scalar-valued function that compared string A to string B one character at a time and returned the number of differences, then call that function from a driving query that will call this function for each student.