sql server - safe tsql Numbering int techniques -

hello stackoverflowers !

i wondering if theres way in safe way, series of numbers in transactions identity.my purpose grouping rows in tables , don't mean row_number(). i've came simple query, safe? table has own identity key

declare @mynextsecuencenumber int     select @mynextsecuencenumber=isnull(max(secuencenumber+1),1) mytable insert mytable (productid,customer,secuencenumber) values (@someval,@anotherval,@mynextsecuencenumber) 

edit

the background

the reason doing next:

first i'm recieving autoparts car services generate ticket recepcion(i can recieve one,two,three auto parts) later on can continue on reciving autoparts specific car service same autopart provider or different provider want able re generate event or ticket otherwise i'll end querying service , the autoparts associated or provider , wont know event recived in operation , on top of need specific id autoparts associated car service.

by way i'm on sql server 2008

heads up

using identity secuence number can messy cus transactions increment value after rolling , other issues aware of approach privided acepted answer can find way gets along transactions first appear on link

here's scalable recommendation microsoft when sql 2012 or higher isn't option, need manage sequence numbers without identities in target table. creates separate table track sequence numbers, let's identity of heavy lifting, , keeps table size minimal cleaning up. if load becomes much, can schedule cleanup during off-peak time.

-- create table tracking sequence create table <tablename> (       seqid int identity(1,1) primary key ) go  -- create procedure return next sequence value create procedure getnewseqval_<tablename>     @nextvalue int output begin     declare @newseqvalue int     set nocount on     insert <tablename> default values     set @newseqvalue = scope_identity()     delete <tablename> (readpast)     set @nextvalue = @newseqvalue end go  -- 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