Suppressing "Index is being rebuilt" from a proc

[mfairchild]

I'm putting a clustered index on a temp table in a stored proc and keeping getting the message "Index (ID = 2) is being rebuilt" when the results are returned. I know what the message means, but I don't know how to suppress it. The temp table has no other indexes on it. Anyone have any ideas?

Sample code:
Create Table #TempTable
(Field1 int)

INSERT INTO #TempTable (Field1)
SELECT Field1
FROM AnotherTempTable

CREATE CLUSTERED INDEX Field1_TempTable_idx
ON #TempTable (Field1)

 

[mfairchild]

Sorry, I forgot. I'm using SQL Server 7.0.

 

[tlbroadbent]

SQL must rebuild all non-clustered indexes whenever it builds or rebuilds a clustered index. You must have added a unique constraint to a column when creating the temp table. A unique constrint creates a non-clustered index. When you build the clustered index, the other index is rebuilt.

I don't if it is possible to suppress the message unless you create the non-clustered index or unique contraint after creating the clustered index. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[mfairchild]

Thanks, Terry. I knew that clustered indexes rebuild the other indexes and that's what was bothering me. The table has no other indexes or constraints. Other than the table and field names, the code I posted is the same as what I was running.

Just for kicks, I changed the "Create Table / Insert Select" into a "Select Into". (Don't ask me why it was done the other way...I don't know) The message is gone and thus the phantom index. I'm curious on how an index got on the table...

Thanks for your response.

Matt