In the past couple of months, I have run into some annoying limitations and unsupported features with T-SQL in our Azure Synapse Dedicated SQL Pools, which is where my team puts our data warehouses for reporting needs across our business. These unsupported features were a bit surprising to me, which is why I’ve decided to share them in this post. This is certainly not going to be an all-inclusive list of things you can’t do in Dedicated SQL Pools that you can in a normal SQL database, but I will keep it updated as I run into more barriers in the future.
What’s in this post
- No Primary Keys
- No Foreign Keys
- No Default Constraint using a Function
- No Multi-Line Inserts
- Cannot use sp_rename
- Summary
No Primary Keys
When you understand that the Dedicated SQL Pools (DSP) are meant to be used as Online Analytics Processing (OLAP) data warehouses for analytics and reporting purposes, it makes more sense that primary keys are not allowed on tables since that would affect performance. I did not initially put that concept together until I was trying to create a new table on the DSP and got the following error:
The full error message is:
Enforced unique constraints are not supported. To create an unenforced unique constraint you must include the NOT ENFORCED syntax as part of your statement.Unfortunately, there is no workaround for this limitation. You must accept that you will not be able to enforce uniqueness on your tables in the DSP, unless you want to have “uniqueness” in name only and not have it actually enforced.
No Foreign Keys
In the same vein as above, foreign keys are also not allowed in a Dedicated SQL Pool (DSP), since that would also hinder the performance of OLAP queries if they existed. When trying to create a foreign key on a table in a DSP, you get the following error:
The full error text is:
Parse error at line: 3, column: 1: Incorrect syntax near 'FOREIGN'.This error is different from the error received when trying to create a primary key, because this query simply returns a parsing error; the query processor doesn’t even understand the concept of trying to apply a foreign key constraint to the tables in this database, since it’s a DSP.
There is no workaround for this limitation beyond accepting that you will not be able to maintain relationships between tables in your database for this type of server.
No Default Constraint using a Function
This is the constraint I am most sad about not being able to apply to tables in a Dedicated SQL Pool (DSP), since I think it would be really useful for certain situations. But again, needing to apply a default value function for every row inserted into a DSP table would slow down the processing speed, which is bad for OLAP queries.
When you try to add a default constraint using a function or expression to a table in a DSP, you will get the following error:
The full error message for that is:
An expression cannot be used with a default constraint. Specify only constants for a default constraint.However, you are still allowed to add default constraints that have a constant value, like this:
ALTER TABLE dbo.CUSTOM_FORECAST_Raw
ADD CONSTRAINT DF_MyDefault
DEFAULT '1900-01-01 00:00:00' for UploadDatetime;
My most common use of a function in a default constraint for a column is to set an update datetime for the row, and that is precisely what I was trying to accomplish when I found this limitation. There is no direct workaround for this limitation. What I did to subvert it was to add the UpdateDatetime value to the column further upstream in the ETL process. Hopefully, that type of workaround will be doable for you as well.
No Multi-Line Inserts
This limitation is one I truly don’t understand, and I was really confused about it when I first ran into it during development. In a normal SQL Server database, you can insert multiple rows into a table at once using the syntax INSERT INTO dbo.MyTable VALUES (value1, value2...), (value3, value4...);. Running that command would insert two different rows into the table at the same time. That syntax is not allowed in Dedicated SQL Pools (DSP).
If you were to run the same command on a DSP, you would get this error:
The exact error message for that scenario is:
Parse error at line: 2, column: 19: Incorrect syntax near ','.This is another scenario where the query processor doesn’t even know how to approach this code, even though it would work just fine if I instead ran it on a normal SQL Server database.
The workaround for this limitation is to create an INSERT statement for every row of data you want to insert into your table, or use a pipeline in the Synapse workspace to load data instead.
INSERT INTO dbo.MyTable VALUES ('AR',5103);
INSERT INTO dbo.MyTable VALUES ('CA',6111);I only needed to load a few test rows into a table on my DSP for testing purposes, so the multiple INSERT statements worked in a pinch. I would not want to do that if I were adding more than a few rows to the table.
Cannot use sp_rename
For this scenario, I’m not entirely sure what the reasoning is behind removing the ability to run this particular procedure (and likely others like it), but I have learned that in a Dedicated SQL Pool (DSP), you are not able to rename objects in the database using the system stored procedure sp_rename. Instead, you now either need to just not rename your object, drop it and recreate it, or use a new command called rename object, which has a different syntax.
When you try to run the sp_rename procedure on your DSP, you will get an error like this:
The full error message is:
"An insufficient number of arguments were supplied for the procedure or function sp_rename"This is an odd message, because it leads you to believe you’ve typed something wrong instead of the fact that this procedure is not valid on this type of database. I’m not sure why the procedure is even installed on the database, given that it doesn’t work as it does on other databases.
When I first got that message, I wasted time going to the documentation and verifying that I did have the correct parameters for the procedure. The following message is displayed at the top of the documentation page, but that isn’t specific to any given T-SQL command that may not work, like the one being documented on the current page. Plus, the notice only warns for serverless SQL Pools and not dedicated SQL Pools, like what I am working with.
Mostly, when it comes to this system procedure not working, I am annoyed that, from the documentation of the procedure, it seems like it should work in this scenario. That includes the existence of the green checkmark next to “Azure Synapse Analytics” at the top of the page!
How to Rename Tables on a Dedicated SQL Pool
If the main system stored procedure for renaming objects doesn’t work for DSPs, how then are we supposed to rename objects as needed? Or are we out of luck and have to drop and recreate the table, or leave its name alone? At first glance, it does seem like we may be out of luck, because even the “Rename” option in the right-click menu on a table is grayed out and won’t let us change it from there:
There is a way to rename, it’s just a little harder to find! With this type of database, there is a new command rename object that will let you rename a table. The syntax for that looks like:
rename object MySchema.MyTable to NewTableName;That command worked successfully for me in my scenario of renaming a table that I wanted to mark for future deletion, as shown by this screenshot:
With further reading of the sp_rename procedure documentation, if you scroll all the way to the bottom part with Examples, there is another note that the sp_rename feature is currently only in “preview” for Azure Synapse Analytics and is therefore only available for objects in the dbo schema. And honestly, that’s a little silly to me. It’s also silly that you wouldn’t find out that information unless you looked for examples; the information was not up in the main section of the documentation, so it isn’t as easy to find.
If you would like to read further on the new syntax for renaming tables in Dedicated SQL Pools, you can review the document for the rename object command.
Summary
SQL Pools in Azure Synapse Analytics give you a lot of great performance and features that are specifically tuned for use in Data Warehouse situations, but those performance improvements do come with a cost–not being able to do things exactly as you are used to with standard SQL Server databases. I’ve struggled quite a bit with database development on this style of database, since it seems like every time I want to do something on the database, I hit a temporary wall of differences that I didn’t expect. I have obviously found an explanation for or a workaround to most of these problems, as evidenced by the information above, but it’s not a welcome interruption to my workflow. I hope that you will hit fewer walls in your own database development now that you’ve read through this post.
