Transact SQL :- Sequence Object

Background

As I read more about Hibernate, reminded once again the usage of Identity Columns to implement Sequences is quite unique to SQL Server and likely Sybase.

And, that Oracle, for one, uses Sequence to generate increasing numbers.

Hibernate

hibernate.IdGeneratorStrategy

Sample Code

Outline

  1. Schema
    • Does Schema exists?
      • schema_id()
    • If Schema does not exist?
      • Create Schema
  2. Sequence
    • Does Sequence exists?
      • Consult sys.sequences
    • If Sequence does not exist
      • Create Using “CREATE SEQUENCE”
  3. Utilize Sequence Generator
    • Add Data
      • Get next available Sequence
        • Syntax
          • NEXT VALUE FOR [sequence object]
        •  Sample
          • NEXT VALUE FOR [lab].[seq]
  4. Review Sequence Objects
    • sys.sequences
      • name
      • is_cached
      • increment
      • current_value
      • is_exhausted
      • maximum_value

Code


use [tempdb]
go

set nocount on
go

set XACT_ABORT on
go

declare @commit bit

declare @object  sysname

declare @schema_id int
declare @schema    sysname
declare @sequence  sysname

set @commit = 0

set @schema = 'lab'
set @sequence = 'seq';

begin tran

    /*
        Get schema_id for schema @schema
    */
    set @schema_id = schema_id(@schema);

    /*
        If schema_id is null, then does not exist
        create it
    */
    if @schema_id is null
    begin

        exec('create schema [lab] authorization [dbo]')

    end

    set @schema_id = schema_id(@schema);

    /*
        If schema does not exist, please create it
    */
    if not exists
    (
        select *
        from   sys.sequences
        where  [name] = @sequence
        and    [schema_id] = @schema_id
    )
    begin

        CREATE SEQUENCE [lab].[seq]
        AS INTEGER
        START WITH 1
        INCREMENT BY 1
        MINVALUE 1
        --MAXVALUE 1000000
        CYCLE
        ; 

    end

    /*
        Please create table lab.vehicle
    */
    if object_id('[lab].[vehicle]') is null
    begin

        create table [lab].[vehicle]
        (
            id bigint not null
                DEFAULT NEXT VALUE FOR [lab].[seq]
        )

    end

    /*
        Please create table lab.bridge
    */
    if object_id('[lab].[bridge]') is null
    begin

        create table [lab].[bridge]
        (
            id bigint not null
                DEFAULT NEXT VALUE FOR [lab].[seq]

            , [name] nvarchar(80) not null

        )

    end

    /*
        Insert data
    */
    insert into [lab].[vehicle] default values;

    insert into [lab].[bridge]([name]) values('Seven Mile Bridge');

    insert into [lab].[vehicle] default values;

    insert into [lab].[bridge]([name]) values('Royal Gorge Bridge and Park');

    insert into [lab].[bridge]([name]) values('New River Gorge Bridge');

    /*
        Review data
    */
    select *

    from   [lab].[vehicle]

    select *

    from   [lab].[bridge]

    /*
        Review Sequence
    */
    select
              [schema] = tblSS.[name]
            , tblSeq.[name]
            , tblSeq.is_cached
            , tblSeq.increment
            , tblSeq.current_value
            , tblSeq.is_exhausted
            , tblSeq.maximum_value

    from   sys.sequences tblSeq

    inner join sys.schemas tblSS

            on tblSeq.[schema_id] = tblSS.schema_id

    where  tblSeq.[name] = @sequence
    ;

while (@@TRANCOUNT>0)
begin

    if (@commit =1)
    begin

        commit tran;

    end
    else
    begin

        rollback tran;
    end
end

Output

sequence.lab.20190512.1134PM

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s