The T-SQL Quiz
-
The % operator is in Books Online. The actual name is modulo (though most people know it as mod in part because many programming languages use MOD as the modulo operator). It is in the Books Online index under %.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Hello Robert,
Yes, I see it in the index. But it is almost impossible to find by searching for it - and because I did not know what the operator was (or that it was not listed in math functions because it is an operator, not a function), I did not know what entry in the index to consult. Had I checked the index as a last resort, though, I would have found it by luck since the operators are listed first.
Next time, knowing the limitations of the search tool, I will check the index if needed.
Thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
Hi webrunner,
2.b Even in your example you should be surprized, why charindex returns 2, and not
select CHARINDEX('.',1300001.0) which equals 8
It happens because decimal point in second place for scientific notation
To see it more clearly run the following
declare
@a float
set
@a = 3900003000000000
select
Results:
2 1.300001E+15
3. it was not about charindex.. you have used two variables inside the loop body. It will be nicer to call charindex there. In this case it will be 2 calls of charindex instead of 4
4. Sorry for being so hard on you.. I'm just tired of interviewing guys with certificats that doesn't prove anything. I agree with you, they should make it harder. IMHO, it is really strange that anyone can passed without knowledge of "%". It's not your fault, but the problem in certification process
Anyway, good luck with your career.
-
Hello Igor,
2.b Even in your example you should be surprized, why charindex returns 2, and not
select CHARINDEX('.',1300001.0) which equals 8
It happens because decimal point in second place for scientific notation
To see it more clearly run the following
declare
@a float
set
@a = 3900003000000000
select
Results:
2 1.300001E+15
I see your point now. It is a bug, not a scalability issue. Your example with the scientific notation made it more clear.
3. it was not about charindex.. you have used two variables inside the loop body. It will be nicer to call charindex there. In this case it will be 2 calls of charindex instead of 4
I think the two variables is the least of the problems with the code, but I see your point. It took 18 minutes and 48 seconds to run my code
, and I think the calls to CHARINDEX and CAST were probably causing the biggest performance hits. I still think Jeff's solution is the best - it has a worst case run of about 15 seconds, which is amazing, and it has a good explanation of why SQL programmers should always see whether a set-based solution would run better.I rewrote the code to include the % operator (see below), and it ran in 3:29. Still way too slow, but a lot better than 18 minutes!
/* Powered by General SQL Parser (www.sqlparser.com) */-- T-SQL solution to the BizzBuzz problem.
-- Loop from 1 to 1,000,000
-- If the number is evenly divisible by 3, print 'Bizz'
-- If the number is evenly divisible by 5, print 'Buzz'
-- If the number is evenly divisible by 3 and 5, print 'BizzBuzz'
-- Otherwise print the number.
DECLARE @count INT
SET @count = 1
WHILE @count <= 1000000
BEGIN
IF (@count%3 = 0
AND @count%5 = 0)
PRINT 'BizzBuzz'
ELSE
IF @count%3 = 0
PRINT 'Bizz'
ELSE
IF @count%5 = 0
PRINT 'Buzz'
ELSE
PRINT @count
SET @count = @count + 1
END
Thanks to Mike C for link to the SQL formatter. It is really nice.
4. Sorry for being so hard on you.. I'm just tired of interviewing guys with certificats that doesn't prove anything. I agree with you, they should make it harder. IMHO, it is really strange that anyone can passed without knowledge of "%". It's not your fault, but the problem in certification process
I think the test could be improved by having a section where the answers have to be submitted as code to be tested for efficiency and bugs and reviewed to see if it has documentation explaining what each part does and why. (No multiple choice or using a simulator.) That would make the tests more challenging and less susceptible to criticism that certification doesn't mean anything.
Anyway, good luck with your career.
Thanks.
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
Hello Jeff,
The results are almost identical, plus or minus 1 second. It does seem from this unscientific test that the varchar(8) version is a tiny bit faster than varchar(10) when running against a server. Not sure if you can see the same difference or if it is a result of differences in hardware or optimization.
Here is what I got, after discarding the first trial, on my local PC and then on a test server.
Run times are in seconds.
varchar(10)
local PC
trial 1: 6
trial 2: 6
trial 3: 6
trial 4: 5
server
trial 1: 14
trial 2: 9
trial 3: 8
trial 4: 19
varchar(8)
local PC
trial 1: 6
trial 2: 6
trial 3: 6
trial 4: 6
server
trial 1: 7
trial 2: 11
trial 3: 8
trial 4: 8
Thanks,
webrunner
P.S. I ran your code for a count of 10,000,000 and it ran in 3:10 - less time than it took my code to run for 1,000,000!
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
2.b "I see your point now. It is a bug, not a scalability issue. Your example with the scientific notation made it more clear."
It's not a bug. The problem is that you are relying on implicit conversion that have no contract whatsoever. The surprising results you got caused by the difference implicit conversion rules of IDE (in your case probably QA, in mine - SSMS) and server implicit conversion rules.
3. @count%15 instead of @count%3 = 0 AND @count%5 = 0) still could improve performance
-
Hello Igor,
It's not a bug. The problem is that you are relying on implicit conversion that have no contract whatsoever. The surprising results you got caused by the difference implicit conversion rules of IDE (in your case probably QA, in mine - SSMS) and server implicit conversion rules.
I don't follow. Are you saying that this is not a bug in my code? It seems to me like it is. Or did you think I meant that it is a bug in SQL Server? I wasn't saying that - I was saying that it is a bug in my code to rely on the decimal point, for two reasons - (1) 3900003000000000 is divisible by 15, so it is divisible by 3, so it should not have a decimal point at all if it is evenly divisible by 3. (2) When you originally said that this indicated that the code was not scalable I thought you meant for performance reasons. But if by not scalable you mean that after a certain number the format changes to scientific notation and the code returns the wrong result then that is a bug as far as I am concerned. But maybe I am still missing something about what you said.
3. @count%15 instead of @count%3 = 0 AND @count%5 = 0) still could improve performance
I ran the code with @count%15 and got results of 3:32 and 3:27. I don't know how much actual improvement it gives to this particular version. But thanks for the pointer.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
You are right
a) I got under impression that you meant that it is a bug in SQL Server
b) by "scalability" I meant that your code is not scalable for bigger N
Anyway, sorry I wasn't clear enough, English is my second language
-
Exactly...if you know that the 2 numbers you are "bizzing" and "buzzing" are prime numbers then the only time you would "BizzBuzz" is when it's evenly divisible by the product of the 2 prime numbers.
-
Hello Igor,
You are right
a) I got under impression that you meant that it is a bug in SQL Server
b) by "scalability" I meant that your code is not scalable for bigger N
Anyway, sorry I wasn't clear enough, English is my second language
No worries - when I read your post before this one I realized that perhaps I had not made it clear enough that I was talking about a bug in my code.
And, yes, the code (even as corrected by using %) is still not scalable for large values of @count.
But as I said before, I think it goes back to what the interview question is testing.
If the question is testing what it states at face value - Write code that counts from 1 to 100 For each number evenly divisible by 3, substitute 'Bizz' For each number evenly divisible by 5, substitute 'Buzz' For each number divisible by both substitute 'BizzBuzz' - then scalability for large N should not be factored into the assessment of the answer. Doing it for 100 should count as the correct answer even if the code works slowly for 1,000,000.
But if the question is testing how much initiative an applicant has, then the person administering the test can factor in whether or not the person went the extra step to make the code run quickly even if asked to count from 1 to 1,000,000.
Each employer has to make the decision for how to make that evaluation. Some might ask for efficiency and documentation up front; others may leave that out of the question to see which applicants do or do not add those features to their answer. Either way is fine by me as long as the employer has decided ahead of time what the criteria are and judges applicants fairly across the board.
I think that the employer has to decide how to respond when a person puts something into the answer that was not requested. Like anything else, depending on the situation it may or may not be desirable for the person to do something that is not specifically requested. Some employers may see it as a positive sign of initiative, while others may see it as a warning that the person may not stick to requirements.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
declare @i int
declare @ia int
declare @ib-2 int
declare @ic int
declare @startTime datetime
declare @endTime datetime
set @startTime = GETDATE()
set @i = 1
while @i <101
Begin
Set @ia = @i%3
Set @ib-2 = @i%5
Set @ic = @ia+@ib
if @ic=0 Print 'BIZZBUZZ'
else if @ia=0 Print 'BIZZ'
else if @ib-2=0 Print 'BUZZ'
else Print @i
Set @i = @i + 1
End
set @endTime = GETDATE()
Print 'Elapsed Time: ' + cast(DATEDIFF(ms,@startTime,@endTime) AS varchar) + ' ms'
Found this to be quick and easy.
-
-
Thank you very much... always good to see timings on these types of experiments even if they're "unscientific"
And thanks for the feedback on the 10,000,000 row run... --Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Found this to be quick and easy.
That's exactly what I've been ranting about
"Quick and Easy" is usually mutually exclusive with "Scalable and Fast"... try your code with the number 1000001 and lemme know how long it takes 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
BIZZ
BUZZ
Elapsed Time: 19393 ms
I just ran it
2 times less efficient that optimizied loop approach that use CASE
Still good enough
Viewing 15 posts - 121 through 135 (of 309 total)
You must be logged in to reply to this topic. Login to reply