Non-Clustered-Index on a temporary table

Question

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?

Answer 1

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.

Answer 2

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;
go

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

insert into #MyTempTable
default values;
go 100

select *
from #MyTempTable;

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

select
    name,
    object_id,
    type_desc
from tempdb.sys.tables
where name like '#MyTempTable%';

select
    name,
    object_id,
    index_id,
    type_desc
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?

Answer 3

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!