In this post, let us see about temporary tables in Azure SQL database and Azure SQL data warehouse.
Global (DB scoped) & Local temporary tables are supported for Azure SQL Database and follow the same syntax and semantics that SQL Server uses.
Local temporary table (#table_name),
Database scoped global temporary table (##table_name)
Lets see an example for below statement:
"Azure SQL Database supports global temporary tables and it can be accessed only by all users sessions within the same Azure SQL database."
I have created two Azure sql databases azsqldb1 & azsqldb2.
As shown in below image, created temp table inside azsqldb1 from session 1
As shown in below image, temp table was accessible from session 2, this session points to database azsqldb1 where temp table was created.
As shown in below image, temp table was not accessible when queried from another session which points to database azsqldb2.
"In SQL Data Warehouse, temporary tables scope is at the session level when temp table is created as stand alone statement. When created inside a stored procedure it can be accessed in other sessions as well.
Temporary tables are slightly different in Azure SQL Data Warehouse as they can be accessed from anywhere including both inside and outside of a stored procedure."
I have created two Azure sql DW databases DW1 & DW2.
Created a stored procedure within DW1 from session1, temp table is accessible even outside of stored procedure as shown below:
Temp table is accessible from session 2 (same database DW1) as shown below:
Reason behind this is, as I have executed stored procedure that creates temp table it is available in session 2.
Temp table is not accessible from session 3 (same database DW1) as shown below:
(As temp table scope is at session level in azure sql data warehouse)
Temp table is not accessible from session 3 (different database DW2) as shown below:
(As global temp table is not supported in azure sql data warehouse)
Temporary tables can also be created with a CTAS (CREATE TABLE AS SELECT)
Temp tables in Azure SQL database:
Global (DB scoped) & Local temporary tables are supported for Azure SQL Database and follow the same syntax and semantics that SQL Server uses.
Local temporary table (#table_name),
Database scoped global temporary table (##table_name)
Lets see an example for below statement:
"Azure SQL Database supports global temporary tables and it can be accessed only by all users sessions within the same Azure SQL database."
I have created two Azure sql databases azsqldb1 & azsqldb2.
As shown in below image, created temp table inside azsqldb1 from session 1
As shown in below image, temp table was accessible from session 2, this session points to database azsqldb1 where temp table was created.
As shown in below image, temp table was not accessible when queried from another session which points to database azsqldb2.
Temp tables in Azure SQL data warehouse:
Lets see an example for below statement:"In SQL Data Warehouse, temporary tables scope is at the session level when temp table is created as stand alone statement. When created inside a stored procedure it can be accessed in other sessions as well.
Temporary tables are slightly different in Azure SQL Data Warehouse as they can be accessed from anywhere including both inside and outside of a stored procedure."
I have created two Azure sql DW databases DW1 & DW2.
Created a stored procedure within DW1 from session1, temp table is accessible even outside of stored procedure as shown below:
Temp table is accessible from session 2 (same database DW1) as shown below:
Reason behind this is, as I have executed stored procedure that creates temp table it is available in session 2.
Temp table is not accessible from session 3 (same database DW1) as shown below:
(As temp table scope is at session level in azure sql data warehouse)
Temp table is not accessible from session 3 (different database DW2) as shown below:
(As global temp table is not supported in azure sql data warehouse)
Temporary tables can also be created with a CTAS (CREATE TABLE AS SELECT)
- Temporary tables offer a performance benefit because their results are written to local rather than remote storage.
- Global Temporary Tables are not supported.
Initially global temp table was not supported in Azure SQL database, later it was introduced. So we might expect some changes related to temp tables in Azure sql database and Azure sql data warehouse.
No comments:
Post a Comment