Was the index created or not?

Question

Was the index created or not?

water490

Ten Centuries

Points: 1174
More actions
October 15, 2025 at 2:49 pm

#4672433

Hi everyone
I am getting an error when I create the index but I don't get any errors when I save and run the SP. Does this mean the index was created or not? Is there a way to verify? The index is on a temp table.
Thank you

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

water490 Ten Centuries Points: 1174 More actions · Answer 1

I forgot to mention...I am not seeing any performance improvements so that is why I am kind of thinking the index is not being setup correctly but that is just my guess. If my approach is wrong please let me know the correct way.

I have created a temp table that is created as follows "select...into #temp..." so the table is not explicitly created using a create table command.

Jo Pattyn SSC-Dedicated Points: 32552 More actions · Answer 2

if you have the queryplan you can view if it's being used

from stackoverflow "sql server check if index exists on temp table" (copied because there forum doesn't like links lately)

CREATE TABLE #temp (id int, val1 int)

CREATE INDEX ix_t1 on #temp (id)

EXEC tempdb.dbo.sp_helpindex '#temp'

CREATE TABLE #tmpTable (ID BIGINT PRIMARY KEY, INDEXCOLUMN BIGINT)

IF NOT EXISTS(SELECT * FROM tempdb.sys.indexes WHERE name = 'IX_TMPINDEX' AND OBJECT_ID = object_id('tempdb..#tmpTable'))
BEGIN
CREATE NONCLUSTERED INDEX IX_TMPINDEX ON #tmpTable (INDEXCOLUMN)    
END

GO 

SELECT * FROM tempdb.sys.indexes WHERE OBJECT_ID = object_id('tempdb..#tmpTable')