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?

  • 1
    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
  • 2
    @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?
    – Learner
    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.


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?

  • 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.
    – Learner
    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.
    – Learner
    Jan 30 '13 at 16:16
  • 2
    @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... Jan 30 '13 at 17:06

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!

  • 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?
    – Learner
    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 sqlservercentral.com and ask for help there. 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?
    – Learner
    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.
    – Learner
    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. Jan 31 '13 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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