CODEWITHSUNDEEP
sqlsql-servert-sqlobjectid
Ed Baker
Ed Baker

Reputation: 11

SQL to Generate Unique Object ID

Is it possible for multiple clients to obtain the same objectID if they all use this sql to generate the next objectID? Is there some industry standard code that we can use instead? Thanks.

self doWriteSql: 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'.
someRows := self executeSqlSelect:

RETRY:
BEGIN TRANSACTION
BEGIN TRY
    SELECT IDENTIFIER FROM IDENTIFIERTABLE WHERE TYPE = ',aType printString,'
    UPDATE IDENTIFIERTABLE SET IDENTIFIER = IDENTIFIER + ',anIncrement printString,' WHERE TYPE = ',aType printString,'
    COMMIT TRANSACTION


END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION
    IF ERROR_NUMBER() = 1205
    BEGIN
        WAITFOR DELAY ''00:00:00.05''
        GOTO RETRY
    END
END CATCH

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED'.

^someRows first first asInteger

Upvotes: 1

Views: 3915

Answers (1)

Kritner
Kritner

Reputation: 13765

What you posted seems ok enough at a glance, though like my comment and NeilMoss` said, there are already built in methods of creating unique identifiers.

GUID

Identity

having one of these as your primary key will ensure you will never have a duplicate record. A GUID (globally unique identifier) looks something like this:

declare @guid uniqueidentifier
set @guid = newId()

select @guid
-- 9BA52CDD-C2E3-4660-9E48-3661DD0B45FD

this will return a different value each time you run it.

an Integer Identity will start at the value specified, and increment based on what is specified.

create table test (id int primary key identity(1,1))

Identity starts at 1, and grows by one after each attempted insert. Note the keyword attempted - you will not always necessarily have an identity column grow as 1, 2, 3, 4, 5. It could go 1, 3, 4, 5 - if the transactional insert on row 2 were fail for some reason, the identity would be default be skipped over.

There are arguments that can be made over "which should I use" when it comes to GUID vs identity, as each has their own place - these articles seem to cover a lot of the information regarding which to use and when:

http://blog.codinghorror.com/primary-keys-ids-versus-guids/ http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html

Upvotes: 5

Related Questions