August 15, 2008 at 9:52 am
This raises an interesting question: how quickly can we generate an acceptably random pseudo-random sequence in SQL Server, without resorting the obvious built-in functions (rand, NewID, Checksum, the crypto functions, etc.)?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 15, 2008 at 10:02 am
rbarryyoung (8/15/2008)
This raises an interesting question: how quickly can we generate an acceptably random pseudo-random sequence in SQL Server, without resorting the obvious built-in functions (rand, NewID, Checksum, the crypto functions, etc.)?
LOL...if we are going to challenge ourselves to not use the tools we are given....
the next challenges will be:
selecting data without the word SELECT in the statement,
updating data without the word UPDATE in the statement and
deleting data without the word DELETEin the statement.
Lowell
August 15, 2008 at 11:03 am
Lowell (8/15/2008)
rbarryyoung (8/15/2008)
This raises an interesting question: how quickly can we generate an acceptably random pseudo-random sequence in SQL Server, without resorting the obvious built-in functions (rand, NewID, Checksum, the crypto functions, etc.)?LOL...if we are going to challenge ourselves to not use the tools we are given....
the next challenges will be:
selecting data without the word SELECT in the statement,
updating data without the word UPDATE in the statement and
deleting data without the word DELETEin the statement.
In terms of serious application, I absolutely agree with you Lowell.
However, in terms of interest, I think that it is always good to know different ways to do things and to learn what their advantages and disadvantages are. We might be able to get a running count today just by doing:
Select *, ROW_NUMBER() Over(Order by PK) From FooTable
but think about how much we learned by trying to solve that problem when we didn't have 2005's windowing functions? All of those tips, tricks and tools developed to address that problem are still usable today for a variety of different problems.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 15, 2008 at 12:39 pm
Create a 3 column table
create table Random (number int, Entropy bigint, status tiny)
insert 0-9999 into number
update Entropy with select convert(bigint,HashBytes('SHA1', convert(varchar(255), number )+convert(varchar, getdate(),121) ))
whenever you select a 'random' number from this table, order by Entropy select top 1 and set status to 0 so that you never reselect that number again.
August 15, 2008 at 12:46 pm
bcronce (8/15/2008)
update Entropy with select convert(bigint,HashBytes('SHA1', convert(varchar(255), number )+convert(varchar, getdate(),121) ))
Hmm, I thought the Crypto functions were off the table?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 15, 2008 at 2:46 pm
rbarryyoung (8/15/2008)
This raises an interesting question: how quickly can we generate an acceptably random pseudo-random sequence in SQL Server, without resorting the obvious built-in functions (rand, NewID, Checksum, the crypto functions, etc.)?
How about this:
drop table #T
create table #T (
ID int identity primary key nonclustered,
Timestamp,
Random as cast(timestamp as int)%datepart(millisecond, getdate())%datepart(second, getdate()),
Used bit not null default(0))
set nocount on
go
insert into #T
default values
go 100
declare @Num int
select @num = id
from #T
where Used = 0
order by random
update #T
set Used = 1
where id = @Num
select @Num
go 5
Since the Order By column is calculated, and changes every time the select is run because of the getdate() parts, it's pretty "pseudo-random", as far as sequence goes.
Of course, it can only be run 100 times before it runs out of numbers. 10k times if we expand the insert into the table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 20, 2008 at 12:42 am
How about this?
create table #temp_table
(
ID int identity primary key,
number int
)
GO
declare @ss int
declare @ms int
declare @rand int
select @ss = datepart(ss, getdate())
select @ms = datepart(ms, getdate())
select @ss = case when @ss <10 then @ss * 100 else @ss end
select @ms = case when @ms <10 then @ms * 100 else @ms end
select @ms = case when @ms >100 then @ms / 10 else @ms end
select @rand = case when @ms * @ss < 999 then (@ms * @ss*10)
when @ms * @ss > 9999 then (@ms * @ss)/10
else @ms * @ss end
while exists(select * from #temp_table where number = @rand)
begin
select @rand = case when @ms * @ss < 999 then (@ms * @ss*10) else @ms * @ss end
end
insert into #temp_table select @rand
select @rand
GO
August 20, 2008 at 2:21 am
Actually, it seems to me that what the original poster wanted could be more appropriately defined as random ordering than generating a random number.
- there is a predefined set of possible values (0001-9999)
- "the numbers should not repeat" is a strange requirement, but maybe we should understand it as "no number may repeat, before all other numbers from the set have been used". No matter how you explain this requirement, it isn't RANDOM generation. If you want to use the word "random", then it can be at best random ordering.
We don't know what should happen once we reach the end of the set (all numbers have been used once)... either this is the end and we don't have to bother about anything, or we have to continue somehow (restart the counter of used values, or empty the table which holds pre-generated random order of values and fill it again).
As this seems to be orphaned question, I'm afraid we'll never know how it was meant originally and we can only guess and play around with numbers - but not solve it.
August 20, 2008 at 7:14 am
I was thinking along similar lines, vladan.
It seems to me that the OP was confabulating two contradictory requirements in PRNG's (Pseudo-Random Number Generators), the "Uniform Coverage" requirement and the "Long Period" requirement.
The Uniform Coverage criteria requires that if our output range is of size R (that is, 1 to R, or 0 to R-1, etc.) then in R successive calls to our PRNG, we will produce all R possible values once and only once (and still have a high quality of pseudo-randomness). This is a well known criteria and there are many long-established ways to do it, the venerable "linear congruential generator" being the most traditional and simplest method, if of questionable quality these days.
On the other hand, the Long-Period criteria requires that is our output range is of size R, then for successive calls of our PRNG, we will not repeat our sequence of values for some period P where P > R. Usually P is much greater than R, as in at least R2. Sometimes P is so large that it is described as never repeating although I believe an infinite/unlimited period is impossible for a PRNG (because it would require the maintenance of an infinite-sized state).
While the Long-Period criteria has not been around as long as the Uniform Coverage criteria (say, 35 years vs. 45 years) it is also well known with long established methods for achieving it (as well as many new ones). What is less clear is that they are also fundamentally contradictory requirements, in large part because as you noted, vladan, Uniform Coverage is not truly random (or rather, pseudo-random). And although it is certainly possible to construct a generator that satisfies both, it would probably be both fairly inefficient and of questionable quality.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 21, 2008 at 6:46 am
If there is no pressing need to generate the numbers in SQL consider generating them in code
I found that the rand() function would repeat numbers, defeating the purpose of random number generation
August 21, 2008 at 7:55 am
Dear all,
I'm sorry for responding to your replies and i also please you for not clearly explaining my requirement
i have incorparated the following logic to generate the random number
create table test(id varchar(20)
create procedure random
(
@vendor_name varchar(50),
@company_code varchar(50)
)
as
begin
declare @testvar varchar(15)
declare @len int
select @testvar = upper(substring(@vendor_name,1,5) +
substring(@company_code,1,5)+
substring (convert(varchar(20),getdate(),112),5,9))
set @len=len(@testvar)
--print @testvar
if not exists(select 1 from test where substring(id,1,@len) = @testvar)
begin
insert into test select
upper(substring(@vendor_name,1,5)+
substring(@company_code,1,5)+
substring (convert(varchar(20),getdate(),112),5,9)+
+ right('0001',4))
end
else
begin
declare @l_num varchar(20)
select @l_num=right(id,4) from test
set @l_num=@l_num+1
--select right('0000'+convert(varchar(20),@l_num),4)
insert into test select upper(substring(@vendor_name,1,5)+
substring(@company_code,1,5)+
substring (convert(varchar(20),getdate(),112),5,9)+
right('0000'+convert(varchar(20),@l_num),4))
end
end
random1 'Test', 'test'
select * from test
Thanks
Chandru
August 21, 2008 at 7:59 am
Seggerman (8/21/2008)
If there is no pressing need to generate the numbers in SQL consider generating them in codeI found that the rand() function would repeat numbers, defeating the purpose of random number generation
I'm not sure if you've been following along here, but what you describe is A) impossible and B) not the purpose of a pseudo-random number generator.
Specifically, if your output range is {0 to 9999}, then by your 10,001st number generated you must have repeated at least one number.
Plus, insuring that you do not repeat any numbers until your 10,001st is not very random.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 21, 2008 at 8:06 am
Chandru: can you explain how the code you are using meets your needs? It could be of help to others in the future.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 21, 2008 at 8:26 am
Sure .......
Hope this helps for someone
My requirement is i want to generate the tracking number in random order
based on the vendor name and company code that too if a different vendors
are logging in means i want to generate the number from 0001.
--assign the track number to a local variable
declare @testvar varchar(15)
declare @len int
select @testvar = upper(substring(@vendor_name,1,5) +
substring(@company_code,1,5)+
substring (convert(varchar(20),getdate(),112),5,9))
set @len=len(@testvar)
--check the local variable value with the value present in test table
--if not generate track number in following logic
if not exists(select 1 from test where substring(id,1,@len) = @testvar)
insert into test select
upper(substring(@vendor_name,1,5)+
substring(@company_code,1,5)+
substring (convert(varchar(20),getdate(),112),5,9)+
+ right('0001',4))
--if the track number is present in test table generate the
--tracking number in following logic
declare @l_num varchar(20)
select @l_num=right(id,4) from test
set @l_num=@l_num+1
--select right('0000'+convert(varchar(20),@l_num),4)
insert into test select upper(substring(@vendor_name,1,5)+
substring(@company_code,1,5)+
substring (convert(varchar(20),getdate(),112),5,9)+
right('0000'+convert(varchar(20),@l_num),4))
Thanks
Chandru.
August 21, 2008 at 9:02 am
I tried running your earlier code and from how it behaves I think you are not really interested in random numbers. I think what you want is sequential numbers for each unique vendor-company combination that arrives.
Thus if vendor V1 and company C1 have occured already 3 times you would have records
V1,C1,0001
V1,C1,0002
V1,C1,0003
and what you now want is to generate record
V1,C1,0004
If vendor V1 and company C1 has never occured you want to generate record
V1,C1,0001
Is this a correct description of your requirements?
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply