Fun with RAND()

Question

Fun with RAND()

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Dave62 SSCertifiable Points: 7459 More actions · Answer 1

Brian.Klinect (11/5/2014)
I forgot RAND() was exclusive. So we can get .99999..., which multiplies to 9.999999..... I take it then that storing a floating point number as an integer just truncates in SQL?

This may be one of those "it depends" SQL Server answers.

Run this code and see if your results are the same as mine.

Declare @decFloat Float = 9.99999999999999;

Declare @intTest Integer = @decFloat;

Select @decFloat As [Float], @intTest As [Integer];

-- Returns: 9.999999999999999

Set @decFloat = 9.9999999999999999999999999;

Set @intTest = @decFloat;

Select @decFloat As [Float], @intTest As [Integer];

-- Returns: 109

Set @decFloat = 9.999999999999999;

Set @intTest = @decFloat;

Select @decFloat As [Float], @intTest As [Integer];

-- Returns: 1010It appears that sometimes we get rounding and sometimes truncating.

Enjoy!

h.tobisch SSCommitted Points: 1671 More actions · Answer 2

Do not like this question.

There is no economy.

What is actually used is the assignment to @intRND.

Chiefly Confused Ten Centuries Points: 1247 More actions · Answer 3

I also believe the answer is misleading. The select will return 1 row, 1 to 9 times, each one is a separate select of 1 row. A better question would ask how many times it would do a select, and how many rows were in each select, then we could determine the correct answer.

Miles Neale SSChampion Points: 13147 More actions · Answer 4

freeheel (11/5/2014)
alvertorave (11/5/2014)
I think the answer is wrong, you can only have 1 row, but you can execute the select 0 to 9 times returning a single row.
my thought exactly. i got it right, but ...

If you look at the question really critically it does not ask how many are selected. The question is "How many rows are returned?" The select is run 0-9 times, each select returns one row. So 0-9 rows are returned.

Not all gray hairs are Dinosaurs!

TomThomson SSC Guru Points: 104773 More actions · Answer 5

Good question.

I was careless, and ignored the implicit conversion of ((RAND() * 10) + 1) to int; since implicit conversion to int rounds towards 0 in SQL, and since RAND() delivers a positive value, that meant rounding down in this case but I just ignored rounding. So I picked the answer that would have been correct if @intRND had been declared as float instead of as int. I guess that was 4 glasses of red wine and a large brandy that went with lunch (I'm back in civilisation again, nowhere near England where that would be regarded as excess, and anyway it was my 70th birthday lunch). Without the alcohol I would probably have noticed that @intRND was a weird name for a float and might even have noticed that it was declared as an int :laugh:.

Tom

Marcia J SSCertifiable Points: 5649 More actions · Answer 6

Missed it due to carelessness. (Not noticing that it was < instead of <=). Well at least it taught me to pay more attention.

Good question.

Thomas Abraham SSChampion Points: 10761 More actions · Answer 7

TomThomson (11/5/2014)
Good question.
I was careless, and ignored the implicit conversion of ((RAND() * 10) + 1) to int; since implicit conversion to int rounds towards 0 in SQL, and since RAND() delivers a positive value, that meant rounding down in this case but I just ignored rounding. So I picked the answer that would have been correct if @intRND had been declared as float instead of as int. I guess that was 4 glasses of red wine and a large brandy that went with lunch (I'm back in civilisation again, nowhere near England where that would be regarded as excess, and anyway it was my 70th birthday lunch). Without the alcohol I would probably have noticed that @intRND was a weird name for a float and might even have noticed that it was declared as an int :laugh:.

Sounds like Tom's day was more fun than the question! (Which I liked very much.)

Hope you had a great B-Day Tom.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Thomas Abraham SSChampion Points: 10761 More actions · Answer 8

Chiefly Confused (11/5/2014)
I also believe the answer is misleading. The select will return 1 row, 1 to 9 times, each one is a separate select of 1 row. A better question would ask how many times it would do a select, and how many rows were in each select, then we could determine the correct answer.

If the return from RAND() is less than .1, that value times ten will truncate to 0. Adding 1 will yield intRND = 1.

Since intCounter starts at 1, the loop code won't execute. So, it could execute 0 to 9 times, not 1 to 9 times.

Also, the question asks "How many rows are returned?" Didn't say for each SELECT, so very reasonable to assume that all SELECTS should be considered in answer. However, I agree that the question could have been written more clearly.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Dave62 (11/5/2014)
Brian.Klinect (11/5/2014)
I forgot RAND() was exclusive. So we can get .99999..., which multiplies to 9.999999..... I take it then that storing a floating point number as an integer just truncates in SQL?
This may be one of those "it depends" SQL Server answers.
Run this code and see if your results are the same as mine.
Declare @decFloat Float = 9.99999999999999;
Declare @intTest Integer = @decFloat;
Select @decFloat As [Float], @intTest As [Integer];
-- Returns: 9.999999999999999
Set @decFloat = 9.9999999999999999999999999;
Set @intTest = @decFloat;
Select @decFloat As [Float], @intTest As [Integer];
-- Returns: 109
Set @decFloat = 9.999999999999999;
Set @intTest = @decFloat;
Select @decFloat As [Float], @intTest As [Integer];
-- Returns: 1010It appears that sometimes we get rounding and sometimes truncating.
Enjoy!

Well, the float is rounding up in the last case to 10. The assignment to int is merely the same since it is a whole number sans decimal.

In the case that the float does not round to the nearest whole number, then the assignment to int will round down to whole number that is less than the current float value. We can call that a truncate.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 10

results are different than the choices. in text mode its 9, in grid mode its 6.

Thanks.

Hany Helmy SSChampion Points: 13921 More actions · Answer 11

rabih_karam (11/5/2014)
Smart question :-):-)
thank you Dave
rabih

+ 1

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

SQL-DBA-01 (11/5/2014)
results are different than the choices. in text mode its 9, in grid mode its 6.

Execute the query a bunch.;-) See what you get each time you execute it.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 13

Thank you for the post. I tried it on sql 2012 and got 7 rows

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.