17

According to the documentation the only limitation on the number of rows stored per table is as below Limited only by available resources What local resources does this potentially include? Memory Space in the database data files to physically write the inserted data to Space in the database log files Space in TempDB (Data and log) As for how much a ...


7

This and constraints in general are currently unsupported in Azure SQL Data Warehouse. See https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-overview#unsupported-table-features for full list. If the constraint is not enforced at the source or if you have multiple sources, you can implement during the ETL or ELT process. For ...


6

Azure SQL Data Warehouse has limited support for UDFs. It does not yet support the syntax SELECT @var =. Instead you must use DECLARE @var int = or SET @var =. SQL DW UDFs also do not yet support queries on user tables. Please use our feedback page to vote for new features.


6

Functions in Azure DW don't support select statements that access tables like in your use case, see CREATE FUNCTION (SQL Data Warehouse): function_body Specifies that a series of Transact-SQL statements, which do not reference database data (tables or views), define the value of the function. Could you double check that function is created in DW?


6

Polybase is powerful but not really that sophisticated so will not be able to deal with this weird format. You have three options as I see it: Correct the file format at source. Instead of having weird mix of fixed-width and delimited file format, use a standard file format, such as .csv. This format you have where the column delimiter is either carriage ...


5

Try using sys.dm_db_partition_stats instead of sys.partitions, as in: SELECT ObjectName = QUOTENAME(sc.name) + '.' + QUOTENAME(t.name) , RangeValue = rv.value , sys_partitions_RowCount = p.rows , sys_dm_db_partition_stats_row_count = ddps.row_count FROM sys.tables t INNER JOIN sys.schemas sc ON t.schema_id = sc.schema_id INNER JOIN sys....


4

When provisioning users from external Azure Active Directory instances that are federated with your Azure subscription, you need to use the underlying "guest" email address created for the Azure subscription, not the "actual" email address. i.e. The Microsoft scenario mentioned here as "Imported members from other Azure AD’s who are native or federated ...


4

If you look at the list of Unsupported Table Features for Azure SQL Data Warehouse, unique constraints is still on that list as at today: It may be that they get supported at some point in the future but I wouldn't hold your breath for this MPP system. As a workaround, get your ELT/ETL process to ensure records are unique during ingest for example, or make ...


4

Generally speaking, merge join (including merge join concatenation) only preserves the sort order of the join keys. The merge join keys are client, campaign, orderId. The required input sort order for the window function is client, orderId, campaign, id , datestamp. The merge join cannot therefore provide the sort order required by your window function. ...


4

(IMHO) Azure SQL Data Warehouse is a Massively Parallel Processing (MPP) engine with a shared nothing architecture designed to handle hundreds of terabytes of data. It does this by dividing your data (either by a distribution key you specify or round robin algorithm) across 60 distributions split across n nodes, depending on the performance tier you are at. ...


3

There does not appear to be a row limit except as it relates to resources. If your SQL Server truly have enough resources to insert half a billion rows then it will do it. In the past, I have been faced with such resource constraints, so I would add a WHERE clause that limited the resultset. Or I would run the INSERT recursively with a TOP on the select. ...


3

According to a hex editor, your sample file has single line feed (0A) for some line endings and two line feeds as the delimiter between rows: A U-SQL custom extractor might be able to handle this file, but I wonder if we will run into issues with the full 30GB file. Instructions Set up an Azure Data Lake Analytics (ADLA) account if you don't already have ...


3

Update Jul 2019 - as at July 2019, Azure SQL Data Warehouse now supports materialized views. The engine is implemented somewhat differently in SQL Data Warehouse so it does not support the feature Partitioned Views as SQL Server does. For example CHECK constraints are required to enable these to work correctly, but CHECK constraints are not supported in ...


3

The documentation of the CREATE TABLE syntax for Azure SQL Data Warehouse does not mention CHECK constraints at all, nor any other type of constraints except for NOT NULL and DEFAULT constraints. I assume that CHECK constraints are not supported in this product. Simple talk article: Taking Azure SQL Data Warehouse for a Test-Drive by Robert Sheldon, says ...


3

There is an Extended Event that fires when a columnstore group is being 'cut', I think the event is column_store_index_build_process_segment. This event will have a 'trim' reason, and you should look for two possible trim causes: memory (unlikely for your case), if the index build does not have enough memory to build a segment. dictionary size (the likely ...


2

For Azure SQL Data Warehouse, unless you have have millions of records, then it's unlikely you will really benefit from clustered columnstore indexes. In fact this could even be detrimental to performance. For smaller volume tables, you would probably be better off with either heap (for fast load) or clustered index. ROUND_ROBIN will at least ensure even ...


2

I ended up using sed to cleanse the file zcat myfile.txt.gz | sed -r 's/[ ]+/vin/g'|tr -d '\n'|tr 'vinvin' '\n'|grep -v '^$' > myfile.txt This took care of formatting issues with the source files. Once these files were uploaded to Azure blob storage, the rest was easy. I created external table pointing to files on blob via Polybase and then created ...


2

Traditional SQL Server query plan is not available yet. You can use DBCC PWD_SHOWEXECUTIONPLAN to see estimated execution plan. For actual execution plan you can get a D-SQL plan using Visual Studio. Grant Fritchey explains in details.


2

I had the same problem and came across this gem. Found in this article: https://www.red-gate.com/simple-talk/sql/bi/azure-sql-data-warehouse-explaining-architecture-system-views/ SELECT pnp.partition_number,t.name,nps.[row_count],nps.[used_page_count]*8.0/1024 as usedSpaceMB,nt.distribution_id FROM sys.tables t INNER JOIN sys.indexes i ON t.[...


2

There is a Data Warehouse Migration utility (available here) for Azure SQL Data Warehouse which picks up issues like incompatible datatypes, in-line functions, use of hints, use of RETURN statement, INSERT ... EXEC and many others: Unfortunately it does not pick up scalar functions which reference tables and really it should. However, for your particular ...


2

In general terms we use a join hint is to correct a failure of the optimizer to identify a better query plan. This can happen for many reasons which broadly boil down to optimization being a "good enough" approach constrained by a time budget for finding a plan. Reduce "also called a semi-join hint". With a semi-join we are only ...


2

Round robin always entails data movement because of its nature, but it needn't be catastrophic for your performance. The reason you use it for skewed data is because, when you distribute by hash, a unique hash is generated for each value and the rows distributed across the 60 distributions accordingly. In your example, most of your data would end up on ...


2

Running explain on the query showed that something underneath was setting MAXDOP to 1. Forcing the query to use 0 overcame this, as columnstores require DOP >= 2 Therefore adding the below hint enables batch mode OPTION (MAXDOP 0) I don't understand why this is happening - my understanding is that CTAS and building a new columnstore should always ...


1

If you check out the documentation for the USE statement, you'll see that it is not supported for Azure Synapse Analytics (formerly Azure SQL DW). To change database contexts, you'll have to open an explicit connection to the desired database. If you're having issues with your IDE, try specifying the desired database in your connection string when you ...


1

Polybase does not have this ability. The source file and external table definition must match for it to work. Simply add or remove additional column(s) in your external table definition. Then when you are physically creating your table in the database eg via CTAS, add/remove the additional column(s) from your CTAS statement. Alternately, consider the new ...


1

I don't believe it's possible, or useful to CREATE MASTER KEY in the master database in Azure SQL Database/Azure Synapse Analytics (formerly SQL Data Warehouse). And the error you're seeing is a bit misleading, as you get that error whenever you attempt an operation in master that is allowed in normal SQL Server, but prohibited in Azure SQL Database. The ...


1

Are you getting this info from an EXPLAIN plan or the new SSMS plan functionality for Azure SQL Data Warehouse? Please provide. In the "box product" engine (SQL Server 2017 14.0.3008.27), I can reproduce converting a many-to-many MERGE join to a one-to-many using the DISTINCT keyword against a columnstore table. I can't guarantee this would behave the ...


1

Azure SQL Data Warehouse does not support check constraints so partition switching is a slightly different ballgame. The partition scheme as described in the table DDL (rather than a partition schema/function pair) must match exactly in both tables. Code example duplicated from here: -- Assume we have a file with the values 1 to 100 in it. -- Create an ...


1

Ref: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-develop-best-practices-transactions The transaction safety limits only apply to fully logged operations. CTAS is capable of being minimally logged. So answer depends, if CTAS is minimally logged safety limit does not apply and vice versa. Unlike fully logged operations, ...


1

This came up the other day and the reply was "switch on Auditing": https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-auditing-overview I have not tried this myself yet so I'm not sure if you get the full text of the query. I plan to test this feature in the next few weeks so will update the post, or you can : ) I have been ...


Only top voted, non community-wiki answers of a minimum length are eligible