GUID (equivalent of SEQUENCE in Oracle)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

GUID (equivalent of SEQUENCE in Oracle)

Author

Topic

mron0210
Starting Member

2 Posts

Posted - 2002-08-08 : 11:40:52

Hi,

I am trying to generate a Globally Unique Identifier, some thing similar to the SEQUENCE command in Oracle. I believe GUID in MS SQL Server works or I can use the IDENTITY key. How do I retrieve them without having to place them in the database table (i.e without creating a record in a particular table with IDENTITY or GUID key)? Can I retrieve multiple ID's with one statement or a batch ?

Thanks

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-08 : 12:02:28

There's really no need to double post.

Oracle and SQL Server handle this 'AutoNumber' feature differently. In SQL Server the IDENTITY is a property of the column. The column and the IDENTITY are indistinguishable. It will only increment when you insert.

GUID is something different and probably not what you want.

Why don't you explain the process you are trying to duplicate from your Oracle systems and explain how/why you need to know the ID's ahead of time?

Any way you slice it, the 'AutoNumber' feature is (most of the time) relational lazyness. You could/should design using the Natural Key.

Jay White
{0}

KHeon
Posting Yak Master

135 Posts

Posted - 2002-08-08 : 13:46:50

I think in Oracle, a SEQUENCE is an auto-incrementing number that you can query for, it'll return the next highest number and increment the count. It's not really an identity value but is, to my recollection often used (in web-programming) to prevent form-reposts, as I'll explain below:

- User requests webpage, which is a form.
- Code on the form calls Oracle for the next "SEQUENCE" number, which it returns.
- The form is assigned this value as an identifier.
- When the form is submitted, the backend code checks to see if the "SEQUENCE" number has been used in the database and if not adds the record, otherwise it assumes a re-post (double-click of the submit, etc) has happened and does something else.

mron0210: Can you confirm that this is what you mean by a SEQUENCE in Oracle?

If this matches what you are looking for then I'm afraid there is no equivalent. However, I've seen/heard of solutions that mimic this functionality, but it is, at best, a hack. Basically what you need to do is create a table that you query (updating a sequence_id of sorts) and return the next value. You just have to make sure that the value is always incremented otherwise you could get into data corruption issues. This, I'd imagine would be to create a sproc that updates the value and then returns the value immediately. Like I said, it's a hack, a single rowed table...

Hope this helps (some). Good luck!

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

quazibubble
Starting Member

25 Posts

Posted - 2002-12-16 : 00:23:04

Note to self: DO NOT hire Set Based Systems (http://www.setbasedsystems.com)!

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-12-16 : 08:01:26

quote:
Note to self: DO NOT hire Set Based Systems (http://www.setbasedsystems.com)!

I challenge you write an article about the dangers of natural keys. I'd love to hear your take on Codd's rules, normalization and relational theory with regard to keys. I'd love to see the results of your performance testing of the surrogate vs. natural key usage. At a minimum, I'd love to hear your response to my questions in the other thread.

Jay White
{0}

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-12-16 : 08:13:48

That would go against the rules of trolling

Damian

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-16 : 08:36:12

If you want what KHeon suggests then a guid generated in the middle layer sounds like it would fit the bill.

If you just want something that is unique in a database then an SP would do

create table tbl (ID int)
insert tbl select 0

create procedure SPGetNum
@ID int output
as
update tbl set ID = ID + 1, @ID = ID + 1
go

Or if you want to allocate a range
create procedure SPGetRange
@IDStart int output ,
@TDEnd int output ,
@Number int
as

update tbl set ID = ID + @Number, @IDStart = ID + 1, @IDEnd = ID + @Number
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Pol
Starting Member

1 Post

Posted - 2007-07-31 : 09:59:47

Este script lo encontré en [url]http://jamesthornton.com/software/coldfusion/nextval.html[/url] y funciona muy bien.
El problema con insertar en tablas (como los otros ejemplos) es que no siempre podemos obtener el valor que SQL insertó en la tabla.

ALTER TABLE sequences (
-- sequence is a reserved word
seq varchar(100) primary key,
sequence_id int
);

insert into sequences values ('numero',1)

CREATE PROCEDURE nextval
@sequence varchar(100),
@sequence_id INT OUTPUT
AS

-- Devuelve error si no existe la secuencia
set @sequence_id = -1

UPDATE sequences
SET @sequence_id = sequence_id = sequence_id + 1
WHERE seq = @sequence

RETURN @sequence_id

--**Forma de uso:
--DECLARE @numerador int
--EXECUTE nextval 'numero', @sequence_id = @numerador OUTPUT
--print @numerador

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 10:10:07

Jag har absolut ingen aning om vad du skriver. Jo, egentligen eftersom jag talar mycket lite spanska men inte tillräckligt för att förstå allt.

DECLARE @Sequences TABLE (Seq VARCHAR(100) PRIMARY KEY, ID INT)

INSERT	@Sequences
SELECT	'Numero', 1

DECLARE	@Sequence VARCHAR(100),
	@id INT

SELECT	@Sequence = 'Numero',
	@ID = -1

UPDATE	@Sequences
SET	@ID = ID = ID + 1
WHERE	Seq = @Sequence

SELECT	@ID

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-31 : 10:13:58

moved from article discussion.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources