CODEWITHSUNDEEP
sqlsql-server
NewbieProgrammer
NewbieProgrammer

Reputation: 864

Auto Generate Unique column in SQL database

I was using the primary key (TicketID) as an identity column which was auto incrementing on every time a ticket is generated. Everything was going smoothly and then I started seeing that my TicketID jumps from 5,6 to like 1007, 1008.

Upon googling, I found about this design-by in SQL Server 2012:

http://connect.microsoft.com/SQLServer/feedback/details/743300/identity-column-jumps-by-seed-value

Now I want to custom create a column, which will auto increment just like identity column but does not jump and leave gaps (I don't want an identity column)

How should I do that? I think triggers are what I am looking for but as I have never used triggers before, I would really appreciate some help here.

OR should I use computed column?

Upvotes: 1

Views: 2581

Answers (4)

NewbieProgrammer
NewbieProgrammer

Reputation: 864

I have just solved my problem using a AFTER INSERT trigger. Here it is what I did.

     ALTER TRIGGER [dbo].[tid2]
     ON [dbo].[tblPrac2]
     AFTER INSERT
     AS
     declare @nid int;

     set @nid = ( select MAX(TicketID) from [tblPrac2] );

     if(@nid is null)
     begin
         set @nid = 1;
     end
     else
         set @nid = @nid + 1;

update tblPrac2 set TicketID = @nid where ID in (select ID from inserted)

Upvotes: 1

Aleksei Semidotskii
Aleksei Semidotskii

Reputation: 1465

Create you own autoincrement mechanism is a bad idea.

Look at SEQUENCE object http://technet.microsoft.com/en-us/library/ff878091.aspx

But if you still want to do this, one possible solution is

CREATE TABLE dbo.TicketNumber
(
    Number INT NOT NULL
)

INSERT INTO dbo.TicketNumber(Number) VALUES(0)

CREATE PROCEDURE dbo.sp_GenerateTicketNumber
(
    @Number INT OUT
)
AS
BEGIN

    DECLARE @Number INT 
    DECLARE @CurrentNumber INT

    BEGIN TRANSACTION

        SELECT
            @CurrentNumber = Number
        FROM dbo.TicketNumber WITH(UPDLOCK)

        SET @Number = @CurrentNumber + 1

        UPDATE dbo.TicketNumber 
        SET Number = @Number

    COMMIT TRANSACTION

END

The alternative implementation of dbo.sp_GenerateTicketNumber may be looks like

DECLARE @number TABLE(number INT);

UPDATE dbo.TicketNumber
SET 
    [Number] = [Number] + 1
OUTPUT INSERTED.Number INTO @number;

SELECT * FROM @number

And the solution you want, probably

CREATE PROCEDURE dbo.sp_RegisterTicket
(
    @PersonName varchar(255),
    @TicketNumber INT OUT
)
AS
BEGIN

    BEGIN TRAN

        SELECT
            @TicketNumber = MAX(TicketId) + 1
        FROM dbo.Tickets WITH(UPDLOCK)

        INSERT INTO dbo.Tickets VALUES(@TicketNumber, @PersonName)

    COMMIT TRAN

END

Using example:

DECLARE @Number INT
EXEC dbo.sp_RegisterTicket 'Vasya', @Number OUT

SELECT @Number

Upvotes: 2

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

You cannot manipulate IDENTITY column to remain without gaps . For implementing your requirement , you will have to use a table with single column and row which stores the current maximum value of TicketID and a stored procedure to access this value , increase it by 1 if insertion takes place and subtract by one if deletion takes place. Of course this will have impact on performance vs using IDENTITY column and ignoring the gaps.

Upvotes: 0

peter.petrov
peter.petrov

Reputation: 39477

You can use unique identifier, I think.

http://technet.microsoft.com/en-us/library/ms187942.aspx

But it takes more space of course.

Upvotes: 0

Related Questions