Identity column confusion

I have imported a pretty large table from Access to MSSQL 2K
I have a column that is a ID column, which is an int. If I try to set it to be an identity column, the sort in the table gets all messed up :-
If I add a new column and want to have that as the identity my sort gets messed up (sort is extremely important to me in this case)
Anybody has any idea why this would be happening? I am trying to add a column via Enterprise Manager
Thanks
Branka> If I try to set it to be an identity column, the sort in the table gets
all messed up :-(
That's because your table isn't "sorted" ... a table, by definition, is an
unordered set of rows. There is no relationship between application of an
IDENTITY addition and any conceptual sort order you might envision. You
might get lucky if you add a clustered index on your desired sort column(s),
and *then* apply the IDENTITY, but this behavior is not guaranteed -- purely
coincidental.
Also, are you going to do this over again, every time the data in your
desired sort column(s) change? What happens when you add a row, the new row
will have the highest IDENTITY value, but it will almost certainly no longer
fit your "very important" sort order.
Instead, I think you should generate this "rank" number at query time. See
http://www.aspfaq.com/2427 for some ideas.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||> I have a column that is a ID column, which is an int. If I try to set it
to be an identity column, the sort in the table gets all messed up :-(
Do you mean the ID values change or that you simply see the rows in a
different order? In SQL, tables have no logical ordering. If you want to see
the table in order use ORDER BY on your SELECT statement - that's the only
way to guarantee that the rows will be returned in a particular order.
> If I add a new column and want to have that as the identity my sort gets
messed up (sort is extremely important to me in this case).
I'm not sure what you're asking here. You can't have two IDENTITY columns in
a table, nor should that be necessary. If you mean that the sequence of
values in the column is important to you then don't use IDENTITY. You can't
control the order in which IDENTITY values are assigned to rows. The order
is indeterminate, there may be gaps and IDENTITY columns don't even have to
be unique (although typically they are assigned a UNIQUE constraint).
--
David Portas
--
Please reply only to the newsgroup
--|||> IDENTITY columns don't even have to be unique
That's a great point that I think a lot of people miss.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/