3

Does Azure SQL Data Warehouse support any indexing on temporary tables? No mention of the limitation is found at https://learn.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-temporary.

In one attempt at such, I received: 

 Cannot create a non-clustered index on a temporary table.

Does that wording infer a clustered index can be placed on a temporary table?

Steve
  • 548
  • 8
  • 24
  • Yes, synapse #temp_tables, at least ones populated ala select * ... into #temp_table... get a clustered index created on them automatically. so this may be a pro-tip for pre-creating your #temp tables so that you can be in control of your clustered index definitions. Otherwise, if you can find its actual name, you can drop it and recreate it how you like it (I did just this yesterday). – user1390375 Sep 30 '21 at 21:14

2 Answers2

3

At one of my project usage temporary tables with indexes at several columns and with option adding new columns to them was quite critical to for me. (ALTER TABLE is also not supported for temp tables). The standard table was not the option as each query run had to have own table.

Finally, I used something that can be used as temporary tables but in fact is not a temporary table. I'm creating a standard table but with Guid as name and assign the name to a variable. So instead of #TempTable I'm using @MySemiTempTable but that work only for generated code. So I have to run that code using sp_executesql. Bellow example:

declare @MySemiTempTable  NVARCHAR(MAX)
declare @sql  NVARCHAR(MAX)

set @MySemiTempTable = 'TMP_' + CAST(NEWID () AS NVARCHAR(37))
set @sql = 'CREATE TABLE ['+ @MySemiTempTable + '] (Column1 [int], Column2 NVARCHAR(50))'
EXECUTE sp_executesql @sql
set @sql = 'INSERT INTO ['+ @MySemiTempTable + ']   VALUES (1, ''test1'')'
EXECUTE sp_executesql @sql

set @sql = 'create unique index column1 on  ['+ @MySemiTempTable + '] (column1)'
EXECUTE sp_executesql @sql

set @sql = 'select * from  ['+ @MySemiTempTable + '] '
EXECUTE sp_executesql @sql

set @sql = 'drop table   ['+ @MySemiTempTable + '] '
EXECUTE sp_executesql @sql
Krzysztof Krysztofczyk
  • 627
  • 5
  • 17
2

Clustered indexes are supported on temp tables. For example:

CREATE TABLE #temp_table   
    ( 
    c1 integer
    ,c2 integer
    )  
     WITH (  CLUSTERED INDEX (c1 ASC) )

;

Steve
  • 548
  • 8
  • 24
  • 2
    All initial index configurations are available on temp tables ie `HEAP | CLUSTERED COLUMNSTORE INDEX | CLUSTERED INDEX`. Only secondary index are not supported on temp tables at this time. – wBob Dec 15 '16 at 21:38