Questions tagged [azure-sql-data-warehouse]

For questions about Microsoft Azure SQL Data Warehouse, the cloud-based, massively parallel processing database for relational and non-relational data.

Filter by
Sorted by
Tagged with
8
votes
0answers
177 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 ...
1
vote
1answer
282 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 ...
5
votes
2answers
1k 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
vote
1answer
179 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 ...
2
votes
1answer
296 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 ...
-1
votes
1answer
404 views

How to copy data from Azure SQL DB into Azure SQL DW

I need to copy some data from an azure SQl DB instance into my azure data warehouse. The most obvious way to do this is to use Elastic Query, similar to the below: CREATE EXTERNAL DATA SOURCE SqlDb ...
1
vote
1answer
352 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
vote
1answer
166 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 ...
1
vote
2answers
153 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
vote
1answer
138 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 ...
0
votes
0answers
34 views

Are clustered indexes in Azure data warehouse ever a good idea on large tables?

Azure data warehouse supports both clustered and non-clustered indexes in addition to columnstore (which is the default for any new table). I know that having a large clustering key is normally ...
1
vote
1answer
31 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
vote
1answer
38 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
vote
0answers
101 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 ...
2
votes
0answers
80 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....
4
votes
1answer
108 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 ...
3
votes
1answer
385 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 ...
1
vote
1answer
435 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 ...
1
vote
1answer
1k 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 ...
1
vote
1answer
133 views

Clustered columnstore indexes - Do I need to create a non clustered index on a column that's used in a join? [closed]

I have two very large tables that need to be joined to produce a data extract. Both tables have clustered column store indexes and are partitioned. DW Sentry advises me in the plan view that it ...
2
votes
1answer
378 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 ...
5
votes
3answers
3k 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
vote
1answer
586 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 ...
0
votes
1answer
235 views

Azure SQL data warehouse - do transaction limits apply to CTAS opperations?

Azure SQL Data Warehouse has limits on the maximum size of a transaction (as documented here). As CTAS operations cannot be performed inside a transaction, I was wondering if these limits still ...
2
votes
3answers
3k 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
vote
1answer
180 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 ...
3
votes
2answers
221 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 ...
3
votes
1answer
228 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 ...
5
votes
3answers
1k 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
votes
2answers
6k 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 ...
4
votes
2answers
757 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, ...
7
votes
3answers
3k 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 ...