|
|
Posted by Erland Sommarskog on 03/11/06 20:34
David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> Tibor posted a repro that demonstrates the values are not always
> contiguous. Also there is a related problem with IGNORE_DUP_KEY, which
> causes gaps if rows are ignored.
>
http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/msg/
375cbb8f978decc9
Ah, that was interesting!
> This is easy to solve provided you have an alternate key. IDENTITY
> should not be the only key of a table and this is one example of why -
> without an alternate key you have no entity integrity so you cannot
> always guarantee reliable results from the data.
That is about as useful as saying "this is a good car, but you should
not drive it at night". If databases would only include data that
have natural keys, there wouldn't be much data in them.
What I didn't say in my first post, is that my take on this is usually
to not have IDENTITY on my surrogate key, but instead bounce data over
a temp table with IDENTITY, and then add that to a SELECT MAX(id)
from the target table. This comes with scaling problems obviously, but
that has not been an issue for me, luckily.
> In SQL Server 2005 you have a more concise alternative. Use the OUTPUT
> option:
>
> INSERT INTO tablemania (a, b)
> OUTPUT inserted.id, inserted.a, inserted.b
> SELECT a, b
> FROM othertable
> WHERE c > 15 ;
I will have to admit that I not really seen the point with the OUTPUT
clause for INSERT, but this an excellent use for it!
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|