Tag Archives: Update

The SEQUENCE, an updatable alternative for an IDENTITY column

Introduction

Recently I used SEQUENCES instead of IDENTITY columns. I was aware of their existence but there was never a need to use them .. until now.

Problem

Identity values of another system needed to be preserved, I know normally it is trivial if you should want this, but without going into detail, I can tell you this time it made sense.
But the problem with IDENTITY columns is that you can only assign a specific value during INSERT using the IDENTITY_INSERT option.
In my case this was not a simple solution. I would need a complicated script to create a new table, use IDENTITY_INSERT to copy over the rows from the current table, then recreate all foreign keys that refer to the old table so that they refer to the new table, then drop foreign keys referring to the old table, then drop the old table, then rename the new table to the old name. Sounds like a complicated plan, right? This is what I thought as well ..


Picture credits: © Can Stock Photo / Andreus

Solution

Using SEQUENCES made it possible to UPDATE the values. But I must admit, if you already have a table with an IDENTITY column, with rows, with foreign keys, it is already too late to switch to using a SEQUENCE, or at least you need the complicated plan, described above.
But in my case I generated a data warehouse house with biGENiUS, a data warehouse automation tool.
Then I did an initial load, and then I wanted to set some ID’s right. I added ID’s to Data Vault 2.0 hubs to use for SCD Type 1 Dimensions.
The flexibility of biGENiUS made it possible for me to change the template for the generated hub, and use a column that gets a value from a SEQUENCE instead of an IDENTITY column. So 15 minutes later I had what I wanted, an updatable sequence number in the hub that had a hashkey as primary key.
Without going into more details about why I choose this approach and whether it is good or not, I want to share the scripts with you.

First the script for the table with a sequence number/autonumbered column, in my case a hub table in a data vault.

create_hub.sql

USE [MSSQL_E13]
GO
CREATE SCHEMA [raw]
GO

IF OBJECT_ID (N'[raw].[Example_Hub]', N'U') IS NOT NULL
    DROP TABLE [raw].[Example_Hub];
GO

CREATE TABLE [raw].[Example_Hub] (
     [Example_HK] CHAR(32) NOT NULL -- the hashkey
    ,[Example_BK] NVARCHAR(10) NOT NULL -- the business key
    ,[SA_SourceSystem_ID] SMALLINT NOT NULL
    ,[DW_Load_ID] BIGINT NOT NULL
    ,[DW_Sequence_ID] INT NOT NULL -- don't add IDENTITY(1,1) here,
                                   -- the alternative is below.
    ,CONSTRAINT [PK_Example_Hub] PRIMARY KEY NONCLUSTERED (
          [Example_HK] ASC -- due to the distributed character of
          -- the hashkey, it is created as NONCLUSTERED.
    )
    ,CONSTRAINT [IX_UN_Example_Hub] UNIQUE (
          [Example_BK]
    )
    ,CONSTRAINT [IX_UC_Example_Hub] UNIQUE CLUSTERED (
          [DW_Sequence_ID] -- the additional sequence number is
          -- created with a UNIQUE CLUSTERED index.
    )
);
GO
-- Create a sequence to use instead of IDENTITY.
-- Note that the sequence is not bound to the table.
-- It is in our case only related to the table using a naming
-- convention {TABLENAME}_Sequence, but you could use a sequence
-- to be used by multiple tables, although I do not know why you
-- would want that (it could become a bottleneck for insertions).
IF OBJECT_ID('[raw].[Example_Hub_Sequence]', 'SO') IS NULL
    CREATE SEQUENCE [raw].[Example_Hub_Sequence] AS INT START WITH 1;

-- Add default constraint for column [DW_Sequence_ID] so that
-- it uses the next value of the sequence.
ALTER TABLE [raw].[Example_Hub]
    ADD CONSTRAINT [DF_raw_Example_Hub__DW_Sequence_ID]
    DEFAULT NEXT VALUE FOR [raw].[Example_Hub_Sequence]
    FOR [DW_Sequence_ID];
GO

Then the script to update the sequence number from an existing table, based/joined on the business key.

update_sequence.sql

--\
---) Keep ExampleId's
---) Obviously it only makes sense to run this script when
---) (generated) values are loaded to the table and the [DW_Sequence_ID]
---) column, that you now want to change.
--/
BEGIN TRY
    BEGIN TRAN;

    --\
    ---) Swap the sign of all ID's to a negative value.
    ---) This works only if all values are positive.
    --/
    UPDATE hub
      SET [DW_Sequence_ID] = -1 * [DW_Sequence_ID]
      FROM [raw].[Example_Hub] hub
      WHERE [DW_Sequence_ID] > 0;

    --\
    ---) Now update the values.
    --/
    UPDATE hub
      SET [DW_Sequence_ID] = curdb.ExampleId
      FROM [raw].[Example_Hub] hub
      JOIN [ExistingDB].[dbo].[dim_Example] curdb
        ON curdb.ExampleCode = hub.Example_Code_BK

    --\
    ---) Make sure that the sequence next value will be higher than the
    ---) already used ID's in the existing table.
    --/
    DECLARE @MaxID_New INT = ISNULL((SELECT MAX([DW_Sequence_ID]) FROM [raw].[Example_Hub]), 0) + 1;
    DECLARE @sql VARCHAR(1000)

    SET @sql = 'ALTER SEQUENCE [raw].[Example_Hub_Sequence] RESTART WITH ' + CONVERT(VARCHAR, @MaxID_New) + ';'
    PRINT @sql
    EXEC(@sql);

    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRAN;
    THROW;
END CATCH

--\
---) If not all values are updated, you have to check manually.
---) Maybe you can just swap the sign again to make it a positive value, if that
---) does not conflict with existing values.
--/
IF EXISTS(SELECT 1 FROM [raw].[Example_Hub] hub WHERE [DW_Sequence_ID] < 0)
BEGIN
    PRINT 'Not all rows of [Example_Hub] are updated, please check.'
    SELECT * FROM [raw].[Example_Hub] hub WHERE [DW_Sequence_ID] < 0;
END

GO

Download the scripts here.

Conclusion / Wrap up

A sequence is an object type in SQL Server of which the usage is not very widely spread, as far as I know. But it can be useful!
It can be used as an alternative to a column with an IDENTITY specification, if you want to update the value later on.
Please make sure if you update the values, that after this update the SEQUENCE object will only return values that are higher than the updated values. It is your responsibility to make this happen. An example of how to to this is shown in the second script.

(c) 2017 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

Free download: Blog Yearbook 2016

I have made a compilation of all my blog posts of the past year, and it was more work than you would think.
Especially because it also contains a non-published new article.

After filling in the form you can download the e-book for free.

I promise you the following:

  • I will not give or sell your email address to any third party.
  • I will not spam you.
  • If I start with a newsletter, you can easily unsubscribe or change the frequency.

(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

It’s patch & update time! (Updates on previous posts)

Introduction

As you might have noticed I have had a lack of time over the past weeks, this is why my posts are a bit irregular. Sorry for that.
Today a listing of some updates that I did lately.

The updates

How to copy a database table structure (T-SQL Scripting Series)

This script was last updated on 14 July, 2016. If you use it, make sure it is the most recent version.

Using alternate (non-time) triggers to start a SQL Agent job (SQL Server Series)

This article and open source code on Github was updated a few days ago. Please check it if you are interested.

How to protect your stored procedures against multiple concurrent executions (SQL Server Series)

This article is just updated. Thanks to very useful comments some of you gave me on the initial article (thank you for that) I have added examples of using sp_getapplock and sp_releaseapplock.

Conclusion / Wrap up

Nobody is perfect, this why updates exist.

(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.