Can we create indexes on temporary tables?

2
892

Aim : Can we create indexes on temporary tables in SQL Server?

Can we create indexes on temporary tables?
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

use PHPRING

go

 –Creating temp table

create table #test (
id int,
name varchar(10) )

Create table

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)

Create clustered index on table

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

use tempdb

go

sp_helpindex #test

Output of created index on temp 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.

Summary:-

  1. Created one temp table #test in TEMPDB.
  2. After that, we created clustered index on the TEMP table #test.
  3. 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.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.9/10 (19 votes cast)
VN:F [1.9.22_1171]
Rating: +17 (from 17 votes)
Can we create indexes on temporary tables?, 9.9 out of 10 based on 19 ratings

2 COMMENTS

Comments are closed.