Table Index - 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)

Table Index

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-17 : 11:58:14

Any commentary on this table's keys, indexes, ways to improve it?

CREATE TABLE dbo.QuizPool (
CourseID	INT NOT NULL ,
ModuleAbbr	VARCHAR (10) NOT NULL ,
UserID		INT NOT NULL ,
QuestionID	INT NOT NULL
)
GO
CREATE UNIQUE INDEX IDX_QuizPool ON dbo.QuizPool (CourseID, ModuleAbbr, UserID, QuestionID)
GO

The usage of the table is limited to the following query:

	SELECT	Top 1 @QuestionID = QuestionID
		FROM	dbo.QuizPool
		WHERE	CourseID = @CourseID
		AND	ModuleAbbr = @ModuleAbbr
		AND	UserID = 0
		AND	QuestionID NOT IN (
				SELECT	QuestionID
					FROM	dbo.QuizPool
					WHERE	CourseID = @CourseID
					AND	ModuleAbbr = @ModuleAbbr
					AND	UserID = @UserID )

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-17 : 12:46:42

What does the plan say now?

And where's your order by?

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-17 : 12:48:59

Hmm.. Cut and Paste problem.

ORDER BY IS AT THE END:

ORDER BY NEWID()

I'm still foggy on creating tables without primary keys and all. Can this table be created with a better indexing schema?

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-17 : 12:52:30

I got index seeks for that query and index...

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-17 : 12:56:20

Ok. I'll leave it as is.

Thanks,

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources