469,088 Members | 1,268 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,088 developers. It's quick & easy.

Yukon - will it support sequence object?

I did some search and didn't find anything about whether Yokon will
support sequence object. Appreciate it if you can reply with some
resources.

Thanks, James
Jul 20 '05 #1
6 1237
Yukon has ROW_NUMBER(), which gives similar functionality.

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2
"David Portas" <RE****************************@acm.org> wrote in message news:<Da********************@giganews.com>...
Yukon has ROW_NUMBER(), which gives similar functionality.


David,

It looks like sequence is still tied to a given table, and will be
similar/same to identity column? Will Yukon support a sequencing
mechanism that is not specific to any table?

Thanks,
James
Jul 20 '05 #3
It isn't too difficult to use IDENTITY or even ROWVERSION as a
table-independent sequence generator. Just use a redundant table to generate
the sequence and then INSERT the keys where you need them.

I would much prefer to use a numbers table for this sort of thing. Making a
surrogate key serializable across multiple tables seems like the worst of
both worlds to me: a non-intelligent key plus a potential bottleneck and
resource hog.

--
David Portas
SQL Server MVP
--
Jul 20 '05 #4
> I would much prefer to use a numbers table for this sort of thing. Making a
surrogate key serializable across multiple tables seems like the worst of
both worlds to me: a non-intelligent key plus a potential bottleneck and
resource hog.


The advantage of the independent sequence is to allow your generated
value to have check digits etc applied before using it as a key in the
target table. Eg policy numbers or any number you expect a user to key
in for searches etc.

While there are alternatives, the sequence as specified in the
SQL-2003 standard is the most convenient and versatile method to
generate key values.

Christian.
Jul 20 '05 #5
You say Non-Intelligent Key .. I say Abstract Key.

The ID values should still be in temporal order (smaller ID # is earlier) so
its not truly without meaning.

The benefit of having a single pool of next id numbers to pull from across
all tables, is the ability to prevent bad joins by separating the domains of
each attribute.

If you have an employee and orders table both with an identity column the
following join will work even though its meaningless

select *
from Emp
join Order on Emp.EmpID = Order.OrderID

A single sequence generator that feeding both would mean that there never
would be an Order ID with the same value as an employee ID.

Also an Employee ID can be placed in other columns as an attribute without
causing confusion

select *
from Order
join Emp E1 on Emp.EmpID = Order.OrderExpediterEmpID
join Emp E2 on Emp.EmpID = Order.QualityControlEmpID

In this case a bad join would not bring back any rows (e.g. QC emps are not
listed in the Emp Table).

As for performance, I sort of see your point, but there are ways around that
if you really hit the wall (preallocate ranges for bulk operations, etc.)

We must not sacrifice the quality of the data in exchange for speed (in my
little value system anyway).


"David Portas" <RE****************************@acm.org> wrote in message
news:ja********************@giganews.com...
It isn't too difficult to use IDENTITY or even ROWVERSION as a
table-independent sequence generator. Just use a redundant table to generate the sequence and then INSERT the keys where you need them.

I would much prefer to use a numbers table for this sort of thing. Making a surrogate key serializable across multiple tables seems like the worst of
both worlds to me: a non-intelligent key plus a potential bottleneck and
resource hog.

--
David Portas
SQL Server MVP
--

Jul 20 '05 #6
> The ID values should still be in temporal order (smaller ID # is
earlier) so its not truly without meaning.


In a set-based INSERT all the rows are inserted simultaneously and they get
arbitrary keys. The sequence is non-deterministic and non-reproducable even
if you know the temporal order. There's a similar problem of how to merge
data from external sources into a table with a surrogate key. You are
heading for trouble as soon as you ascribe ANY meaning to a surrogate IMO.

--
David Portas
SQL Server MVP
--
Jul 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Lorenzo Braidi | last post: by
reply views Thread by Tim Anderson | last post: by
1 post views Thread by Jon | last post: by
reply views Thread by magister | last post: by
53 posts views Thread by Deniz Bahar | last post: by
52 posts views Thread by malcolm | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.