Survey Says !!!!!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Survey Says !!!!!!

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-27 : 15:23:08

I'm building a survey table much like that on the sqlteam home page.

CREATE TABLE dbo.Survey (
Abbr VARCHAR (50) , -- Abbreviated quetion ID
Question VARCHAR (200) , -- The entire survey question
Answer VARCHAR (20) , -- This User's answer to the survey
UserID INT NOT NULL -- User ID of respondant
)
GO

When a user submits his survey result, the insert of his feedback will be followed by a query against the table. Something like:

SELECT Answer, Count(*) as Total
FROM Survey
WHERE Abbr = @Abbr -- User's Survey ID
GROUP BY Answer

My question is, what's the best kind of index or clustered index for the Survey table?

Sam

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-03-27 : 23:16:17

As far as clustered index is concerned, only UserID makes any sense. The other columns have way too many duplicate values, and will cause high contention if many users try to answer the same questions at once.
And for nonclustered index, probably the Abbr columns since you're querying it. You might do a compound index on Abbr and Answer to get a covered index on the table.

Actually: If I understand your structure, you have a lot of redundant data. It seems like you will be repeating the full text of the question on each line. To normalize your data, split this into two tables: A Question table with the Abbr and Question fields, and a Responses table with a foreign key to Questions, UserID and Answer.

Sarah Berger MCSD

Edited by - simondeutsch on 03/27/2003 23:20:00

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-28 : 08:45:15

Thanks Sarah,

Not much in BOL searching on "compound index".

I'm guessing this is what is meant by a compound index.

CREATE INDEX MyIndex
ON Survey (Abbr, Answer)

BTW - I winced when I added the Question to the column. It should've been a separate table and it's gone now.

A new requirement crawled out of the back room. I've got to combine survey results from 3 languages, so this sort of shoots the idea of storing the answer in the native language anyway....

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources