Hi!
Beeing new to sql-server I look for some pointers. I have a system,
written for Oracle
that uses sequences in order to get a primary key value. I see that
that is not supported in sql-server,
at least not in 2005. Is there any common practice to deal with this?
(Thinking of porting it to sql-server)

I know that there is something called auto-increment or so, but as I
understand it, its only per table.
I want to be able to get a number, and use it as primary key for
_several_ tables.
And also, it _has_ to be in a transaction of its _own_ , ie setting up
a table with
sequence_name, min_val max_val and cur_val is not enough, unless it
can be reached
outside the application transaction. A rollback on application side
cannot rollback sequnce number taken out.

Do I really need to have two connections, one for usual application
stuff, and one for getting sequence numbers.
or what is the common practice here?

/Björn
björn lundin

The most common practice I've seen is a sequence table updated on a separate
connection. If you must get the sequence using the current
connection/transaction, you'll need to use an IDENTITY table. The example
below "burns" an identity value with or without a current transaction
context:

CREATE TABLE dbo.Sequence(
SequenceNumber bigint IDENTITY(1,1)
)

--start or save transaction
IF @@TRANCOUNT = 0
BEGIN TRANSACTION GetSequence
ELSE
SAVE TRANSACTION GetSequence

INSERT INTO dbo.Sequence DEFAULT VALUES

ROLLBACK TRANSACTION GetSequence

SELECT SCOPE_IDENTITY()

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"björn lundin" <b.f.lundin (AT) gmail (DOT) com> wrote

Hi!
Beeing new to sql-server I look for some pointers. I have a system,
written for Oracle
that uses sequences in order to get a primary key value. I see that
that is not supported in sql-server,
at least not in 2005. Is there any common practice to deal with this?
(Thinking of porting it to sql-server)

I know that there is something called auto-increment or so, but as I
understand it, its only per table.
I want to be able to get a number, and use it as primary key for
_several_ tables.
And also, it _has_ to be in a transaction of its _own_ , ie setting up
a table with
sequence_name, min_val max_val and cur_val is not enough, unless it
can be reached
outside the application transaction. A rollback on application side
cannot rollback sequnce number taken out.

Do I really need to have two connections, one for usual application
stuff, and one for getting sequence numbers.
or what is the common practice here?

/Björn
björn lundin

On 28 Juni, 17:04, "Dan Guzman" <guzma... (AT) nospam-online (DOT) sbcglobal.net>
wrote:

Ok, so if I understand you correctly, you use Ident on a 'useless'
table,
insert a record, rolls i back, useing savepoint, and then gets the
number used
by a function call SCOPE_IDENTITY()
All in the same connection. Seem like a solution I can use,
by creating a 'useless' table for each sequence I need.

Thanks


/Björn
björn lundin

björn lundin (b.f.lundin (AT) gmail (DOT) com) writes:
Keep in mind that SQL Server is not Oracle and vice versa. Trying to
implement solutions on SQL Server as if it was Oracle or vice versa,
will only lead to frustration.

I don't know what your exact business requirements are, but they seemed to
modeled very close on a specific behaviour in Oracle (and for that matter
ANSI-SQL.) You may have reason to review them.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Your understanding is correct. I must admit this is a bit of a kludge but
will provide the behavior you requested.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"björn lundin" <b.f.lundin (AT) gmail (DOT) com> wrote

On 28 Juni, 17:04, "Dan Guzman" <guzma... (AT) nospam-online (DOT) sbcglobal.net>
wrote:

Ok, so if I understand you correctly, you use Ident on a 'useless'
table,
insert a record, rolls i back, useing savepoint, and then gets the
number used
by a function call SCOPE_IDENTITY()
All in the same connection. Seem like a solution I can use,
by creating a 'useless' table for each sequence I need.

Thanks


/Björn
björn lundin

You can also try the T-SQL specific syntax that allows you to set value of variable in the same statement as setting a
column value. See the following example:

CREATE TABLE Sequence (value INT);

INSERT INTO Sequence VALUES(0);

GO

CREATE PROCEDURE GetNextSequence @value INT OUTPUT
AS
UPDATE Sequence
SET @value = value = value + 1;

GO

DECLARE @new_sequence INT;

EXEC GetNextSequence @value = @new_sequence OUTPUT;

SELECT @new_sequence AS new_sequence;

--
Plamen Ratchev
http://www.SQLStudio.com

On 28 Juni, 23:19, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Yes, correct. This is a legacy design, the system has been around
since the early 90-ies.
From the very beginning, there was a sequence table, from which we
retrieved system numbers.
The service to retrieve the numbers made sure there was no transaction
in progress when
entering the service. The numbers were put in lists, and the routines
that wanted the numbers
started a transaction, and made a service call to retrive from the
_list_ .

Now, after the system had grown, this design went from bad to worse,
and at the time,
oracle was the only db we ran the system upon, and sequences came to
look as a good thing.

The routines handling the lists were changed, to get numbers from
sequnces directly, and
the programmers philosophy changed, so we relied entirely on the
sequence behaviour.
Ie, no going back to the old way of coding.

(This system went from VAX/VMS to unix to windows, using Oracle _most_
of the time).

So this is an attempt to do the porting to sql-server without too much
rewrite

/Björn
björn lundin

björn lundin (b.f.lundin (AT) gmail (DOT) com) writes:
I don't know what sort of system this, what performance requirements there
are etc. If this is system that leads an easy and lesurly life, it may
work out. If this aims at being a high-end system, you have a tough ride
ahead of you.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx