|
|
Posted by Erland Sommarskog on 10/01/99 11:42
pb648174 (google@webpaul.net) writes:
> I guess what I'm getting at is that it is a heck of a lot easier to use
> an identity to refer to the row than the long description, especially
> within the application. Are you saying not to use the identity in
> application code anywhere and not in foreign keys or just not to
> display it to the end user?
Obviously a number is easier, and how it's generated it doesn't really
matter. There is no reason to be religious about IDENTITY. If that
works best for the task, use it. If doesn't, don't use it. Important
thing to know: never use it, if you need contiguous numbers.
Assigning your own keys without IDENTITY is very simple:
BEGIN TRANSACTION
SELECT @id = coalesce(MAX(id), 0) + 1
FROM tbl WITH (UPDLOCK, HOLDLOCK)
INSERT tbl (id, ...)
VALUES (@id, ....)
-- Other stuff as needed.
COMMIT TRANSACTION
This solution is not very good for high-transaction environments, but
your Projects table is never going to be close to that.
On the other hand, the above makes it easier to do mass inserts with
known values. You bounce the data over a temp table with an IDENTITY
column, and then insert as
INSERT tbl (id, ...
SELECT @nextid + identcol - 1, ...
It's all a matter of convenience. The idea that you should present a
value to the user only because it was generated with IDENTITY is very
difficult to take seriously. The only problem you could run into is
that user asks "by the way, why is there no project 22?", when an
IDENTITY values was consumed in a failed INSERT. Just answer with a
smile.
--
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]
|