Aim :– Can we create indexes on temporary tables in SQL Server?
Description:– Absolutely YES, we can create indexes on temporary table. This article aims at providing information on Indexes and temporary tables. As TEMP table is also a table, we can create indexes on tables and views. But the reason for raising this question is that many people thinks that temporary tables are created in the TEMPDB for some time (till the session ends), so they assume that it is not possible to create indexes on temporary tables. I too fall in the category of these types of people and used to think that we cannot create indexes on temporary tables. So, I questioned many people about this doubt, few people said we can create indexes on temporary table and few people said we can’t create indexes on temporary tables. It made me more confused on this topic and my curiosity level increased to cloud 9 to find the true solution for this. Many job seekers faced this as an interview question at the time of interviews. This is one of a frequent question in interviews, so I took all these things into consideration while writing this post.
Let’s go on with the practical session to resolve this question.
Firstly I am creating a temp table and naming it as #test in PHPRING database.
–Using PHPRING database
–Creating temp table
create table #test (
name varchar(10) )
Select and execute the below code to create clustered index on #test table
–Creating index on #test table.
create clustered index ix_test_id on #test (id asc)
In the above screen shot we can clearly see that the code is executed successfully without getting any error.
Now, Let us see whether that index is created or not on #test table by using system defined stored procedure, but make sure we are writing query in TEMPDB.
–Check whether indes is created or not on temporary table
In the above screen shot we can clearly observe that clustered index is created and exists on table #test. Let’s clear this temporary table as our doubt gets resolved.
–drop table #test
drop table #test
Conclusion:– Finally from the output of our practical session, it can be concluded that we can create indexes on temporary tables.
- Created one temp table #test in TEMPDB.
- After that, we created clustered index on the TEMP table #test.
- Then saw index is created or not on table #test by using built in stored procedure ( sp_helpindex #test).
Hope you all enjoyed this article, all of your suggestions and comments are welcome.