Posted by Hugo Kornelis on 11/28/06 20:24

On Tue, 28 Nov 2006 16:16:00 +0100, Robert Klemme wrote:

(snip)
>If for some other reasons you really need the value beforehand, you can
>emulate an Oracle sequence
>
>-- test script
>create table counter (
> cnt int identity(1,1) primary key clustered
>)
>
>insert into counter default values
>print 'ident: ' + cast(scope_identity() as varchar)
>-- optional: truncate table counter
(snip)

Hi Robert,

I don't hink the optional TRUNCATE is a good idea, since TRUNCATE also
resets the identity seed :-)

If you don't want to fill up the table, here's a different suggestion
(blatantly stolen from Itzik Ben-Gan's excellent book):

BEGIN TRAN;
SAVE TRAN S1;
INSERT INTO counter DEFAULT VALUES;
SET @ident = SCOPE_IDENTITY();
ROLLBACK TRAN S1;
COMMIT TRAN;

The BEGIN TRAN and COMMIT TRAN are necessary for the SAVE TRAN and the
ROLLBACK with named savepoint to work. If you're already in a
transaction, the BEGIN TRAN will increase the tran counter and the
COMMIT TRAN will decrease it again. And the SAVE TRAN S1 / ROLLBACK TRAN
S1 combo ensures that the insert is undone (but the increment to the
identity seed and the variable assignment are left intact).

--
Hugo Kornelis, SQL Server MVP

[Back to original message]