SQL Server Performance

Sequences without Identity

Discussion in 'General Developer Questions' started by hmckillop, Sep 17, 2003.

  1. hmckillop New Member

    Hello

    I have the following statement

    INSERT INTO TableA (OID, ReferenceField)
    SELECT MyOID, <Sequence>, DependantField, Otherfields
    FROM TableB

    I need the sequence to be made up of the current date and a number held in another table which should be updated each time its called. The sequence numbers are held in a table and are dependant on the additional field "DependantField")
    e.g.
    Sequence table
    SEQUENCENAME(DependantField) VALUE
    ABC 1
    CDE 1000

    Ideally I wanted a function which would take in the dependant field and would return the next in the sequence while updating the sequence table e.g
    INSERT INTO TableA (OID, ReferenceField)
    SELECT MyOID, UFN_fnGetNextSequence(DependantField), DependantField, Otherfields
    FROM TableB

    But I cant include an update statement within the function.

    Any help much appreciated,

    PS If even you can give me some code to increment a counter while inserting without using identity fields.

    Thanks

  2. bambola New Member

    You cannot update a table inside a functino. You could use a stored procedure to do it.
    Write a store procedure that gets you the next value and increments it.

    Suppose this is your sequence table

    CREATE TABLE SEQUENCE_TABLE (SEQUENCE_NAME varchar(20), SEQUENCE_VALUE int)

    INSERT INTO SEQUENCE_TABLE select 'ABC', 1 UNION ALL select 'CDE', 1000

    CREATE PROCEDURE GetNextID
    @SEQUENCE_NAME varchar(20),
    @SEQUENCE_VALUEint OUTPUT
    AS
    SET NOCOUNT ON

    SET @SEQUENCE_VALUE = NULL

    -- first asign the value to the variable, then increment the value in table.
    UPDATE SEQUENCE_TABLE
    SET @SEQUENCE_VALUE = SEQUENCE_VALUE = SEQUENCE_VALUE + 1
    WHERE SEQUENCE_NAME = @SEQUENCE_NAME

    IF (@@ROWCOUNT <> 1) OR (@@ERROR <> 0)
    RETURN -1 -- error
    RETURN 0 -- ok

    -- This is the call to the procedure.
    DECLARE @nextID int, @RC int
    exec @rc = GetNextID 'ABC', @nextID OUTPUT
    SELECT @RC, @nextID
    Just add to the code the current date.

    Bambola.
  3. Twan New Member


    I didn't know you could do this...?

    -- first asign the value to the variable, then increment the value in table.
    UPDATE SEQUENCE_TABLE
    SET @SEQUENCE_VALUE = SEQUENCE_VALUE = SEQUENCE_VALUE + 1
    WHERE SEQUENCE_NAME = @SEQUENCE_NAME

    two equal signs in one set line...? very cunning!!!

    Cheers
    Twan
  4. bambola New Member

    Isn't that cool? I use it in a certain situation when we cannot use identity and it works like a charm!

    Bambola.
  5. FrankKalis Moderator

    From what I understand it doesn't necessarily need to be stored in the db. Some kind of presentational stuff.
    You could use


    create table #t( f1 int primary key, c char(3))
    insert #t values(1, 'abc')
    insert #t values(11, 'abc')
    insert #t values(111, 'abc')
    insert #t values(23, 'cba')
    insert #t values(234, 'zze')
    insert #t values(2345, 'zze')
    insert #t values(345, 'yyw')
    select * from #t
    select t2.f1, t2.c,
    (select count(*) from #t t1 where t1.f1 <= t2.f1) AS uniq
    from #t t2
    order by 3

    this might also be interesting
    http://support.microsoft.com/defaul...port/kb/articles/q186/1/33.asp&NoWebContent=1

    Cheers,
    Frank

    Somehow the link is strangely inserted. Choosehttp://support.microsoft.com:80/support/kb/articles/q186/1/33.asp&NoWebContent=1 should work
  6. bambola New Member

    Interesting. And if the requirment is only to generate a seq number it would work. Keep in mind that 1- It would cost you a join on each select 2- you can only do it if you have a unique column or a combinations of columns that makes a unique value.
    I was under the impression that what hmckillop needs is a number that together with the date would server as a key. Maybe I was wrong.

    Bambola.

Share This Page