Adding an IDENTITY column to a temp table
Unknown
My process requires that I use "alter table" to add an
IDENTITY
column to an existing temporary table that was created using
"select...into...order by" syntax. When we add the IDENTITY
column, we observe that the IDENTITY number starts at 1 and
is
assigned to the record first entered into the temp table
upon
its creation, as was detemined by the "order by" clause; 2
to
the second, 3 to the third and so on.
My question: Is this just a co-incidence that the number
values are assigned sequentially according to the order in
which the records were added to the temp table, or does
anyone know if Sybase has promised us (by way of
documentation,
etc.) that this is the way it will always work? My boss is
worried that it might not and may break if I do not take
harsher
measures to insure the order and IDENTITY are more
explicitly
associated.
Thank you in advance!
Regards,
WJ Barstad
Rob V [Sybase]
control how the values are assigned.
First, the documented behaviour is that all rows will get a unique number in
the identity column. What follows below works, but you cannot call Sybase
TechSupport and complain about it when the orderign works out differently
than you expect.
In practice the identity values are assigned in the order of the rows in the
table as they appear when you would do a serial (not parallel) table scan.
Things like overflow pages (for an APL clustered index) or forwarded rows
(for DOL tables) may give you a slightly different order than you'd probably
expect.
The only way to indirectly control the number assignment is to selet the
rows into a new table first, using an order-by clause. When you then add an
identty column to that newly created table, the identity numbers will be in
that order:
select *
into new_table
from old_table
order by a, b, c
alter table new_table add identity idcol numeric identity
Note that you can actually do this in one step as follows:
select *, idcol=identity(18)
into new_table
from old_table
order by a, b, c
Without an order-by, there is no guarantee that the identiy values will be
in the expected order.
If you start creating indexes or inserting/updating rows, beforer adding the
identity column, the orderingmay be affected too.
HTH,
Rob V.
-----------------------------------------------------------------
Rob Verschoor
Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase
Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"
mailto:r...@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------
"WJ Barstad" <wbar...@miqs.com> wrote in message
news:49b190d8.2f...@sybase.com...
Unknown
Thanks for your timely response!
To be clear: Are you saying that using an order by clause
on the select that creates the table DOES guarantee that
the values in the added IDENTITY column will be assigned
incrementally in the same order as the order by?
Thanks again!
WJB
> ------- Rob Verschoor
Rob V [Sybase]
the order by is sufficiently defined so as not to leave sets of rows whose
order is not defined by the order-by (i.e. include enough columns in the
order-by).
The safest way is to include the identity() function as part of a
select-into.
HTH,
Rob V.
"WJ Barstad" <wbar...@miqs.com> wrote in message
news:49b19e5b.3e...@sybase.com...
John Flynn
>> To be clear: Are you saying that using an order by clause
>> on the select that creates the table DOES guarantee that
>> the values in the added IDENTITY column will be assigned
>> incrementally in the same order as the order by?
>
> Yes -- provided you don't do any other processng in between...
I think you're confirming that in the following sequence:
> select *
> into new_table
> from old_table
> order by a
>
> alter table new_table add identity idcol numeric identity
... the resulting identity values will be in sequential order following the
order of column "a" (assuming of course that "a" is composed of unique
values). I believe you're saying this is "guaranteed". I was wondering, is
it guaranteed in the sense that Sybase promises it works that way? Or only
in the sense that it's been observed to work that way in some particular
versions of ASE?
Thanks.
- John.
Derek Asirvadem
(Not answering for Rob, here.)
Whether Sybase "guarantees" it or not, is no the issue. As per a
similar thread, if we are going to rely on it, we have to code for it,
and thus been immune to order changes, implicit operation, etc.)
Separately, this is exactly the kind of operation (eg. the recent 15.0
order by changes) that does change from version to version, and does
get corrected by subsequent ANSI SQL requirements after some vendor
implementation.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2009 Software Gems Pty Ltd
--
With the financial meltdown, consolidating many databases into one ASE
server and managing mixed load is a demand. Ask people who have been
doing it for years.
Rob V [Sybase]
"John Flynn" <jfl...@miqs.com> wrote in message
news:49b2f75c$1@forums-1-dub...
This works this way as a result of the way adding an identity column has
been implemented, irrespective of the ASE version. However, assigning
identity numbers in a particular row order,as discussed here, was never
defined to be a feature by itself and consequently, you won't find this
documented. Formally speaking, you are therefore relying on something that
Sybase does not explicitly support. In practice, I feel quite comfortable
using it however.
Again, you have to make sure the row order in the table is what you expect
them to be. When using table partitioning, that may not hold for example.
HTH,
Rob V.
-----------------------------------------------------------------