We debated using NEWID, but decided the extra typing when using SQL to trace
items was too much of a downside :-)
We decided to use a central Reference Generator as we have implemented
generic list tables that are related to a number of other tables, and the id
column can be called through outer joins without having to use a compund
key.
Stephen
-----Original Message-----
From: Paul Ritchie [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, 6 March 2001 12:22 p.m.
To: Multiple recipients of list database
Subject: RE: [DUG-DB]: Getting unique ID through SQL
I've never used it, but NEWID() produces a value of type uniqueidentfier
which is 16 bytes long whereas an integer is 4 bytes.
It depends on whether you want the record to be uniquely identified in just
that table or the entire world I guess, but I would have thought most
databases supporting entity and referential integrity internally should use
a primary key on an integer IDENTITY field.
On the rare occasions when I need to know the ID I have just added I use the
@@identity function Eg for my Clients table:
CREATE TABLE [dbo].[Clients] (
[ClientID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50),
[ExternalID] [int] NULL
) ON [PRIMARY]
END
When inserting a new client:
DECALRE @ClientID int
INSERT INTO Clients (Name) VALUES (@Name)
SET @ClientID = @@IDENTITY
Also, from the SQL Server Books Online on uniqueidentifier types:
********************
The main advantage of the uniqueidentifier data type is that the values
generated by the Transact-SQL NEWID function or the application GUID
functions are guaranteed to be unique throughout the world.
The uniqueidentifier data type has several disadvantages:
* The values are long and obscure. This makes them difficult for users to
type correctly, and more difficult for users to remember.
* The values are random and cannot accept any patterns that may make them
more meaningful to users.
* There is no way to determine the sequence in which uniqueidentifier values
were generated. They are not suited for existing applications that depend on
incrementing key values serially.
* At 16 bytes, the uniqueidentifier data type is relatively large compared
to other data types such as 4-byte integers. This means indexes built using
uniqueidentifier keys may be relatively slower than implementing the indexes
using an int key.
Consider using the IDENTITY property when global uniqueness is not
necessary, or when having a serially incrementing key is desirable.
********************
HTH
Paul Ritchie
Radio Computing Services.
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, 7 March 2001 12:49 a.m.
> To: Multiple recipients of list database
> Subject: RE: [DUG-DB]: Getting unique ID through SQL
>
>
> Just a By-the-By....if no one knew this, there is a very cool
> function in SQL Server 7 called NEWID
>
> -- Creating a local variable with DECLARE/SET syntax.
>
> DECLARE @myid uniqueidentifier
>
> SET @myid = NEWID()
>
> PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid)
>
> this really does work. we use it now more and more, and is
> more reliable that a table that stores the last number, and
> adding one to it !
>
>
> Cheers Jeremy Coulter
>
>
>
>
> --------------------------------------------------------------
> -------------
> New Zealand Delphi Users group - Database List -
> [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
