Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am working on various query optimization techniques. I reduced the query execution time from 1 minute to 12 seconds just by adding a Non-Clustered-Index on a table including one column (which is used in multiple where conditions) but DBA is very picky about adding indexes.

I would like to know if it makes any difference by adding NCI on a temp table instead of actual table. If YES, how? If NO, why?

share|improve this question
The best way to get answers to such question is to do your own benchmarks. Install Dev Edition on your desktop and start learning things on your very own personal sandbox - this will be much faster, and you will learn much more. – A-K Jan 30 '13 at 17:07
@Alex: Yes, I can do that. But I need explanations on certain things and talk about it with experts. I suppose that's the primary reason why we visit Stack overflow. No? – Mihir Jan 30 '13 at 17:21
There may be a big difference between data slowly read from slow storage, and data quickly read from the cache. I am not sure if you can easily reproduce the difference with temporary tables. – A-K Jan 30 '13 at 19:12

Temporary tables comply to the same rules as permanent tables when it comes down to indexing. The only difference is in the storage location, which is Tempdb for temporary tables.
However, if you are adding an index to a table that is heavily written, you have to take into account the write vs. read tradeoff.

Since the temporary table is probably used in a procedure or in a script, it's your code that controls how hard you're hitting the table with writes.
INSERTs are faster without indexes in place: if you're inserting lots of data in multiple statements, you probably want to create the index after fully populating the table.
UPDATEs and DELETEs have to find the row(s) to modify first, so they could highly benefit from proper indexing.

If your DBA wants to pay (a lot) more reads + CPU + elapsed time vs. some writes, I think (s)he should clarify his point.

Long story short, if your code runs faster with a NCI, go on and add it.

share|improve this answer
Thanks for the info! – Mihir Jan 30 '13 at 16:51

What kind of differences are you looking for? The difference is that the table is stored in tempdb, as opposed to the current database. The same goes for the index. See this below:

use TestDB;

if exists (select 1 from tempdb.sys.tables where name like '#MyTempTable%')
    drop table #MyTempTable;
create table #MyTempTable
    id int identity(1, 1) not null

insert into #MyTempTable
default values;
go 100

select *
from #MyTempTable;

create unique nonclustered index IX_MyTempTable
on #MyTempTable (id);

from tempdb.sys.tables
where name like '#MyTempTable%';

from tempdb.sys.indexes
where name = 'IX_MyTempTable';

You should see something similar to the output above:

name                            object_id         type_desc
--------------------------      -----------       ---------
#MyTempTable________......      -1516322775       USER_TABLE

name                   object_id         index_id          type_desc
--------------         -----------       -----------       ---------
IX_MyTempTable         -1516322775       2                 NONCLUSTERED

What other differences are you looking for? What is the DBAs justification behind the uneasiness of creating an index on a temp table?

share|improve this answer
The difference I am trying to understand is in terms of cost to re-build the index on actual table vs temp table. How does the rebuild work on physical table vs temp table? DBA's uneasiness is with creating index on existing physical table which is because this table is a high traffic table with 100s of records consistently coming in every few seconds. – Mihir Jan 30 '13 at 16:08
You should consider the time to rebuild indexes on temp tables to be the same as other tables. Why? Because they are both physical tables. – Jimbo Jan 30 '13 at 16:13
@Jimbo: Sorry didn't get what you said. Please explain. – Mihir Jan 30 '13 at 16:16
Thanks for the info! – Mihir Jan 30 '13 at 16:50
@Mihir Can you explain why you would be rebuilding indexes on a temp table? Why are you concerned about that aspect specifically? Also if your DBA is concerned about "100s of records" then you might want to invest in a tin foil hat... – Aaron Bertrand Jan 30 '13 at 17:06
up vote 0 down vote accepted

Synopsis on the approach of adding NCI dynamically on a high traffic table :

The time involved in creation of NCI on a temp table was much more than reading it. So, overall it took much more time to execute the query.

NCI on actual table is much much faster.

Finally, decided to optimize the query a little bit and create NCI on the actual table.

Answer to my question is YES

it makes huge difference when you create NCI on temp table vs actual table. At least for my scenario (high traffic table with millions of records and constantly coming data) it's a not correct approach to create NCI on temp table as it takes more time to build the index then to read it (again this is for my type of scenario).

My understanding on Indexes was limited when I asked the question. Learnt a bit more today.

Thanks everyone!

share|improve this answer
I would like to see the explanation for down-vote. Why do people down-vote without any solid explanation? How is this answer misleading to anyone? – Mihir Jan 31 '13 at 14:16
Downvote isn't mine (needs 125 rep). However, if I could downvote, I would. It makes absolutely no difference in performance whether you create your indexes on a temporary or permanent table. If the new NCI on the temp table doesn't make your code faster, it's not a matter of table type (temporary vs. permanent) but it's a matter of code flow. If you want advice on how to tune your code, I would suggest posting it to a forum such as and ask for help there. – spaghettidba Jan 31 '13 at 15:46
Ok, how come it took more time to execute the query after I added index to the temp table? Can you explain? – Mihir Jan 31 '13 at 17:34
What about the time to rebuild the NCI on temp table every time the procedure is executed? I was talking about creating NCI dynamically after creation of temp table. It's a silly approach though but that's what I meant. – Mihir Jan 31 '13 at 17:42
If you're inserting millions of rows into a temporary table every time a stored procedure is executed, that's where things go wrong. It's not the index's fault. I would suggest changing your strategy. Let me state it again: indexes on temporary tables do not come at a higher price than indexes on permanent tables. – spaghettidba Jan 31 '13 at 18:18

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.