April 28, 2011 at 6:52 am
Curious how it somethimes takes a long time for the obvious to sink in! The random number generator below is used to create user accounts for our corporate databases.
In all the time it has been in use, a duplicate has never occurred but is possible with random number generation. Maybe we just got lucky or is SQLServer clever enough to realise what the sql is doing and give us unique numbers? Regardless, the routine is being amended for future use to reject any duplicates generated.
use useraccDB
go
--
declare @ctr int
declare @seed int
set @ctr =1
WHILE @ctr < 10
BEGIN
set @seed=@ctr + 2048
insert into pwdTbl
select 'DIOSC' +
cast(
cast(
RAND(@seed) *100000
as decimal (5,0)
)
as CHAR(5)
),
'LOCKED',
GETDATE(),
' ',
' ',
' ',
' ',
' ',
' '
;
set @ctr = @ctr + 1
END;
--
April 28, 2011 at 9:54 am
When running your code (eight consequtive runs) on SQL 2008 Developer edition as
CREATE TABLE #pwdTbl(Pwd Char(10),C2 CHAR(6),C3 DATE)
declare @ctr int
declare @seed int
set @ctr =1
WHILE @ctr < 10
BEGIN
set @seed=@ctr + 2048
insert into #pwdTbl
select 'DIOSC' +
cast(cast(RAND(@seed) *100000 as decimal (5,0)) as CHAR(5)),
'LOCKED',
GETDATE() --,' ',' ',' ',' ',' ',' ' --eliminated unnecessary columns
;
set @ctr = @ctr + 1
END
SELECT COUNT(pwd),pwd,C3 FROM #pwdTbl GROUP BY pwd,C3
--DROP TABLE #pwdTbl
Results:
(No column name)pwd C3
8 DIOSC75175 2011-04-28
8 DIOSC75177 2011-04-28
8 DIOSC75179 2011-04-28
8 DIOSC75181 2011-04-28
8 DIOSC75183 2011-04-28
8 DIOSC75185 2011-04-28
8 DIOSC75186 2011-04-28
8 DIOSC75188 2011-04-28
8 DIOSC75190 2011-04-28
As you can see, the code produces duplicates for each iteration. Are you sure you posted the correct code, otherwise I would have to say your server behaves very differently than what I would have expected.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply