The fact IDENTITIES and SEQUENCE Types are missing makes it very difficult to generate surrogate keys. It seems the only pattern available is to combine ROW_NUMBER() with a select max from table, which is horribly clunky and won't work if parallel treads are loading into the table.160 votes
IDENTITY property is now supported in SQL DW. Because this request includes information on SEQUENCE types, a new separate suggestion will be created for SEQUENCE types here: https://feedback.azure.com/forums/307516/suggestions/19746709.
See https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-identity for more information on IDENTITY
Please add support of JSON data format like just happened for SQL Server 2016 and Azure SQL Database with JSONVALUE, JSONQUERY, etc.133 votes
The support for new JSON TSQL functions are already available in the Azure Data Warehouse.
Storing dynamic SQL in NVARCHAR(4000) is not enough for our use case.10 votes
Thanks for the suggestion. We have completed the first version of this feature and it has been rolled out worldwide. The following data types are now supported, NVARCHAR, VARCHAR, VARBINARY and BINARY. There are a couple of limitations in this first version. (1) PolyBase loads to these new data types will not be supported. But you will be able to load this data through other means like bcp. (2) Cluster ColumnStore tables do no yet support these data types. These data types are supported in Heap and Cluter Index Tables. 1028371
If SET tables aren't available, at least allow an UNIQUE INDEX to be placed on a table if the DISTRIBUTION column is found in the columns of the UNIQUE INDEX
Table with locationid and productid with any number of fact columns. Partition the table by productid. All the data for any productid will be on one specific distribution. Allow that distribution to enforce uniqueness on locationid + productid.7 votes
Primary key and unique constraints are now supported by Azure Synapse SQL Analytics tables.
Columnstores can eliminate segments based on the min/max vaules of a column.
For best performance, the data would need to be sorted by the users desired column, such as a date.
I'd love to see a syntax that looks something like the below (but without the need for creating a clustered index first)
DISTRIBUTION = hash ( [store] ),
clustered columnstore index (order by DateStamp)
Ordered clustered Columnstore index is now supported by Azure Synapse SQL Analytics (formerly known as Azure SQL Data Warehouse).
Right now according to the documentation the only options for collation on SQL Data Warehouse are "The default collation is SQLLatin1GeneralCP1CIAS. The other collation option is Latin1General100CIASKS_WS."
We run Latin1GeneralCI_AS which I suspect a large amount of UK customers would do. This is a supported Collation as per SQL Azure which we are migrating our operational datastore to but we can't move our data warehouse because of this. Also like SQL Azure can we have this as an option via the management portal when we create a DW.4 votes
SQLDW introduced support for over 3800 collations a while ago. I am therefore now marking this suggestion as completed. Please excuse the tardy response to your feedback.
Please refer to the following post for more information:
Thanks for the feedback!
We are unable to see list of objects using SSMS (any version). This would be require to navigate through database objects.1 vote
Microsoft offers a free download of SQL Server Management Studio 2016 that offers full object explorer support for SQL Data Warehouse. You can download SSMS 2016 from here:
- Don't see your idea?