Emulate Oracle's "Sequence" on SQL Server 2000/2005

Is there any equivalent of Oracle's Sequence in SQL Server 2000/2005?
Or can I emulate it somehow?
Thanks,
Amir Mahmood.Take a look at IDENTITY. There are some differences, but in general works
the same way. You can also implement yourself by creating a Sequence table
and managing the increment of numbers.
See more info on IDENTITY here:
http://msdn2.microsoft.com/en-us/library/aa933196(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/aa933167(sql.80).aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Plamen
Actually an INDENTITY property can cause gaps as well as duplicates.
Aamir
In SQL Server 2005 you have ROW_NUMBER() function. For SQL Server 2000 to
generate a sequence can be costly on large set of data
USE northwind
GO
SELECT OrderID,(SELECT COUNT(*) FROM Orders O WHERE
O.OrderID<=Orders.OrderID) AS seq
FROM Orders
ORDER BY seq
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:O3UllmBcHHA.2120@.TK2MSFTNGP03.phx.gbl...
> Take a look at IDENTITY. There are some differences, but in general works
> the same way. You can also implement yourself by creating a Sequence table
> and managing the increment of numbers.
> See more info on IDENTITY here:
> http://msdn2.microsoft.com/en-us/library/aa933196(SQL.80).aspx
> http://msdn2.microsoft.com/en-us/library/aa933167(sql.80).aspx
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>|||Hi Uri,
I know IDENTITY can cause gaps and duplicates (btw, the Oracle sequence
too). But sequence in Oracle for most part is used to create an autonumber
column. Using ROW_NUMBER will not do that, as row number is generated after
you have the data in your table.
Here is more on the sequence in Oracle:
http://www.techonthenet.com/oracle/sequences.php
http://www.psoug.org/reference/sequences.html
Regards,
Plamen Ratchev
http://www.SQLStudio.com|||On Tue, 27 Mar 2007 08:17:00 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:
>Actually an INDENTITY property can cause gaps as well as duplicates.
I know about the gaps, but don't recall ever hearing about the
duplicates. Can you expand on that?
Roy Harvey
Beacon Falls, CT|||My interpretation about the duplicates was that you can use SET
IDENTITY_INSERT ON and insert a duplicate value in the column (if there is
no unique index, primary key, etc. on the column)...
Plamen Ratchev
http://www.SQLStudio.com|||Roy
There are two methods that I know about them
Palmem had demonstrated one of them
create table test (col1 int identity(1,1),col2 char(1))
create table test_demo (c int)
insert into test_demo values (1)
insert into test_demo values (2)
insert into test (col2) values ('a')
insert into test (col2) values ('b')
set IDENTITY_INSERT test ON
insert into test (col1) select c from test_demo
set IDENTITY_INSERT test OFF
select * from test --we have duplicates
--another one is to use
--DBCC CHECKIDENT
DBCC CHECKIDENT (test, RESEED, 1)
insert into test (col2) values ('a')
select * from test
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:hg1i03t3k3trd8i7q9246ljssjrv8jtbts@.4ax.com...
> On Tue, 27 Mar 2007 08:17:00 +0200, "Uri Dimant" <urid@.iscar.co.il>
> wrote:
>>Actually an INDENTITY property can cause gaps as well as duplicates.
> I know about the gaps, but don't recall ever hearing about the
> duplicates. Can you expand on that?
> Roy Harvey
> Beacon Falls, CT|||On Tue, 27 Mar 2007 08:22:24 -0400, "Plamen Ratchev"
<Plamen@.SQLStudio.com> wrote:
>My interpretation about the duplicates was that you can use SET
>IDENTITY_INSERT ON and insert a duplicate value in the column (if there is
>no unique index, primary key, etc. on the column)...
>Plamen Ratchev
>http://www.SQLStudio.com
You are correct that using the ability to manually override the
IDENTITY assignment allows creating duplicates when there is no key,
constraint or index preventing it. That isn't quite what I would
describe as the IDENTITY property "causing" duplicates, but it is
certainly correct that IDENTITY_INSERT can be used to cause
duplicates. Gaps, on the other hand, can happen without any such
override and are a normal part of using IDENTITY.
Roy Harvey
Beacon Falls, CT|||On 27.03.2007 14:59, Roy Harvey wrote:
> On Tue, 27 Mar 2007 08:22:24 -0400, "Plamen Ratchev"
> <Plamen@.SQLStudio.com> wrote:
>> My interpretation about the duplicates was that you can use SET
>> IDENTITY_INSERT ON and insert a duplicate value in the column (if there is
>> no unique index, primary key, etc. on the column)...
>> Plamen Ratchev
>> http://www.SQLStudio.com
> You are correct that using the ability to manually override the
> IDENTITY assignment allows creating duplicates when there is no key,
> constraint or index preventing it. That isn't quite what I would
> describe as the IDENTITY property "causing" duplicates, but it is
> certainly correct that IDENTITY_INSERT can be used to cause
> duplicates. Gaps, on the other hand, can happen without any such
> override and are a normal part of using IDENTITY.
And the same happens with sequences as well: you can insert duplicates
by manually providing values and you can reseed the sequence. Of
course, a UNIQUE constraint will prevent that - in both worlds. :-)
The major conceptual difference between IDENTITY and Oracle sequences is
IMHO that an IDENTITY column is tied to a table whereas this is not the
case with sequences. Personally I find sequences more modularized and
more flexible but that's probably a matter of taste.
Kind regards
robert|||Thanks, I understand what you meant now. I would not describe that as
IDENTITY >causing< gaps, but that is no big thing.
Roy Harvey
Beacon Falls, CT
On Tue, 27 Mar 2007 14:39:13 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:
>Roy
>There are two methods that I know about them
>Palmem had demonstrated one of them
>create table test (col1 int identity(1,1),col2 char(1))
>create table test_demo (c int)
>insert into test_demo values (1)
>insert into test_demo values (2)
>
>insert into test (col2) values ('a')
>insert into test (col2) values ('b')
>
>set IDENTITY_INSERT test ON
>insert into test (col1) select c from test_demo
>set IDENTITY_INSERT test OFF
>select * from test --we have duplicates
>--another one is to use
>--DBCC CHECKIDENT
>DBCC CHECKIDENT (test, RESEED, 1)
>insert into test (col2) values ('a')
>select * from test
>
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>news:hg1i03t3k3trd8i7q9246ljssjrv8jtbts@.4ax.com...
>> On Tue, 27 Mar 2007 08:17:00 +0200, "Uri Dimant" <urid@.iscar.co.il>
>> wrote:
>>Actually an INDENTITY property can cause gaps as well as duplicates.
>> I know about the gaps, but don't recall ever hearing about the
>> duplicates. Can you expand on that?
>> Roy Harvey
>> Beacon Falls, CT
>|||> IDENTITY >causing< gaps, but that is no big thing.
You meant >causing< duplicates ? :-))
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:1cbi03hevcvaeu0mg6fi8e4scd6cpp9h6n@.4ax.com...
> Thanks, I understand what you meant now. I would not describe that as
> IDENTITY >causing< gaps, but that is no big thing.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 27 Mar 2007 14:39:13 +0200, "Uri Dimant" <urid@.iscar.co.il>
> wrote:
>>Roy
>>There are two methods that I know about them
>>Palmem had demonstrated one of them
>>create table test (col1 int identity(1,1),col2 char(1))
>>create table test_demo (c int)
>>insert into test_demo values (1)
>>insert into test_demo values (2)
>>
>>insert into test (col2) values ('a')
>>insert into test (col2) values ('b')
>>
>>set IDENTITY_INSERT test ON
>>insert into test (col1) select c from test_demo
>>set IDENTITY_INSERT test OFF
>>select * from test --we have duplicates
>>--another one is to use
>>--DBCC CHECKIDENT
>>DBCC CHECKIDENT (test, RESEED, 1)
>>insert into test (col2) values ('a')
>>select * from test
>>
>>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>>news:hg1i03t3k3trd8i7q9246ljssjrv8jtbts@.4ax.com...
>> On Tue, 27 Mar 2007 08:17:00 +0200, "Uri Dimant" <urid@.iscar.co.il>
>> wrote:
>>Actually an INDENTITY property can cause gaps as well as duplicates.
>> I know about the gaps, but don't recall ever hearing about the
>> duplicates. Can you expand on that?
>> Roy Harvey
>> Beacon Falls, CT|||On Wed, 28 Mar 2007 07:53:19 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:
>> IDENTITY >causing< gaps, but that is no big thing.
>You meant >causing< duplicates ? :-))
Yes, thanks Uri for catching that.
Roy