Temp Tables and Clustered Indexes

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)

Temp Tables and Clustered Indexes

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2006-03-27 : 11:07:45

I'm trying to optimize the exeution plan in a stored proc, and the focus is now on a CLUSTERED INDEX SCAN in a permanent table which seems to be SCANNED because an INNER JOINED temporary table isn't indexed. This could be a perfect clustered index INNER JOIN with the temporary table as the columns are there... all I need to do is create an identical CLUSTERED INDEX. Problem is, the CLUSTERED INDEX is a two-column index in the permanent table. If I want to mirror the clustered index in the temporary table, I run into a problem in that T-SQL DDL requires a fixed-name to be declared for explicit index declarations but not for implicit indexes: e.g.

CREATE TABLE #Temp (UserID INT NOT NULL UNIQUE) -- unnamed nonclustered index is implicit for column UserID

however...


CREATE TABLE #Temp (
   ClientID INT NOT NULL, 
   UserID INT NOT NULL, 
   CONSTRAINT MyIndexName CLUSTERED UNIQUE (ClientID, UserID)
)

Requires declaration of a name, but this is a temp table. Naming the index with a fixed name would create a naming conflict if two instances were executed at once.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-27 : 11:40:02

assuming this clustered index will solve your problem, you can do this instead to avoid the explicit naming issue:


CREATE TABLE #Temp (
   ClientID INT NOT NULL, 
   UserID INT NOT NULL, 
  primary key CLUSTERED (ClientID, UserID)
)

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2006-03-27 : 11:43:47

Sam,

Pony up some DDL and the sproc.

I can tell you this though...my temp tables usually always are gonna be scanned, because they usually will be the driver of the process.

So I don't to get it.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SamC
White Water Yakist

3467 Posts

Posted - 2006-03-27 : 13:24:17

It's not that I want to keep the DDL or SP private, but it's a lot of stuff to get an answer on how to create a clustered index without naming it explicitly.

What I will share is this: I found (eventually) that the execution plan for the SP changes from index scan to index seek when the optimizer sees large number of rows in the result set (index seek). For small results, and index scan is generated.

The execution plan is set the first time the SP is called, so the plan is preserved for subsequent calls, no matter if a small or large recordset result is generated.

Parameter sniffing I think this is called.

Sam

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-03-27 : 13:37:01

did TG's solution work for you?

This syntax seems to work for the unique key if that is what you want instead of a PK.

CREATE TABLE #Temp (
   ClientID INT NOT NULL, 
   UserID INT NOT NULL, 
   unique clustered (ClientID, UserID)
)

-ec

SamC
White Water Yakist

3467 Posts

Posted - 2006-03-27 : 14:48:59

quote:
Originally posted by eyechart

did TG's solution work for you?

This syntax seems to work for the unique key if that is what you want instead of a PK.

CREATE TABLE #Temp (
ClientID INT NOT NULL,
UserID INT NOT NULL,
unique clustered (ClientID, UserID)
)

Yes, TG's solution works, but better, I didn't know the syntax you posted was valid, and it seems to slip through QA just fine. So that's another DDL syntax that's very nice to know.

In the end, the 2-column clustered index doesn't seem to change the index scan to an index seek, but the overall query performance has dropped from 40 seconds to 4 seconds.

I'm happy.

Kristen
Test

22859 Posts

Posted - 2006-04-03 : 09:38:45

FWIW I always put a PK on my #TEMP tables - as a PK is clustered by default that would give you the same solution as TG suggested.

Kristen

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-04-03 : 11:27:06

quote:
Originally posted by Kristen

FWIW I always put a PK on my #TEMP tables - as a PK is clustered by default that would give you the same solution as TG suggested.

PKs are only clustered if you use the CLUSTERED key word, they aren't clustered by default.

-ec

Kristen
Test

22859 Posts

Posted - 2006-04-03 : 11:45:50

"PKs are only clustered if you use the CLUSTERED key word, they aren't clustered by default"

ec: You mean on #TempTables specifically? 'Coz I thought on "normal" tables Clustered was the default for a PK (i.e. if no other Clustered index pre-exists)

I hope I don't have to go back and refactor all my CREATE TABLE #TempTable

Kristen

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-04-03 : 16:19:54

quote:
Originally posted by Kristen

"PKs are only clustered if you use the CLUSTERED key word, they aren't clustered by default"

ec: You mean on #TempTables specifically? 'Coz I thought on "normal" tables Clustered was the default for a PK (i.e. if no other Clustered index pre-exists)

I hope I don't have to go back and refactor all my CREATE TABLE #TempTable

Kristen

ok, Kristen you are right. Here is my simple test case.

SET NOCOUNT ON

-- PK CLUSTERED TEST
CREATE TABLE #Temp (
   ClientID INT NOT NULL, 
   UserID INT NOT NULL, 
  primary key CLUSTERED (ClientID, UserID)
)

select 'PK CLUSTERED TEST', indid from tempdb.dbo.sysindexes where id = 
	(select parent_obj id from tempdb.dbo.sysobjects where type = 'K')

drop table #temp
GO

-- PK TEST (CLUSTERED keyword not specified)
CREATE TABLE #Temp (
   ClientID INT NOT NULL, 
   UserID INT NOT NULL, 
  primary key (ClientID, UserID)
)

select 'PK TEST (CLUSTERED keyword not specified)', indid from tempdb.dbo.sysindexes where id = 
	(select parent_obj id from tempdb.dbo.sysobjects where type = 'K')

drop table #temp
GO

-- UK CLUSTERED TEST
CREATE TABLE #Temp (
   ClientID INT NOT NULL, 
   UserID INT NOT NULL, 
  unique clustered (ClientID, UserID)
)

select 'UK CLUSTERED TEST', indid from tempdb.dbo.sysindexes where id = 
	(select parent_obj id from tempdb.dbo.sysobjects where type = 'K')

drop table #temp
GO

-- UK TEST (CLUSTERED keyword not specified)
CREATE TABLE #temp (
   ClientID INT NOT NULL, 
   UserID INT NOT NULL, 
  unique (ClientID, UserID)
)

select 'UK TEST (CLUSTERED keyword not specified)', indid from tempdb.dbo.sysindexes where id = 
	(select parent_obj id from tempdb.dbo.sysobjects where type = 'K')

drop table #temp
GO

here are the results:

                  indid  
----------------- ------ 
PK CLUSTERED TEST 1

                                          indid  
----------------------------------------- ------ 
PK TEST (CLUSTERED keyword not specified) 1

                  indid  
----------------- ------ 
UK CLUSTERED TEST 1

                                          indid  
----------------------------------------- ------ 
UK TEST (CLUSTERED keyword not specified) 0
UK TEST (CLUSTERED keyword not specified) 2

anything with indid = 1 is a clustered index. Both PK tests show indid = 1, only the UK test that specifies CLUSTERED gets an indid = 1.

I did this same test with standard tables and the same thing occurs. So, it looks like SQL Server will slap a clustered index in place on a PK if it can. I am not sure I like this feature though.

-ec

Kristen
Test

22859 Posts

Posted - 2006-04-03 : 16:50:36

"I am not sure I like this feature though"

Well, that's a good point indeed, as I have broken my own rule which is to explicitly name things which would otherwise rely on Defaults / side effects - in case future versions behave differently.

I will name them explicitly in future.

Kristen

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-04-04 : 07:28:37

For what it is worth from BOL:

SQL Server automatically creates a unique index to enforce the uniqueness requirement of the PRIMARY KEY constraint. If a clustered index does not already exist on the table, or a nonclustered index is not explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources