Post reply

Sequence For a Dim Table's Durable Key

  • December 17, 2019 at 3:53 pm

    #3708513

    I am using a sequence as the durable key (DK) of a dim table.  It gets its value from a sequence from the update statement. We have to populate this column as part of the code it cannot be by default.

    The update statement is already written, I'm just confused about how to not make the DK/sequence column populate via the update as opposed to by default.

    How can I make this happen?

  • December 17, 2019 at 8:54 pm

    #3708613

    I'm not sure I understand the question, did you mean you want to explicitly pass in the value for this column in the INSERT statement?

    If this column is defined as NOT NULL, and has the NEXT VALUE FOR sequencename expression as the DEFAULT, then the only way to avoid the sequence is to explicitly include the column in the INSERT.  e.g.:

    CREATE SEQUENCE sq_Items AS int START WITH 1000 INCREMENT BY 1;

    CREATE TABLE DimItems (
        DimItemKey int NOT NULL DEFAULT NEXT VALUE FOR sq_Items,
        Val varchar(10));

    INSERT INTO DimItems (DimItemKey, Val) VALUES (0, 'Things');

    If this column is nullable and you don't want the DEFAULT to evaluate, you would need to explicitly specify NULL e.g.:

    CREATE SEQUENCE sq_Items2 AS int START WITH 1000 INCREMENT BY 1;

    CREATE TABLE DimItems2 (
        DimItemKey int NULL DEFAULT NEXT VALUE FOR dbo.sq_Items2,
        val varchar(10));

    INSERT INTO DimItems2 (DimItemKey, val) VALUES (NULL, 'Junk');

     

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply