UNION SELECT, parallelization and IDENTITY

Viewed 82

There are four identical staging tables:

CREATE TABLE s_table_1
(
      id INT IDENTITY(1, 4) NOT NULL PRIMARY KEY CLUSTERED
    , some_data NVARCHAR(100)
);

CREATE TABLE s_table_2
(
      id INT IDENTITY(2, 4) NOT NULL PRIMARY KEY CLUSTERED
    , some_data NVARCHAR(100)
);

CREATE TABLE s_table_3
(
      id INT IDENTITY(3, 4) NOT NULL PRIMARY KEY CLUSTERED
    , some_data NVARCHAR(100)
);

CREATE TABLE s_table_4
(
      id INT IDENTITY(4, 4) NOT NULL PRIMARY KEY CLUSTERED
    , some_data NVARCHAR(100)
);

Subsequently, they are used in a view:

CREATE VIEW s_table AS
              SELECT * FROM dbo.s_table_1
    UNION ALL SELECT * FROM dbo.s_table_2
    UNION ALL SELECT * FROM dbo.s_table_3
    UNION ALL SELECT * FROM dbo.s_table_4;

The rationale behind this structure is to be able to stage data in four parallel streams to make use of multi-core CPU on the server (data comes from a large flat text file from a local drive; reading that file it is much, much faster than writing rows to target table; splitting this to four streams gives me over 3x faster ETL and I need this to be fast)

I need the ID values to be unique across all four staging tables - this is why I created them as IDENTITY(N, 4) where N is 1, 2, 3 or 4, respectively.

Is this setup "risky" in terms of uniqueness? I mean, is there any chance that one of those four IDENTITY columns clashes with another one?

Any hints welcome.

SQL Server 2012

1 Answers

From SQL Server 2012 you are able to use sequences instead of Identity for such scenarios. See here

However SQL Server uses all cores unless you cap with using maxdop. A performance benefit would be if you store the tables on different filegroups on different drives to increase read performance.

Related