Global Temporary Tables in Azure SQL Data Warehouse

Azure SQL Data Warehouse (DW) has been improving its capabilities day-by-day. This relational database platform used by Microsoft is known to be faster and cheaper than other major cloud data warehouse solutions.

In this post, I will discuss a consideration for migrating databases from SQL Server on-prem or Azure SQL DB to Azure SQL Data Warehouse as it relates to global temporary tables.

As you may know, with SQL Server on-prem or Azure SQL DB, local temp tables have one pound sign (#) as a part of the table’s name, #Products_az for example.  Local temp tables are visible only to the connection used to create it and are deleted after the connection is closed.

A global table will have two pound signs, ##Products_az for example.  Global temp tables are visible to any connection.  A global temp table is deleted automatically when the last connection that references it has closed.  

You may have code in a stored procedure that creates a global temporary table in SQL Server on-prem or Azure SQL DB.  The code to create a global temp table will execute in Azure SQL DW without error to ostensibly create a table named ##Products_az, for example.  However, any subsequent code that queries ##Products_az will fail. Since global temp tables are not supported in Azure SQL DW, the code to create the table really created a local temp table with a name beginning with a single #.  The “##” table is not created.

Let’s look at an example in Management Studio:

• Open a query window on a connection to an Azure SQL DB.
• Execute SQL to create a new global temp table loaded with sample data.
• Query the global temp table. Verify the global temporary table was created and contains data.
• Next, open a new session and check that that table is available in this new session.  Successfully querying this global temp table from another connection verifies this functionality on Azure SQL DB.

Now here’s where the problem comes in. At this time, Azure SQL DW only supports local tables. So, what will happen if we try to create a global temporary table in Azure SQL DW?

• Open a query window on a connection to an Azure SQL DW
• Using the same code from earlier, execute SQL to create a new global temp table loaded with sample data
• Querying the global temporary table fails!

The error is occurring because Microsoft SQL Server is still considering this a local temporary table because it looks for the first pound sign while the second pound sign is ignored and consider part of the name.

This is a problem if you are trying to do a migration of hundreds of stored procedures because you will be able to batch, compile and even run without any errors. You will not run into an error until a second stored procedure or process tries to use that table which is quite a big issue.

It’s important to keep in mind that global temporary tables are not supported in Azure SQL Data Warehouse.

I hope these tips will help you to know what to consider when creating local and global tables in Azure SQL Data Warehouse. If you’re new to Azure SQL Data Warehouse or want to learn more about the migration process, please click the link below. We would love to help you take your business from good to great! 

Looking for more about Azure? We've got the conference for you! Azure Data Week is coming to you in October - the only virtual conference 100% dedicated to Azure topics. With 4 jam packed days, eight 1-hour sessions each day that you can pick and choose from, plus access to all the recordings for one year all for only $49! Click the link below to learn more and register for this incredible event!