syntax for index in temp table

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

Transact-SQL (2000)

syntax for index in temp table

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-02-22 : 18:59:06

Hi,
I am creating a temp table as shown in code.
what will be syntax to create a index on jobnumber.
I can have duplicate jobnumbers.


CREATE TABLE #ProgGDDTPHours
(
	JobNumber varchar(10), 
	Programming float,
	GD float,
	DTP float
)

mk_garg

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-22 : 19:01:08

Did you look up CREATE INDEX in BOL?

CREATE INDEX idx_JobNumber on #ProgGDDTPHours(JobNumber)

Tara

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-02-22 : 19:11:26

I had a look.
what i dont know how it came into mind that may be we can create index as part of "Create Table" statement.

I got this error
"Cannot create an index on table '#ProgGDDTPHours', because this table does not exist in database 'ABC_Systems'"

May be because it is temp table

What i have to do for this?

thanks

mk_garg

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-22 : 19:14:12

Maybe you have a GO in there or you are in another session where the temp table doesn't exist. This worked fine for me:



CREATE TABLE #ProgGDDTPHours
(
	JobNumber varchar(10), 
	Programming float,
	GD float,
	DTP float
)

CREATE INDEX idx_JobNumber on #ProgGDDTPHours(JobNumber)

DROP TABLE #ProgGDDTPHours

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-22 : 19:14:58

You can add a PK (you automatically get an index with it) in the CREATE TABLE statement, but not an index.

Tara

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-02-22 : 19:24:33

I created indexes for many temp tables.
i made some mistake.

Thanks tara

mk_garg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources