Questions tagged [azure-synapse-analytics]
For questions about Azure Synapse (formerly Azure SQL Data Warehouse). It is an analytics service that brings together enterprise data warehousing and Big Data analytics.
78
questions
8
votes
5
answers
9k
views
sys.partitions row count is badly wrong - how to correct this?
Querying sys.partitions can return an approximate row count for a table.
I've noticed that this is returning the same row count for all partitions, regardless of the actual content (even for empty ...
- 1,244
8
votes
1
answer
637
views
What cases benefit from the Reduce, Replicate, and Redistribute join hints?
The From Clause Documentation starting with SQL Server 2008 briefly mention 3 join hints and their basic mechanisms:
Reduce
Replicate
Redistribute
However there does not seem to be much information ...
- 2,078
7
votes
3
answers
6k
views
Azure Data warehouse - User defined function issues
Anyone here had luck with creating and using UDFs on Azure data warehouse database? I am in the middle of migrating an on-prem warehouse from SQL Server 2014 to Azure datawarehouse and I ran into an ...
- 2,427
5
votes
2
answers
4k
views
How many rows can SQL Server process in a single INSERT statement into a table?
To illustrate my question, the following is a query detecting order's id which have not been inserted in a data warehouse and inserts them:
With NewOrders
As
(
Select OrderID From Orders
...
- 1,451
5
votes
3
answers
2k
views
Azure SQL Warehouse - Data Ingestion - Convert a huge fixed width (with commas) file to delimited
I am not even sure if I am framing this question right, but I will try - I have a bunch of huge text files generated from an Oracle export on a Linux system. Each file is about 30 GB in size, and I ...
- 2,427
5
votes
1
answer
718
views
User can drop table from a schema, but cannot create one
In an Azure Synapse dedicated SQL pool I have the following setup:
-- a custom DB role to manage privileges
CREATE ROLE [owner];
-- there is a schema owned by this role
CREATE SCHEMA [myschema] ...
- 31k
5
votes
2
answers
17k
views
Add Active Directory User For Azure-SQL-DB
I have an active directory user LDomain\LUser and I want that user to be able to connect to Azure-Sql-DB. The syntax MS uses is throwing an error.
T-SQL:
CREATE USER [LDomain\LUser] FROM EXTERNAL ...
- 141
4
votes
1
answer
150
views
Does Azure SQL DW support partitioned views?
I've got a really expensive join in data warehouse that isn't distribution aligned. Unfortunately the join key is nullable, and only half the data has a value, meaning that it wouldn't be a good ...
- 1,244
4
votes
2
answers
1k
views
How Can I Measure How long a query takes on Azure SQL Data Warehouse
In traditional SQL Server I can get CPU and Elapsed Time by setting the following
set statistics time on
When I try that on Azure SQL Data Warehouse I get the following error
Msg 103010, Level 16, ...
- 143
4
votes
1
answer
618
views
Revision Tracking & Source Control for Azure SQL Data Warehouse
What is a good approaching for tracking incremental changes to database tables, stored procedures, etc for Azure SQL Data Warehouse?
I am in the process of moving a large database over to Azure SQL ...
- 149
3
votes
2
answers
271
views
Best practice Azure Datawarehouse for less data-movements
I have a Table with a HASH on a Employee_Key, it also Hold a Date_From and Date_To.
To build the facts I need to change it on a by_date column.
But what is the best practice in Azure DataWarehousing ...
- 139
3
votes
1
answer
790
views
How can I obtain the full text for a query that appears in sys.dm_pdw_exec_requests?
I'm trying to tune a DB in Azure SQL Datawarehouse for queries produced by Power BI, which can connect directly to Azure SQL Data warehouse. I'm interested in studying execution plans, tweak things ...
3
votes
1
answer
1k
views
How are deadlocks detected by Synapse
I'm trying to produce a deadlock in Azure Synapse. I would have expected it to work much as on-prem SQL Server does given the documentation says it "applies to" Synapse. I've waited an ...
- 24.7k
2
votes
3
answers
4k
views
How to add a check constraint in sql azure data warehouse?
I'd like to add a check constraint to a table in azure data warehouse.
I have the below script:
alter table [FactTbl_Test] add constraint Ck_Test_id check ([Test_Id] != 'Unknown')
This causes the ...
- 1,244
2
votes
3
answers
2k
views
How to run legacy SSIS packages with Azure Synapse
I would like to know how to run an SSIS package from Azure Synapse Studio or whether it's at all possible. Apparently there is no support for SSIS runtime in Synapse but I suppose there should be a ...
- 121
2
votes
1
answer
491
views
Difference between Clustered Columnstore Index Table in Azure SQL and Table in Azure Data Warehouse
What is the essential difference between a Clustered Columnstore Index table in Regular Azure SQL, and a table in Azure Data Warehouse?
They both have columnar storage, no foreign keys, no primary ...
user161709
2
votes
1
answer
313
views
Why does my columnstore index not fill the entire rowgroup?
I'm using [vColumnstoreDensity] to monitor the health of my columnstore indexes.
I've noticed that I've got one index that looks strange.
As far as my understanding goes, the columnstore index will ...
- 1,244
2
votes
1
answer
531
views
Azure Sql data warehouse - is there any way to see the true query plan?
Azure SQL DWH / PDW have the EXPLAIN keyword to show the parallel query plan. This is really useful for seeing the data movement operations, however I was wondering if there was an equivalent of the ...
- 1,244
2
votes
1
answer
929
views
How to ignore columns in parquet/polybase import?
I'm using polybase to import a parquet file.
Over time, it is likely we may add or remove named columns in the file.
When I add an additional column, I get the below error:
External file access ...
- 1,244
2
votes
2
answers
757
views
Lock a table for a CTAS upsert in Azure SQL DataWarehouse
I have a type 2 dimension in Azure SQL Data Warehouse.
Essentially I am creating an interim table with
CREATE TABLE myDimension_temp
AS
SELECT
...
FROM myStagingTable;
etc
After the CTAS is ...
- 21
2
votes
1
answer
1k
views
Azure Synapse SQL Pool: Issue in MERGE INSERT UPDATE statement
I have a table in Azure Synapse Dedicated SQL POOL.
CREATE TABLE table_A
(
ID int IDENTITY(1,1),
ClientID varchar(10),
fitrstname varchar(20),
lastname varchar(30),
phone ...
- 131
2
votes
0
answers
211
views
Exactly-once FIFO queue in Synapse
Creating a queue table in SQL Server is a much-studied problem. However, I would like to implement one in Azure Synapse where many of the building blocks do not exists. Specifically
no table hints (...
- 24.7k
2
votes
0
answers
117
views
Why does outer apply cause a broadcast move?
I've got an outer apply with a condition on the distribution keys.
select e.a
,e.b
,p1.c
from e
outer apply
(
select top 1
p.DateStamp
from p
where e....
- 1,244
1
vote
3
answers
1k
views
What are the limitations in Synapse Analytics compared to normal SQL Server?
I'm keen to get a list of the feature limitations or suprises when using Synapse Analytics (a.k.a. Dedicated SQL Pool, used to be known as SQL DW / SQL Data Warehouse) compared to using normal SQL ...
- 628
1
vote
2
answers
744
views
Why does a many to one merge join change the sort order of the data set?
I have a query that calculates row_Number(). The table has a clustered index on the same columns (and order) as the partitioning and ordering for the row number.
When using a merge join (many to one),...
- 1,244
1
vote
1
answer
3k
views
I'm getting the below error while trying to ADD CONSTRAINT in Azure DataWarehouse
Msg 104467, Level 16, State 1, Line 1
Enforced unique constraints are not supported in Azure SQL Data Warehouse. To create an unenforced unique constraint you must include the NOT ENFORCED syntax as ...
- 19
1
vote
2
answers
339
views
SQL Server tempdb files growth increment size
In our Azure SQL Managed Instance, we have 12 tempdb data files each of size 11GB. The Log file growth value is set to a max default size of 2TB. The growth value of each data file is 32GB.
Question: ...
- 289
1
vote
1
answer
1k
views
Why is a round robin distribution better for a skewed dataset?
I have a skewed dataset, where most rows fall into the largest 10 values of my best candidate distribution key. My data is made up of two large tables, that only share two keys - my best candiate key, ...
- 1,244
1
vote
1
answer
55
views
select runs in batch mode, but CTAS runs in row mode
I have the same query that shows as running in batch mode when run as a select statement, but row mode when the same query is the select part of a CTAS operation. Both tables (plus the resulting table)...
- 1,244
1
vote
1
answer
926
views
SQL Data Warehouse - Is it possible to switch a whole table to a partition?
Probably a stupid question, but I wanted to know if it was possible to switch a whole table into a partition of a larger table?
I'm guessing not, as check constraints aren't available to constrain ...
- 1,244
1
vote
1
answer
230
views
How to retain same FLOAT value after converting it to a VARCHAR in Azure Synapse
Consider the following example:
DROP TABLE dbo.TEST_FLOAT;
CREATE TABLE dbo.TEST_FLOAT (
AMOUNT FLOAT
);
INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (11546.25);
SELECT AMOUNT, CAST(AMOUNT AS ...
- 69
1
vote
2
answers
4k
views
How to get queries executed in Synapse SQL?
I need to find all the query executed on my DB in last 1Hr in Synapse workspace with SQL pool. Will apply my logic on top of the result set.
What's the right table/view to look for?
sys....
- 131
1
vote
1
answer
818
views
How to prevent Dropping of tables but allow Creating of them, within a Schema
Is it possible to, within a specific Schema, allow a user to create a table, and select from it, but not allow them to Delete or update rows after initial creation, and also prevent them from dropping ...
- 137
1
vote
1
answer
199
views
How to achive a many to one merge join in Azure Data Warehouse
I have a merge join in azure data warehouse. My estimated execution plan currently shows it as a many to many join. I'd like to know if it is possible to achieve a many to one join. Currently I'm ...
- 1,244
1
vote
0
answers
24
views
Schema Drift & Delta Table update - cannot resolve `target.new_column` in UPDATE clause given columns [list of columns]
Using Azure Synapse Analytics, I have configured a pipeline and a Mapping Data Flow that were working great until I recently added a Derived Column to my Mapping Data Flow, which created a column that ...
- 11
1
vote
1
answer
108
views
Azure Dedicated SQL Pool - How to checksum a table?
We're moving all our on premise tables into the azure dedicated sql pool. We're using synapse workspace pipelines to import the data into the dedicated pool.
Is there a way to checksum a table to ...
- 11
1
vote
0
answers
183
views
How do I change the database owner in Synapse Analytics Serverless SQL Pool?
I am working with Azure Synapse Serverless SQL Pool. A former employee created the databases in the pool, and is listed as their owner. Consequently, I cannot drop the login.
I would like to change ...
- 111
1
vote
0
answers
235
views
Synapse Dedicated SQL Pool: temporary CREATE TABLE failed despite number of columns are well below the maximum limit
I am performing an UPSERT operation in a Synapse Dedicated SQL Pool.
The TARGET table has 394 column and I am trying to update the TARGET with 5 Unique Keys.
MERGE table_A AS TARGET using
(SELECT &...
- 131
1
vote
0
answers
19
views
Getting insight into partitions of a table in Azure Synapse (similar to SQL Server) [duplicate]
I'm using Dedicated SQL-Pools AKA Azure Synapse (which is different from serverless/on-demand Synapse that comes with Azure Synapse Analytics). And According to Azure:
While the syntax of ...
- 149
1
vote
0
answers
15
views
Resource overlap between Synapse databases
I have an Azure Synapse dedicated pool with a single database. It is scaled to DW500c which the documentation says means it has a single compute node.
If I add a second database how does work against ...
- 24.7k
1
vote
0
answers
36
views
Implementing data warehouse on the cloud [closed]
I need to implement a data warehouse on the cloud for a mid-sized company. The database size will be around 200 Gb.
My options are (so far):
AWS with its database Redshift
Azure with its database ...
- 51
1
vote
0
answers
58
views
Shrinking 150 TB data warehouse instance is feasible option?
We are using azure SQL data warehouse(azure synapse analytics) with size of 150 TB among which 75 TB is used and we are planning to shrink database with help of MS. What are the challenges should we ...
1
vote
0
answers
48
views
how to identify cross login in azure synapse analytics?
In my environment user are logged in to SQL server via MFA authentication with their email id.
Its possible for single user to log in to multiple laptops with same credentials.
Can we track user ...
1
vote
1
answer
1k
views
How to see what's using TempDb space in Azure SQL DW?
I'm having problems with TempDb being full. Are there any system views I can see to diagnose what's using up TempDb and/or how much space is still available on it?
It would be awesome to also see ...
- 1,244
1
vote
1
answer
454
views
Copy real time data from Azure SQL into Azure Data Warehouse
I would like to copy our data from Azure SQL (OLTP) into Azure Datawarehouse. I want the path to be close to real time.
The Microsoft system looks it takes two steps copying data from SQL Server into ...
user162174
1
vote
1
answer
235
views
How to tell if Azure SQL DW is using a secondary index?
Using Azure SQL DW, I have created a secondary index on a single column in a table, yet I'm unsure if the index is ever being used by my query. The performance is still slow, but I'm searching about 7 ...
- 133
1
vote
1
answer
50
views
How to index cube style queries with changing aggrigations
I've got a query that emulates the CUBE function.
This is many different aggregations unioned together.
select x,y,z, sum(a) from tbl group by x,y,z
union all
select x,'all',z, sum(a) from tbl group ...
- 1,244
1
vote
0
answers
278
views
Output result of DBCC PDW_SHOWEXECUTIONPLAN into a variable
I would like to output the results of DBCC PDW_SHOWEXECUTIONPLAN into a variable (or table, I'm not fussy!).
I know the PDW syntax is quite different when it comes to outputing results, which means ...
- 1,244