Rolling dice in SQL


Gamer Life General Discussion

Silver Crusade

1 person marked this as a favorite.

I'll admit it: I browse these forums from work. I work in front of a computer, and my job frequently has me running computer programs and waiting a couple of minutes for a response, so I log in and chat about Pathfinder. More than once, I've found myself participating in an "in character" conversation where I've wanted to roll a d20 for something, but don't have one with me.

So I created one.

I primarly work with Microsoft SQL Server, and you'd think it would be easy to get a random integer out of it, but it's tougher than I expected. The built in random number generator gives random numbers between 0 and 1, out to about 16 decimal places. So you need to convert it to an integer to be useful, making sure you have an even distribution of the numbers you want.

So I googled it, figuring someone else must have done this at some point. Much to my surprise, a normally reliable SQL blog actually got it wrong. I wrote a quick program to loop their version 1000 times, and realized that it was coming up on 1 and 20 only about half as often as the other numbers. That's because he was rounding, which is a mistake. By converting the result to an integer using cast, you get a more even distribution of numbers.

So if anyone wants to roll a d20 in MS SQL Server, here's the command you need:

select cast(((20 * rand()) + 1) as int)

Obviously, change the 20 to whatever number you need if you want to roll d8, d10, d12, d100, d27, etc.

And here's my script to prove that it provides a relatively even distribution of random results after "rolling" 20,000 times:

Declare @N int
Set @N = 0

select top 0 @N as D20
into #Random

While @N < 20000
Begin
insert into #Random
select cast(((20 * rand()) + 1) as int) as D20

select @N = @N + 1
End

select D20, count(*)
from #Random
group by D20
order by D20


It's amazing; I have absolutely no idea what you just said. :)

Silver Crusade

The thread's for anyone who works with a SQL database, and has access to a command line in Microsoft SQL. Here's the part that matters:

Quote:

So if anyone wants to roll a d20 in MS SQL Server, here's the command you need:

select cast(((20 * rand()) + 1) as int)

If you don't know what SQL is, then you're in the wrong thread. :)

If anyone wants to know how to do this in other versions of SQL besides Microsoft SQL Server, then we can discuss it, but I might not be helpful.


No, I got it. Meaning, I knew what it was you were trying to accomplish, and I'm sure there are a number of people in the community that will absolutely be grateful for such a thing. I was just saying, at no point in any of what you typed there, did anything make any kind of coherent sense to me. :) I wasn't trying to say you shouldn't have posted it, or anything like that. Just that things like this make me realize just how dumb I really am. -_-


More T-SQL Fun:

Declare @N int
Set @N = 0

declare @Roll as int
Set @Roll = 0

Declare @NumberOfRolls as int
set @NumberOfRolls = 2000

Declare @Magnitude as int
if len(@NumberOfRolls) > 3
set @Magnitude = len(@NumberOfRolls)-3
else
set @Magnitude = 0

select top 0
@N as TwoD6
into
#Random

While @N < @NumberOfRolls
Begin
select @Roll = cast(((6 * rand()) + 1) + ((6 * rand()) + 1) as int)

insert into
#Random
select
@Roll as TwoD6

select @N = @N + 1
End

select
TwoD6,
count(*) as RollCount , replicate(']',count(*)/Power(10, @Magnitude)) as Graph
from
#Random
group by
TwoD6
order by
TwoD6

Enjoy!


I'll just leave this here.
http://pastebin.com/HyivKnTb


My eyes, my brain, my mind!


Trolling the archives for ancient posts be confused by, are we?

Community / Forums / Gamer Life / General Discussion / Rolling dice in SQL All Messageboards

Want to post a reply? Sign in.
Recent threads in General Discussion