I am using sql server 2000 to create a test database. I have two tables, a table for questions and one for answers. I want to create random orders for the test questions and answers. I tried to use the rand function but that doesn’t seem to work, because when I order by the random number, the questions are still in the primary key order. My primary key is 'questioned'. Can someone help me out on how to randomly order my questions and ansers?
Select questionID ,Rand(Questionid)from tblTestQuestions
order by rand(questionID)
The problem is that the SQL optimizer can see that the Rand() function does not depend on any data from the individual rows, so the function is only called once per SELECT statement. You can see this, along with a way to get the desired results in the following code snippet.
CREATE TABLE #questions (
questionId INT IDENTITY
, question NVARCHAR(200) NOT NULL
INSERT INTO #questions (
question) SELECT 'This is question one'
UNION ALL SELECT 'C''est la question deux'
UNION ALL SELECT 'Dies ist Frage drei'
UNION ALL SELECT 'Esta es la pregunta quattro'
SELECT questionId, question, Rand()
ORDER BY 3
SELECT questionId, question
ORDER BY NewId()
DROP TABLE #questions
While this behavior seems weird, it actually improves query performance in almost all cases... You just happened to hit one case where the optimization fails.
Thanks for the help Pat
I will try using a temporary table.
One other question if you don't mind. the way I have decided to go will require me to
drop the temporary table with a drop table statement if the table already exists. I realize
the table will be dropped at the end of the session, but during testing I will be recreating the temporary table but the
temporary table is not listed in the sysobjects table. How do I check for the existence of my temporary table so I can delete it if I need to?
this code doesn't work for a temporary table
if exists (select * from dbo.sysobjects where id = object_id(N'[#questions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [#questions]
The only reason that my demonstration uses a temporary table is to give me a simple place to just stuff the sample data. The technique works just as well with permanent tables, there is no real reason to create a temporary table if the data is already stored in a user table like yours is.
The only reason I was thinking of using a temp table was so that multiple users could take the same test. One field would be added to the answers temporary table to indicate what answer was selected. The test site would be on our company’s intranet site. From what I understand, each user would more or less have their own temp table for their session. When their session ended the temporary table would no longer exist. The questions and answers would all be stored in permanent tables, but when users want to take a test, the randomized questions and answers would be copied to a temporary table and the test would be based on the temp tables. I thought this would be a good idea to prevent multiple users from stepping on each other.
It sounds like you might think this is not the best idea. If so, could you recommend a better way?
Without knowing a lot more about your application (what you are testing, how you are testing, reporting requirements, etc) I can't give you a definite answer.
With that said, I'd recommend a permanent table to keep the question and answer sequence, along with both the respondant's answer (and just as importantly the lack thereof) and the correct answer. Many people only see a test as providing information about the respondant, but there is information about the whole process if you know how to look for it! While you wouldn't want to keep this information forever, there is a goldmine of data to help you understand a lot of things about your training and testing there, and I hate to leave a goldmine laying untouched!
One thing that is critically important to a test administrator is to know when a significant number of respondants fail to answer a question. This tells them one of two things: either the question is somehow faulty or the associated training was somehow faulty. The question can be faulty in many ways, it can be an outright error (it doesn't belong on this test at all), it can be unclear (the respondant couldn't make enough sense of the question to be able to answer it), it can be unanswerable (an ethical violation might do this), etc. The associated trainging might also have been faulty, so for instance a particular subject might have been skipped in the presentation (this happens frequently with classes less than a week long when there is an inexperienced presenter). In any case, if a question is not answered by a significant number of respondants, then the test administrator needs to find out why!
Another thing that needs to be analyzed is if signifcant numbers of respondants fail to answer significant numbers of questions. There will always be cases where an occaisional respondant will only answer a few (or no) questions, but if this is a pattern then there is a problem. Either there is not enough time being allowed for the test, the training was inadequate to allow some students to complete the test. or something else went wrong. If these numbers are significant, this also requires an administrator to understand what the problem is and to try to fix it.
This information should never be evaluated on a person-by-person basis. You need several or many people to provide useful data of this type. This data should never be used to fuel a "witch hunt" looking for question writers or teachers with problems, but it ought to be used to manage the quality of the whole process. When you find one instructor who consistantly has huge score problems, an administrator needs to understand why that is happening. It may not have anything to do with the teacher, it might be because one teacher (often a junior one) gets assigned the least desirable students, or any combination of a thousand other issues. That doesn't matter, an administrator still needs to get involved and understand why the pattern keeps occuring!
Thanks for the response. I’m not at work so I don’t have any table scripts or code but I wanted to respond anyway. You spent a lot of time on your post and I wanted to respond as quickly as possible. I know you probably answer a lot of questions and hate it when you answer a question and you never hear from the person again that you helped – that’s the last thing I want to do to you. Below is a description of what I’m trying to do. If you need table scripts or code, let me know and I will send them when I get back to work.
I don’t have any requirements other than my boss saying, “It would really be nice if we had a way of tracking employee training and qualifications – maybe some kind of testing system.”
I work for a small company with 84 employees that is rapidly expanding and I am the DBA/programmer/web developer. We make UAV’s and have just been awarded a very large contract. We are in the process of training our employees in subjects such as human resources , “how to use MS Offices applications”, how to use PRO III (our mainframe application we are stuck with from corporate), safety, as well as various other subjects.
These are some of the requirements I’ve come up myself:
Develop tables for questions and answers.
The questions table will have the following fields: questioned, QuestionText, subject,directions, and reference. The directions field will contain text such as “Select the correct answer” or “Select the best two correct answers” . The reference field will contain information on where the question came from like “Company policy 1234”.
The answers table will have the following fields: QuestionID, AnswerID, AnswerOrder, AnswerText , and IsCorrect. The isCorrect field will be a Boolean value to indicate whether or not the answer is the correct answer. The answerOrder field will be an integer field that I use to determine whether or not the answer is randomizable and will have a default value of 0. If one of the answers is something like “All of the above” or “Both A and B” . The AnswerOrder field can be used to order the questions so “All of the above “ doesn’t appear firs in the list of choices. The order by clause of my sql will be “Order by QuestionOrder, Randomizednumber”.
MS Access database. The questions and answers will be entered in an MS Access front end application. I also have a lookup table in my Human Resources database and I will either add my subjects to that table or maybe create a separate table for the subjects.
A web application created with Visual Studio 2005 and vb. When the user selects a subject, the questions for that particular subject will be presented and a temporary table for the answers will be created. The temporary table will be called #Temp answers and will be a duplicate of the answers table with one additional field called “IsSelected”. When the user answers the question, I can compare the “IsSelected” value to the “IsCorrect” value to determine whether or not the answer is correct.
Recording when the user took the test and whether or not he passed or failed. I will have another table in my human resources database that will contain information such as date, employee id, subjected id, pass/fail, and score. Other stats on questions and answers mentioned in your previous post are beyond what I want to accomplish right now, but will be something I probably need to consider as we grow larger.
Completely computer illiterate.
I have accomplished most of the above and tested things to see if they work and they do. It’s a very simple application. My main question concerns using temporary tables.
1. I will use a web applications and windows authentication so when I create the temporary table, the table will be created in that user’s session (I think). When the user ends his session the temporary table will be deleted – no problem there, but if the user decides to take another test during the same session how do I delete the temporary table so I can re-create it? Temp tables aren’t listed in the sysobjects table. I am also constantly needing to recreate my temporary table as I’m testing and right now I don’t know how to do that.
How do I check to seem if a temporary table exists so I can write a conditional drop statement if the table currently exists?
The first problem that you are likely to hit is something known as "connection pooling" which is vital for any application that needs the ability to scale, and for IIS in specific. Connection pooling allows all of the logical connections on a given machine (in this case an IIS server) to map their logical connections to a (usually) much smaller group of physical connections as long as those physical connections have consistant properties. This means that from one call to another, there is NO guarantee that you'll be using the same database connection, which means that #temp can gleefully dance along with the physical connection (aka spid) from one logical connection (ADO connection object) to another.
While you might curse connection pooling at the outset, it really is a blessing in the long run. If you are feeling especially masochistic, go ahead and try turning off connection pooling on a dev or QA server... We'll wait, and we can always use a good laugh when you come back to explain or ask about the results!
This is one of many reasons that I suggested using a permanent table to store the test data (questions and answers). That would allow you to cook up a unique identifier (GUID values are splendid for this) that you'd use to identify which logical session the data tied to, and would allow all of the data to peacefully coexist in one table. Every so often you'd need to "empty the trash" of either all data or just the "old" data, but that is a trivial thing to work up at your leisure if and when you decide that you need it.
Once upon a long ago, temp tables were one of the DBA's best friends, and an absolutely necessary tool just to get work done. Temp tables are still more fun than one DBA ought to be allowed within stored procedures. The use of temp tables outside of stored procedures has gotten to be less and less important, and correspondingly less exciting with the last few releases of SQL Server.
In general, it sounds like you've been hit with one of those "tar baby" tasks, where once you touch it, you can't let go. It seems to have a life of its own, and that life sometimes threatens to overtake your own. I think we've all been there, most of us more than once.
It sounds to me like you've got a good handle on what you need to do right now. I'm a little leary of the long term, since I think that there are more things waiting for their chance to byte you that are just out of sight, but I'm also relatively comfortable that you'll recognize the feel of teeth in your pants and will deal with the little beggars appropriately.