Sign-up now and get instant access
Leave a comment
Customized training to master new skills and grow your business.
Beginner to advanced classes taught by Microsoft MVPs and Authors.
In-depth boot camps take you from a novice to mastery in less than a week.
Season Learning Pass
Get access to our very best training offerings for successful up-skilling.
Stream Pro Plus
Combine On-Demand Learning platform with face-to-face Virtual Mentoring.
Quick references for when you need a little guidance.
Summaries developed in conjunction with our Learn with the Nerds sessions.
Digital goodies - code samples, student files, and other must have files.
Stay up-to-date on all things Power BI, Power Apps, Microsoft 365 and Azure.
Earn money by driving sales through the Pragmatic Works' Training Affiliate Program.
It's time to address your client's training needs.
Learn how to get into IT with free training and mentorship.
Discover the faces behind our success: Meet our dedicated team
How can we help? Connect with Our Team Today!
Find all the information you’re looking for. We’re happy to help.
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:
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?
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!
Join other Azure, Power Platform and SQL Server pros by subscribing to our blog.