Azure Synapse Analytics
-
[In Preview] MERGE statement support
Support the MERGE sql statement to support management of SCD1 / SCD2
438 votesThis feature is in private preview and will be available to all users in coming months.
-
[In preview] PolyBase: allow escaping string delimiters within string fields
This feedback item is currently in preview here:
https://docs.microsoft.com/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest
If you want to quote the string columns, and if one of the quoted columns has a quote character in it, the proper way to encode this is to double the quote character. (Build a CSV file in Excel and you will see that's how it saves it. I would say Excel is the authority on CSV formats.)
it appears it's impossible for Polybase to properly load that file. For example the following format is not interpreted right with STRING_DELIMITER = '"' since it divides the value 2.5" into two columns
…
247 votes -
[In preview] Polybase: allow field/row terminators within string fields
This feedback item is currently in preview here:
https://docs.microsoft.com/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest
If a line end is present within a text field (qualified) importing data using Polybase will fail.
Loading this line will work fine:
1;2.1;"zzzz"Loading this will fail:
1;2.1;"zz
zz"Code:
CREATE EXTERNAL FILE FORMAT textfileformat_raw
WITH
(FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS
(
FIELD_TERMINATOR =';',
STRING_DELIMITER = '0x22',
USE_TYPE_DEFAULT = TRUE
));
CREATE EXTERNAL TABLE dbo.testexternal
(
col1 INT,
col2 DECIMAL(2,1),
col3 NVARCHAR(5)
)
WITH
(
LOCATION = 'testfailing.txt',
DATASOURCE = azurestorage,
FILEFORMAT = textfileformatraw
);CREATE TABLE dbo.test
WITH…232 votes -
Rename a column name in SQLDW
Scenario: We want to change the column name as it is changed in the source. And in table where we are trying to do has more than 20Billion records.
Workaround: Since rename of column is not possible currently in SQL DW we are doing CTAS of that big table.
Issues With Work around: It is very slow because we can’t do CTAS on entire big table in single transaction because of transaction limits and we end up doing in CTAS and with multiple insert statements . Which is very time consuming process
ASK: Please support column Rename142 votes -
[In Preview] Hash distribute fact table on multiple columns
A large fact table often has multiple join keys to multiple dimension table, so while it can be opitmised for one join it always creates data movement for other joins. The hash distribution should accept multiple columns so all joins are optimised and create no data movement for dimension joins.
98 votesThis feature is currently in preview and will be available to all users in coming months.
-
Resource Governance - Resource Pools - Control CPU, physical IO, memory, priority, run-time cap, max request, concurrency, request timeout..
- Ability to manage workloads effectively
- Enables to specify limits on the amount of CPU, physical IO and memory
- User-Defined Resource Pools
a. Memory size
b. Memory cap
c. CPU
d. Maximum requests
e. Grant time-out
f. Priority
g. Concurrency
h. Run-time cap
64 votesThis is available for public preview as announced at Ignite this November, 2019.
-
Inline and Table Valued Function support
Currently Functions can only return a single variable, but we need to be able to return full tables so we can use them within the WHERE clause.
63 votesThe support for inline Table Valued Function will be available in coming months.
-
[In preview] Load a subset of data - currently you can either point to a source file or a source folder
This feedback item is currently in preview here:
https://docs.microsoft.com/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest
A few ways this can be done: defining a regular expression or wild card support
58 votes -
Automatic Generation and Updating of table statistic
It would be great if table statistics were automatically created and updated in Azure Data Warehouse.
56 votesWorking on now. Should be out in the next few months!
-
[In preview] Import files via single COPY command from blob store.
This feedback item is currently in preview here:
https://docs.microsoft.com/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest
It shouldn't require 5+ unintelligible queries just to import a file from blob store. Redshift lets me import files (including zipped files) from S3 with a single COPY command.
44 votes -
Support for Large Objects in External Tables
While you can read and write to blob storage with large objects like varchar(max) now you can't read them via external tables.
E.g. If you define a field with varchar(max) in an external table you can't use it you need to use BCP or SSIS to get it into a physical table that is a heap or supported indexed table.
Msg 102048, Level 16, State 1, Line 56
Columns with large object types are not supported in external tables.38 votesSorry for the confusion, there was a regression during deployment in the Large Object support that caused a rollback of the functionality. We are actively working on getting a fix.
-
Resource class override
Having a fixed resource class assigned to a user is too rigid. Most queries can be executed efficiently with a small resource class, but certain operations (e.g. rebuild index) should be run with a larger RC. I need to switch between several user accounts just to be able to have appropriate resources for whatever operation or query I need to run. Users assigned to xlargerc can only execute a single query at a time.
Would be great to be able to set/change the resource class used for executing each statement. Alternatively, being able to execute as a different user could…
38 votesWe announced workload isolation via workload groups for public preview at Ignite in Nov., 2019. Workload groups allow you to create your own custom resource classes (among other things). Check out workload classification that allows you to assign requests by more than just login information too!
-
Support EXECUTE AS for stored procedures
As part of the ELT procedure the user staging data should be able to call a transform procedure to ingest the data without having read/write permissions to the production tables.
29 votesThanks for your feedback. Azure SQL Data Warehouse Support for EXECUTE AS feature is now in progress.
-
Better Database & Query Monitoring through Azure Portal
Enable a better experience when it comes to monitoring and analysis of queries and DWH/query performance. Currently the SQL DW blade in the Azure Portal does only show very basic information about queries and their execution as well as DTUs with no possibility to customize the charts or the (query) lists. Only the last 50 queries are shown in this list, there are no overall reports and statistics (long running queries), there is no possibility to filter this list or show other metrics. Please enable a similar experience for monitoring of query and DB performance like SQL DB offers (Query…
27 votesWe are actively improving our monitoring experience. We have future improvements on our road map such as Query Data Store and integrating with Azure Monitor (logs and metrics) for near real time troubleshooting in the Azure portal. If anyone has any other feedback, please elaborate on your scenario on this thread and have your team continue to vote. Thank you for your continued support!
-
Allow CosmosDB linked data to be queried from SQL on-demand
It would be really awesome to be able to query CosmosDB linked data using SQL on-demand! (too bad this isn't aready possible at this moment with synapse-link in preview).
Please add this feature.
9 votesAccessing the Azure Cosmos DB analytic store with Synapse SQL serverless is currently under gated preview. To request access, email cosmosdbsynapselink@microsoft.com
More info at – https://docs.microsoft.com/en-us/azure/cosmos-db/synapse-link#limitations
-
Assign a private IP from VNET
The current VNET endpoint solution does not allow connections via expressroute.
Allow a private IP from the VNET to be assigned to the data warehouse, so that we can easily route to the warehouse from on prem, via expressroute. Removing the need for any complex peering or other IT infrastructure involvement.
9 votes -
Support "DATEDIFF_BIG", like Azure SQL
pretty useful for device data (IoT), often has in sub seconds
9 votesWe have started on the request for DATEDIFF_BIG support.
-
7 votes
We announced some of these capabilities for preview at Ignite this Nov., 2019. Workload groups allow for reserving and capping resources. You can also configure a query timeout to cancel a query.
-
UPDATE FROM Support
Updates/Deletes do not support FROM clause. To update a table with values from another we need to create temporary tables (CTAS) and run loops.
6 votes -
Support the sp_describe_undeclared_parameters system stored procedure
SSIS scripts often use this system stored procedure to investigate parameter types.
5 votesThanks for the suggestion. We have started work on this request. 11648884
- Don't see your idea?