D
Don
I am in the midst of migrating a database from Access to MS SQL Server and
fixing all the little problems with forms, reports, etc. In the original
database, the employee number was simply an AutoNumber field with new values
generated randomly. Since the 'employee number' was only used within the
database for linking tables, the fact it was a random integer was not
visible to database users or employees.
With the migration to MS SQL Server, the closest datatype to a LongInt
AutoNumber is the uniqueidentifier (GUID). From a high level perspective,
not a bad solution. However, some of the database's existing forms and
reports (event code) use the EmployeeID. This is not an issue when the
EmployeeID is a LongInt, but is problematic if the EmployeeID is a GUID.
This problem is discussed in http://www.trigeminal.com/usenet/usenet011.asp.
Revamping the EmployeeID an SQL bigint and setting it as an identity seems
like one solution to the problem. However, the examples for identity
columns in 'Inside MS SQL Server 2000' (Delaney) lead me to believe that
identity columns are really more for temporary table manipulation.
Since I need to either come up with my own random EmployeeID generation
function (checking to see if the value is already used) or simply rebuild
the EmployeeID numbers, I realize I am in for some work. My question boils
down to "What is typical practice in this situation?"
On the surface, the identity column approach eliminates (should) the
possibility of duplicate EmployeeIDs (in the case of two users entering
employees simultaneously. But I do not want to be miss using the identity
column approach.
Finding the largest existing EmployeeID (assuming renumbering them from one
as bigints), and adding one is the simplest but raises the issue of a race
condition of simultaneous adds. Maybe addressing this with a short term,
absolute lock on the table?
Any comments, suggestions, ideas, pointers to references, etc will be
greatly appreciated!
Thanks!
Don
fixing all the little problems with forms, reports, etc. In the original
database, the employee number was simply an AutoNumber field with new values
generated randomly. Since the 'employee number' was only used within the
database for linking tables, the fact it was a random integer was not
visible to database users or employees.
With the migration to MS SQL Server, the closest datatype to a LongInt
AutoNumber is the uniqueidentifier (GUID). From a high level perspective,
not a bad solution. However, some of the database's existing forms and
reports (event code) use the EmployeeID. This is not an issue when the
EmployeeID is a LongInt, but is problematic if the EmployeeID is a GUID.
This problem is discussed in http://www.trigeminal.com/usenet/usenet011.asp.
Revamping the EmployeeID an SQL bigint and setting it as an identity seems
like one solution to the problem. However, the examples for identity
columns in 'Inside MS SQL Server 2000' (Delaney) lead me to believe that
identity columns are really more for temporary table manipulation.
Since I need to either come up with my own random EmployeeID generation
function (checking to see if the value is already used) or simply rebuild
the EmployeeID numbers, I realize I am in for some work. My question boils
down to "What is typical practice in this situation?"
On the surface, the identity column approach eliminates (should) the
possibility of duplicate EmployeeIDs (in the case of two users entering
employees simultaneously. But I do not want to be miss using the identity
column approach.
Finding the largest existing EmployeeID (assuming renumbering them from one
as bigints), and adding one is the simplest but raises the issue of a race
condition of simultaneous adds. Maybe addressing this with a short term,
absolute lock on the table?
Any comments, suggestions, ideas, pointers to references, etc will be
greatly appreciated!
Thanks!
Don