safe tsql Numbering int techniques

Question

Hello stackOverflowers !

i was wondering if theres a way to get in a safe way, series of numbers in transactions just like the identity.My only purpose is for grouping rows in tables and i don't mean row_number(). i've came up with this simple query, is this safe? this table has its own identity key

declare @mynextSecuenceNumber int
    select @mynextSecuenceNumber=isnull(max(secuencenumber+1),1) from mytable
insert into mytable (productID,customer,secuencenumber) values (@someval,@anotherval,@mynextSecuenceNumber)

EDIT

THE BACKGROUND

the reason for doing this is the next:

first i'm recieving autoparts for car services then i generate a ticket for that recepcion(i can recieve one,two,three auto parts) later on i can continue on reciving autoparts for that specific car service from the same autopart provider or from a different provider but i want to be able to re generate the event or the ticket otherwise i'll end up querying the service and all the the autoparts associated with that or the provider and i wont know the event what i recived in that operation and on top of that i need another specific id for all those autoparts associated with that car service.

by the way i'm on sql server 2008

heads up

using identity as secuence number can be messy cus transactions will increment the value after rolling back and other issues so be aware of that thanks to the approach privided as my acepted answer i can find another way who gets along with transactions its the first to appear on the link

Answer 1

Here's a scalable recommendation from Microsoft when SQL 2012 or higher isn't an option, but you need to manage sequence numbers without identities in the target table. This creates a separate table to track the sequence numbers, let's identity do some of the heavy lifting, and keeps the table size minimal by cleaning up. If this load becomes too much, you can schedule something for the cleanup during off-peak time.

-- Create table for tracking the sequence
create table <tablename> (
      SeqID int identity(1,1) primary key
)
GO

-- Create procedure to return next sequence value
create procedure GetNewSeqVal_<tablename>
    @NextValue int output
as
begin
    declare @NewSeqValue int
    set nocount on
    insert into <tablename> DEFAULT VALUES
    set @NewSeqValue = scope_identity()
    delete from <tablename> with (readpast)
    set @NextValue = @NewSeqValue
end
go

-- Get next sequence
declare @seqId int
exec GetNewSeqVal_<tablename> @NextValue = @seqId OUTPUT

For more info: http://blogs.msdn.com/b/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx