sequence number | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

sequence number

I am writing a program where I don’t care what the primary key is. Is there a way to just request a number from SQL, that hasn’t been used, to make this my id for inserting a new row? I know that there is a thing where I can just make the column an IDENTITY column, but that isn’t exactly what I’m looking for. Even though I don’t care what the number for my primary key is, I need to know it for inserting rows in other tables that are foreigns to this main table. Sorry if this is confusing. Basically all I need to know is if there is a way to get a random, unused identifier from SQL to use as a primary key when inserting a row into a table. Thanks,
Ben
To me, it sounds like an identity column is exactly what you want. I’m not sure why you think it wont work. Identity columns are stored as integers. They are often used as primary keys (very often) and can be used by other tables as foreign keys. This is one of the standard uses for identity fields. Before these existed, people would have to keep a seperate table with the last used integer and then add one to it for use. The identity column saves you so much work. You can write a function to accept a seed and generate a (psuedo)random number, then manually check the table to see if it hasnt been used, but why would you bother. Maybe if you can provide an example of why an identity wouldnt work, we can help more. chris
How about doing some tricks? declare @i int
declare @TableKey int set @loop = 1 while (1 = 1 )
begin — random key
SELECT @TableKey = cast(substring(cast(RAND() as varchar), 3, @loop ) as int) –start searching again if the number already exist in the table
if not exists (select * from YourTable where TableKey = @TableKey )
break set @loop = @loop + 1 end — verify the key
print cast(@TableKey as varchar)
Hi You can use the NEWID function (return type: uniqueidentifier),
more details in BOL. Oryan
As Chris said, it sounds like IDENTITY is what you need. You can get the last value inserted to an IDENTITY column by using SCOPE_IDENTITY() function then use it to insert to the other table/s. INSERT INTO TABLE_WITH_IDENTITY….
SELECT SCOPE_IDENTITY() If still this is not the solution for you, you can try to implement a sort of identity field youself. Create a table with 2 columns create table seq_table (column_name varchar(40), column_value int) write a stored procedure that gets the current value (according to the column name) and increments it. create procedure get_next_value
(
@column_name varchar(40)
)
as
set nocount on declare @next_value int update table
set @next_value = column_value = column_value + 1
where column_name = @column_name This will get you the current value and increament it right after.
Identity would work great, but I need to know the value before I do the insert.
More clarifications: I need to know the Identify value of the row I just added so that I can also create additional records in a subordinate table that references the value in this Identiy column as a foreign key. The IDENTITY column lets me add the row well enough – but how can I know the value of the row I just added without querying the whole table again? And in a multi-user environment, how do I know that the "last" row is the row that I added (vs. another user)? What seems like it would be ideal, to me, is to obtain a unique value – use it for my row and all the subornidate rows, and be assured that another user could not have accessed the same value. The GlobalUnique thing (30 characters guaranteed to be unique world wide) variable is larger than I want or need. In Oracle, this functionality was implemented in a "Sequence" object. You could select a Seq from the system, and be assured that it wouldn’t give that value to any other user – even if you never wound up adding any rows to the database (as in the case where the user CANCELS or something).
I belive your answer is in the above post:
quote:Originally posted by bambola As Chris said, it sounds like IDENTITY is what you need. You can get the last value inserted to an IDENTITY column by using SCOPE_IDENTITY() function then use it to insert to the other table/s. INSERT INTO TABLE_WITH_IDENTITY….
SELECT SCOPE_IDENTITY()
scope_identity() will assure that you get the last inserted value for your current scope and not from any other scope or user inserting values at the same time. /Argyle
Along with scope_identity(), if you need to know the value ‘before’ you insert, then use a trigger and select from the ‘inserted’ table. Both will work. chris
Here is a code sample. try to run it from QA create table test_identity (seq int identity (1,1), val varchar(20))
declare @last_id int
insert into test_identity select ‘value 1’
select @last_id = scope_identity()
select @last_id from another window in QA, run this declare @last_id int
insert into test_identity select ‘value 2’
select @last_id = scope_identity()
select @last_id You will see that select scope_identity() from the different windows (connections) will have a different value so no problems with multi users — don’t forget to clean up
drop table test_identity Bambola.
Hi ! , As per your requirements the following may be a simple solution for you . declare @id uniqueidentifier
select @id = newid()
update tablename set fieldname = @id
Here a uniqueidentifier is created first , saved in a variable and then the value is either updated or inserted into the table . That way you get to know the unique value prior to insertion . Regards,
Pranab
After Update/Insert, user "Select Variable = @@identity" for selecting the last used Identity value. You can store it in a variable & use it in other tables. Just remember that if insert or update fails, Identity value doesn’t get reverted back automnatically. HTH
Harsh
Thanks for all your help everybody!
Sorry, but if you are on SQL2K why don’t you just let SQL Server handle this by establishing some PK-FK relationships between your parent and child tables?<br /><br />I see no need to write one’s own code and I feel more confident using a built-in functionality (hopefully optimized [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]) than doing this on my own<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
Reread the thread again. Forget my post! Frank
http://www.insidesql.de
http://www.familienzirkus.de
]]>