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

Index Jeopardy

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-02 : 13:55:39

CREATE TABLE dbo.Assignments (
UserID INT NOT NULL ,
CourseID INT NOT NULL
)
GO
CREATE INDEX AssignmentsUserID ON dbo.Assignments (UserID)
GO

OK. There's the DDL. Everyone happy now?

I'll choose "Index Questions" for $100 Art!

Question is:

What's the optimal index to determine if a user has a particular course assigned? (see DDL above)

a) Clustered Index on UserID, CourseID
b) Unique indexes for both UserID and CourseID
c) Index on UserID alone
d) Ask the audience (Wait! that's not Jeopardy)

A sample usage

SELECT count(*) from users u
inner join assignments a on a.userid=u.userid
where a.courseid=@courseid

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-02 : 14:09:51

What is "A"

Art...man betcha a LOT of people don't know Art...just Alex...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-02 : 14:18:26

i would think by definition if this is a "many-to-many" table you would want you primary constraint / clustered index on both columns.

then, you could add an index on CourseID if needed. depends on the size of the table and how often you will query it.

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-02 : 14:24:38

Jeff,

If users is the first column in the clustered index, then you do not need an additional index just on users. This would be a duplicate index.

Isn't that correct?

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-02 : 14:30:25

OK. Here's goes...

1 - I don't remember Art or Alex myself. What's in a name anyway? I found there was a Mike also when I did a search on Jeopardy.

2 - What is 'A'? I thought it was a clustered index? I must misunderstand something. I thought a clustered index could involve more than 1 column?

3 - Jeff, would you translate your answer into DDL?

4 - I think Tara's correct, but is a clustered index the right approach?

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-02 : 14:46:54

(ooops , I changed my answer -- you're right Tara, I accidentally put "user" instead of "CourseID" for the second potential index, i had them backwards)

sam -- it would be something like:

create table #tmp
(userID int not null,
courseid int not null,
constraint PK_Name primary key clustered (userID, courseID))

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-02 : 15:03:47

Thanks everyone.

I've noticed that I have a table scan of a Tally table.

CREATE TABLE dbo.Tally (
ID INT NOT NULL )

Tally.ID is used in some where statements.

What's the best way to index Tally? Is a clustered index going to perform identically to an index?

Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-02 : 15:05:04

Just create a non-clustered index on Tally.ID. The data is already sorted so don't need the clustered one. Clustered takes up more space, so if you don't need it, then go with non-clustered.

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-02 : 15:11:58

Thanks.

The execution plan just shrunk.

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-02 : 15:51:11

I disagree -- i'd go with clustered on a tally table. i may be wrong, but i believe that clusterd indexes perform quite well when you do return ranges of data from tables, and by definition you are often returning ranges of data from a tally table.

a clustered index just says -- "ensure the data is stored sequentially". even if you inserted rows into a table in a very orderly manner, if you didn't declare a clustered index , you have no guarantee SQL is storing the rows in order.

i could be totally wrong about all this, feel free to yell at me !

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-02 : 15:55:33

Sam,

How are you referencing the Tally table?

And how big is it?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-02 : 16:01:56

It's 8000 Int's long

Typically it'll parse character strings that are fewer than 40 characters long.

Clustered or non?

Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-02 : 16:02:51

I'm sure Jeff is right.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-02 : 16:06:07

quote:
Originally posted by tduggan

I'm sure Jeff is right.

Always a safe assumption...

But it's how you use them that will determine what happend I guess is my point



USE Northwind
GO

CREATE TABLE myTally (col1 Int IDENTITY(1,1) PRIMARY KEY CLUSTERED, col2 bit)
GO

SET NOCOUNT ON

DECLARE @x int
SELECT @x = 1
WHILE @x < 101
	BEGIN
	INSERT INTO myTally(col2) SELECT 1
	SELECT @x = @x + 1
	END

SET NOCOUNT OFF
GO
SELECT * FROM myTally 
GO
SELECT * FROM myTally WHERE col1 = 50
GO
SELECT * FROM myTally a, myTally b WHERE a.col1 = b.col1
GO

DROP TABLE myTally
GO

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-02 : 16:07:26

quote:
Originally posted by SamC

It's 8000 Int's long

Typically it'll parse character strings that are fewer than 40 characters long.

Clustered or non?

Sam

Sam,

Can you post that code?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-02 : 17:13:15

-- Process the Assignments
INSERT INTO dbo.Assignments (UserID, CourseID)
	SELECT	UC.UserID, UC.CourseID
		FROM	(
			SELECT	U2.UserID,  -- Rowset of UserID and CourseID from UserImport CSV string 'Assignments'
				CAST(SUBSTRING(' ' + Assignments + ' ', ID, CHARINDEX(' ', ' ' + Assignments + ' ', ID)-ID) AS INT) AS CourseID
				FROM dbo.UsersImport UI
				CROSS JOIN dbo.Tally
				INNER JOIN (SELECT UserID, Username FROM dbo.Users WHERE ClientID = @ClientID) U2 ON U2.Username = UI.Username
				WHERE	ID BETWEEN 2 AND Len(' ' + Assignments + ' ') -- Limit the rows used in the Tally table 
				AND	SUBSTRING( ' ' + Assignments + ' ' , ID - 1, 1) = ' '
				AND	ISNUMERIC(SUBSTRING(' ' + Assignments + ' ', ID, CHARINDEX(' ', ' ' + Assignments + ' ', ID)-ID)) = 1 -- Ensure this is a valid integer
			) UC
		LEFT OUTER JOIN dbo.Assignments AI ON AI.UserID = UC.UserID AND AI.CourseID = UC.CourseID
		WHERE	AI.UserID IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources