Azure Synapse Analytics
-
[In Preview] MERGE statement support
Support the MERGE sql statement to support management of SCD1 / SCD2
488 votesThis feature is in private preview and will be available to all users in coming months.
-
[In preview] Rename a column name in SQLDW
This request is currently in Preview for columns in user tables.
See release notes:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/release-notes-10-0-10106-0#dec-2020
See sp_rename:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql?view=sqlallproducts-allversionsScenario: 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…167 votesThis feature is in Preview for columns in user tables.
-
@@ROWCOUNT Support
Please enable @@ROWCOUNT?
Furthermore considering that we cannot switch NOCOUNT on, then why should it be such a secret/coded mission to track the number of rows affected by the most recent snippet of code? The proposed work-around seems onerous and doesn't always work (for dynamic SQL) yet my query window still knows and always-prints the fact that it returned 15 rows to me, for example, so why can we not leverage this elementary information inside the selfsame query (?):
SELECT SUM(rowcount) AS rowcount
FROM sys.dmpdwsqlrequests
WHERE rowcount <> -1
AND request_id IN
…( SELECT
130 votes -
105 votes
-
DROP IF EXISTS
Support DROP IF EXISTS
78 votes -
[In preview] Inline and Table Valued Function support
This request is currently in Preview:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-sql-data-warehouse?view=sqlallproducts-allversionsCurrently 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.
70 votesInline Table Valued Functions are currently in Preview. Will be supported in tooling in coming months.
-
Add WAITFOR command to SQL Datawarehouse
The following wont compile on ADW
IF @count = 4
BEGIN
WAITFOR DELAY '00:30';
SET @count = 0;
ENDPlease allow WAITFOR command on ADW
55 votesThank you for the feedback. We are reviewing the request.
-
36 votes
Thanks for your suggestion. We are looking into this scenario for a future release. 10703657
-
Allow DBCC results (e.g. DBCC PDW_SHOWSPACEUSED..) to be copied into yet another table for programmatic interaction with those results?
IF OBJECTID('tempdb..#showspace_used') IS NOT NULL
DROP TABLE #show_space_used
CREATE TABLE #show_space_used
(
[ROWS] INT NULL
, [RESERVED_SPACE] INT NULL
, [DATA_SPACE] INT NULL
, [INDEX_SPACE] INT NULL
, [UNUSED_SPACE] INT NULL
, [PDW_NODE_ID] INT NULL
, [DISTRIBUTION_ID] INT NULL
)
WITH
(
DISTRIBUTION = HASH([ROWS])
, HEAP
)DECLARE @DynamicSQL nvarchar(4000)
SET @DynamicSQL = (SELECT CONVERT(NVARCHAR(100),'DBCC PDW_SHOWSPACEUSED("dbo.TableName");'))INSERT INTO #showspaceused ([ROWS], [RESERVEDSPACE], [DATASPACE], [INDEXSPACE], [UNUSEDSPACE], [PDWNODEID], [DISTRIBUTIONID])
EXEC spexecutesql @Dynamic_SQLMsg 103010, Level 16, State 1, Line 326
Parse error at line: 25, column: 1: Incorrect…29 votesThank you for your request. This item is on our backlog. We will update this thread when the status changes.
-
column default
Support column default with expression. (E.g. Getdate())
25 votes -
Add "DROP Partition" command
Having drop Partition command that automatically merges the partition boundaries and removes the partition would help a lot.
24 votesThank you for your request. This item is on our backlog. We will update this thread when the state changes.
-
Add support for the truncate partition option
Add support for the option to truncate a single partition in a table (as per sql azure and sql 2016).
https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql
18 votesThank you for your request. This item is on our backlog. We will update once the item state changes.
-
Remove Scalar Function limitiations
Currently we can not do the following:
use Global variables within UDF, which makes us to send it from the front end as a value. eg; @@DateFirst
reference tables within the UDF
reference temp tables within a UDF18 votesThanks for your suggestion. We are looking into this scenario for a future release. 10703219
-
Support of iif/choose logical function
Azure SQL DW does not support IIF and CHOOSE logical function while SQL Server, SQL DB and SQL MI support it. It would be good to support them to reduce the gap between SQL services.
15 votes -
Allow results of a Stored procedure call to be stored to a table.
APS PDW on-Prem... I'm having to create a permanent table with session Id as part of the key to be able to pass record sets back and forth between stored procedure calls. I'd like to at least be able to declare a table variable, and populate it with the results of a stored procedure call.
13 votes -
sp_help
Support sp_help
13 votesPlease help describe the scenario for this.
-
Feature request: cursors
Will cusors (https://msdn.microsoft.com/en-us/library/ms180169.aspx) be available anytime in future in Azure SQL Data Warehouse?
13 votesThanks for the request. This item is currently on our backlog. We will update this thread when the state changes.
-
View the Dependencies of a Stored Procedure
In summary, sys.sqlexpressiondependencies doesn’t return the referenced objects of stored procedure. This works for SQL Server and in the online documentation it is likely mentioned as supported scenario. Unfortunately, it is not supported at the moment. Please add this functionality.
12 votes -
Allow extended properties in Synapse
Allow the usage of extended properties in Synapse (cfr. the extended properties in SQL DB)
11 votes -
Not to be locked at CREATE / DROP USER
If you execute CREATE / DROP USER during updating query execution, a wait occurs when trying to acquire an exclusive lock.
11 votesThanks for your suggestion. We are looking into this scenario for a future release. 10698260
- Don't see your idea?