Azure SQL Surrogate Database Classes

Question

So, Azure SQL Data Warehouse does not support identifier columns, and therefore it is difficult to handle surrogate keys. Does anyone have any bold decisions on this?

This is the best I have found, and it is pretty terrible.

Answer 1

This is the best option, but you can use a constant value in your OVER clause to avoid having to sort by a specific value, and you don't need to use a variable.

INSERT INTO testTgtTable (SrgKey, colA, colB)
SELECT
    ROW_NUMBER() OVER(ORDER BY (SELECT 1)) + (SELECT ISNULL(MAX(SrgKey),0) SK FROM dbo.testTgtTable) SK
  , [colA]
  , [colB]
FROM testSrcTable;

Answer 2

- SMP MPP Hadoop, NoSQL Big Data BI.

, , , :

• BI. - , ETL (SSIS, DataStage ..), NoSQL MPP Hadoop.

• , -, ELT ETL. " " (ELT) MPP BigData. -. , / () ( ).

• / , , .

• / - " ", , .

• , UAT .

• - MPP.

:

• - - .

• , - . - , . |, .

• - - .

, , ! , .. . .

Answer 3

. , .

• MAX() . , .
• CTAS . max_count row_number

:

DECLARE @max_count bigint
SET     @max_count = (SELECT MAX(ID) FROM Fact)

...

CREATE TABLE Input_Load
WITH (DISTRIBUTION = ROUND_ROBIN
     ,CLUSTERED COLUMNSTORE INDEX
     )
AS
SELECT @max_count + RowNumber
,      ...
FROM   dbo.stage_table
;

Answer 4

, , -, - , . , DW, BK. "" "" , BK , .

Answer 5

Identity CTAS, "". INSERTS, UPDATES, DELETES, ASDW

Answer 6

Identity Column Azure SQL Data.