Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link: http://hub-nl.insight.com/i/692679

Contents of this Issue

Navigation

Page 203 of 212

191 C H A P T E R 9 | Introducing Azure SQL Data Warehouse the INSERT INTO … SELECT FROM … statement. In both cases, you use the ROW_NUMBER function to generate a surrogate key since SQL Data Warehouse does not support identity columns. Example 9-8: Loading a SQL Data Warehouse table from an external table -- Option 1: Load a new table CREATE TABLE dbo.DimSalesTerritory2 WITH ( CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = ROUND_ROBIN ) AS SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS SalesTerritoryKey, SalesTerritoryAlternateKey, SalesTerritoryRegion, SalesTerritoryGroup, SalesTerritoryCountry FROM dbo.DimSalesTerritoryExternal -- Option 2: Load an existing table INSERT INTO dbo.DimSalesTerritory SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) + (SELECT ISNULL(MAX(SalesTerritoryKey), 0) SK FROM dbo.DimSalesTerritory) AS SalesTerritoryKey, SalesTerritoryAlternateKey, SalesTerritoryRegion, SalesTerritoryGroup, SalesTerritoryCountry FROM dbo.DimSalesTerritoryExternal; Note For best performance, run one PolyBase load operation at a time. You can scale up the load performance by increasing DWUs for the SQL Data Warehouse. For additional guidance related to using PolyBase with SQL Data Warehouse, see "Guide for using PolyBase in SQL Data Warehouse" at https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-load-polybase- guide/. Using SSIS A third option for loading data into your SQL Data Warehouse is to use SSIS packages. You design packages much as you do when working directly against SQL Server, except you must use an ADO.NET connection manager when connecting to SQL Data Warehouse. In the ADO.NET Destination, enable Use Bulk Insert When Possible for best performance during the data load. SSIS is a good option for a one-time load of a small data set. However, unlike the PolyBase loading described in the previous section, SSIS connects only to the control node, which can become a bottleneck if data volume is high. If you are extracting data from on-premises sources to load into SQL Data Warehouse, design your packages to resume package execution at the point of failure without repeating the work that was complete before the failure. Consider implementing the following strategies into your package design as part of your recovery strategy:

Articles in this issue

Links on this page

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016