[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

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

Aamir Mahmood

3/26/2007 11:53:00 PM

Is there any equivalent of Oracle's Sequence in SQL Server 2000/2005?

Or can I emulate it somehow?

Thanks,

Amir Mahmood.


11 Answers

Plamen Ratchev

3/27/2007 2:41:00 AM

0

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/librar...(SQL.80).aspx
http://msdn2.microsoft.com/en-us/librar...(sql.80).aspx

HTH,

Plamen Ratchev
http://www.SQL...

Uri Dimant

3/27/2007 6:17:00 AM

0


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/librar...(SQL.80).aspx
> http://msdn2.microsoft.com/en-us/librar...(sql.80).aspx
>
> HTH,
>
> Plamen Ratchev
> http://www.SQL...
>
>


Plamen Ratchev

3/27/2007 11:48:00 AM

0

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/seq...
http://www.psoug.org/reference/sequ...

Regards,

Plamen Ratchev
http://www.SQL...

Roy Harvey

3/27/2007 11:56:00 AM

0

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

Plamen Ratchev

3/27/2007 12:22:00 PM

0

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.SQL...

Uri Dimant

3/27/2007 12:39:00 PM

0

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


Roy Harvey

3/27/2007 12:59:00 PM

0

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.SQL...

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

Robert Klemme

3/27/2007 2:16:00 PM

0

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.SQL...
>
> 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

Roy Harvey

3/27/2007 2:45:00 PM

0

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
>

Uri Dimant

3/28/2007 5:53:00 AM

0

> 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
>>