Was the index created or not?

Question

Was the index created or not?

water490

Ten Centuries

Points: 1210
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 4 posts - 1 through 4 (of 4 total)

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

water490 Ten Centuries Points: 1210 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: 32575 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')

Ed B Hall of Fame Points: 3690 More actions · Answer 3

The error about not allowing included columns is an intellisense warning, not a syntax error, and I see it in SSMS 21 and 20 any time I create a clustered columnstore. I hadn't noticed it was an intellisense message until now, but I did see it last week and I was offended that SQL server thought so little of me. It doesn't matter whether the table exists, whether it's a temp table or whether you put the index name in square brackets, it still shows the inappropriate warning by the index name. It doesn't prevent the code from running so if you compiled the procedure it should be creating the ccs index. If you look at the execution plan for your proc you should see a clustered columnstore scan if you have a select distinct column from columnstore table.

Attachments:

You must be logged in to view attached files.