Autonumber VS Function Generate Id

[Adhie]

Hi,

In SQL Server, one of the field, we set ID as primary key of the table. To make sure that field is unique there are 2 ways that I can approach:
(1) Set Identity as Yes, Identity Seed and Identity Increment as well, so we call it as Autonumber (same case in Access DB).
(2) We create a function to produce unique Id:

'---- Auto Number Increment ----
Function AutoNumInc(TableName, IncRec)
Set RSAutoNumInc = Server.CreateObject("ADODB.Recordset&quot
RSAutoNumInc.Open "SELECT Max("& IncRec &&quot AS MaxRec FROM "& TableName, Conn,3, 1, 0
If isNull(RSAutoNumInc("MaxRec&quot) Then
AutoNumInc = 1
Else
AutoNumInc = CInt(RSAutoNumInc("MaxRec&quot) + 1
End If
RSAutoNumInc.Close
Set RSAutoNumInc = Nothing
End Function


Which one you think is the best way if we're going to create centralize database?

Thanks you,
Martin

 

[Rhys666]

Use Identity, no errors caused by concurrent updates and SELECT @@Identity after insert returns Last Identit number generated if required...
I.E.,
<CODE Sample From MS SQL Documentation>
INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'
</CODE>


Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[SQLSister]

Agree with Rhys666. I prefere to use identity fields as well. No code to break, no problems with concurrent requests for the last number used, integer joins are faster than alphanumeric joins (and if you are not going with an alpha numeric key then why create it yourself when SQL will do it with no fuss, no muss?), easier to maintain and probably faster inserts and you can insert multiple records without the use of a cursor. I've read that some people think they will have locking problems if they use identity fields, but personally I've not run into this.

Incidentally be wary of the use of @@identity. It will in fact give you the last one generated which may not be the one related to the record you are interested in if concurrent users are entering records. If you are getting it in order to add records to a related table using the identity field as a key, use SCOPE_IDENTITY( ) instead as it is limited to the current scope and will give you the identity of the record you just inserted.