3

Using SQL Server 2014:

In my TSQL database application, I create temporary tables that hold the primary key IDs of the records I am working with. 

DECLARE @TempListOfIDs TABLE(PKID BIGINT)
  • The table will hold between 1000-5000 items in it. That is the work chunk the stored procedure needs to get done on each execution. I throttle the workload with this approach.
  • I use the temporary table to join it with quite large tables, all of them have clustered index based on the PKID. So joining is quite optimised at the larger table side, but not sure with the temp table side.

The question is should I create a clustered index on @TempListOfIDs.PKID column? I am concern that with such small number of rows in the table (<5000 rows) the cost of creating the clustered index on the temp table is more than its benefit.

Allan Xu
  • 7,998
  • 11
  • 51
  • 122

3 Answers3

3

That is a table variable not a temporary table.

Assuming that the values you insert into it are unique I would likely use

DECLARE @TempListOfIDs TABLE(PKID BIGINT PRIMARY KEY)

There are no column statistics on table variables and the constraint can provide some useful density information. Also you should definitely consider using OPTION (RECOMPILE) so the row count of the table variable is taken into account.

Also the supporting index can certainly be useful for some execution plans. E.g if the table variable ends up on the inside of the nested loops join.

If this is some performance critical piece of code then of course just test it.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Thank you for help. Are you suggesting OPTION (RECOMPILE) for the query that fills the @TableVar or every subsequent query that uses @TableVar? If the later, then wouldn't that increase the overhead of rebuilding the query plan? – Allan Xu Jul 26 '16 at 18:33
  • @AllanXu the subsequent queries that use the table variable. And yes it is a trade off. If compilation time is small compared to the difference in execution time from getting a better plan it is probably worth doing. Conversely if you always want the same plan shape and that's the one you get anyway then it probably isn't worth doing. – Martin Smith Jul 26 '16 at 18:38
  • One last question: Would you choose #TempTables or @TableVariables for this scenario? Thanks so much for the helpful answer. – Allan Xu Jul 26 '16 at 18:41
  • Again it depends, `#temptables` are probably a better default choice as they automatically get column statistics created and can better dynamically adapt to differing input data. You are less likely to get a catastrophically bad plan due to this. But if you know exactly the plan you want and that it is always going to be static then using table variables and possibly hints to force that plan can work out a bit better in terms of overhead for statistics, recompiles and tempdb metadata. Some other points in my answer here http://stackoverflow.com/questions/11857789/ – Martin Smith Jul 26 '16 at 18:48
1

The only way to know for sure in your situation is to test both cases. I personally believe that it is not worth it if you are doing an inner join.

SQL should recognize that all records / columns from the small table are needed and as such any index would be ignored. Even with an index a full table scan would be necessary.

Joe C
  • 3,925
  • 2
  • 11
  • 31
0

A clustered index may not help but if you add the values in the order of the clustered index then it would cost very little.

Look at some query plans both ways.

paparazzo
  • 44,497
  • 23
  • 105
  • 176