I want to create an id number for an employee in a microsoft sql server 2000 database...?

Question

The id must be 12 numbers long, and it must be randomly generated. i am using the uniqueidentifer as the data type for the id number. its generating the number, but i want a 12 digit integer format. how do i do this? if not, what data type i should use, and what formula/default value i must use to generate the number? Please help me!!!

Answer 1

Whomever thought up the idea of generating an ID at random should have their head examined. Everytime the potential random number is generated, it would need to be compared against all previously generated values. If the number generated was not unique, it's back to the drawing board for as many times as is necessary. As the list of employees grows, the successive number of tries to generate a unique random number increases exponentially. Just go with an incrementing number, and pad it with zeros. Here is a table-definition script that creates an identity-column on a bigint datatype, and a pair of triggers that left-pads a string-version of the identity column with as many zeros as necessary to bring it to 12 digits.

CREATE TABLE [xt_Employees] (
[Emp01] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ID] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[StrID] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO




create TRIGGER [xtr_01] ON [xt_Employees] FOR INSERT

as
declare @pStrID varchar(12)

begin
select @pStrID = rtrim(cast(ID as varchar)) from xt_Employees


select @pStrID = replicate('0', 12 - len(@pStrID)) + @pStrID

update xt_employees set strID = @pStrID

end


GO

create TRIGGER [xtr_02] ON [xt_Employees] FOR UPDATe

as
declare @pStrID varchar(12)

begin
select @pStrID = rtrim(cast(ID as varchar)) from xt_Employees

select @pStrID = replicate('0', 12 - len(@pStrID)) + @pStrID


update xt_employees set strID = @pStrID

end

GO

Answer 2

I was trying to imagine which data type would be proper, maybe bigint cause its range is from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
The random generated value I don't think u could make it in the table design, I think you could make a trigger to generate that value when a record insert event occurs...