The T-SQL Quiz

Jeff Moden (5/28/2007)

Heh... looking at it the wrong way...

Original budget/deadline was wrong because you didn't bid right... Client necessarily changes scale... wants to know why idiots wrote code that wasn't scalable... all future contracts lost because client thinks people who don't have their best interest at heart wrote the code.

And, wanna tell be why the following would take more than 2 minutes? It's still documented , scalable, nasty fast, and it still uses setbased thinking... customer happy, inteviewer happy, boss happy, and my peers don't get the work because I had the for-thought to write code anticipating a change...

--===== Limit the number of rows to be built
SET ROWCOUNT 1000000

--===== Create and populate the table on the fly
SELECT IDENTITY(INT,1,1) AS RowNum,
CAST(NULL AS VARCHAR(10)) AS DesiredResult
INTO #Nums
FROM Master.dbo.SysColumns sc1 WITH (NOLOCK),
Master.dbo.SysColumns sc2 WITH (NOLOCK)

--===== Restore the number of rows to return to normal
-- Note: Can replace with TOP @variable in SQL 2k5
SET ROWCOUNT 0

--===== Produce the desired results according to the
-- requirements
SELECT CASE
WHEN RowNum % 15 = 0 THEN 'BizzBuzz' --Divisible by 15
WHEN RowNum % 3 = 0 THEN 'Bizz' --Divisible by 3
WHEN RowNum % 5 = 0 THEN 'Buzz' --Divisible by 5
ELSE CAST(RowNum AS VARCHAR(10))
END AS DesiredResult
FROM #Nums
ORDER BY RowNum

declare @x int, @r3 int, @r5 int
select @x = 1
select @r3 = 1
select @r5 = 1
while @x <= 1000000
begin
if (@r3=0 and @r5 = 0)
print 'BizzBuzz'
else if (@r3 = 0)
print 'Bizz'
else if (@r5 = 0)
print 'Buzz'
else
print @x

select @r3 = case when @r3 = 2 then 0 else @r3 + 1 end
select @r5 = case when @r5 = 4 then 0 else @r5 + 1 end
select @x = @x + 1

end
--13 secs

declare @x int, @r3 int, @r5 int
select @x = 1
select @r3 = 1
select @r5 = 1
while @x <= 1000000
begin
if (@r3=0 and @r5 = 0)
print 'BizzBuzz'
else if (@r3 = 0)
print 'Bizz'
else if (@r5 = 0)
print 'Buzz'
else
print @x

select @r3 = case when @r3 = 2 then 0 else @r3 + 1 end, @r5 = case when @r5 = 4 then 0 else @r5 + 1 end, @x = @x + 1
end
--7 seconds

SELECT TOP 1000000
CASE
WHEN ROW_NUMBER() OVER(ORDER BY GETDATE()) % 3 = 0 THEN 'Bizz'
WHEN ROW_NUMBER() OVER(ORDER BY GETDATE()) % 5 = 0THEN 'Buzz'
WHEN ROW_NUMBER() OVER(ORDER BY GETDATE()) % 3 = 0 AND ROW_NUMBER() OVER(ORDER BY GETDATE()) % 3 = 0 THEN 'BizzBuzz'
ELSE CAST(ROW_NUMBER() OVER(ORDER BY GETDATE()) as VARCHAR(10))
END
FROM Tally a, Tally b

-- Set output to "text"
;with
Numbers (Number, Mod3, Mod5, Mod15) as
(select top 1000000
cast(row_number() over (order by t1.object_id) as varchar(10)),
row_number() over (order by t1.object_id)%3,
row_number() over (order by t1.object_id)%5,
row_number() over (order by t1.object_id)%15
from sys.all_objects t1
cross join sys.all_objects t2)
select
case
when Mod15 = 0 then 'BizzBuzz'
when Mod5 = 0 then 'Buzz'
when Mod3 = 0 then 'Bizz'
else Number
end
from Numbers;

;with
Numbers (Number, Mod3, Mod5, Mod15) as
(select
cast(number as varchar(10)),
number%3,
number%5,
number%15
from dbo.numbers)
select
case
when Mod15 = 0 then 'BizzBuzz'
when Mod5 = 0 then 'Buzz'
when Mod3 = 0 then 'Bizz'
else Number
end
from Numbers;

if object_id(N'tempdb..#Numbers') is not null
drop table #Numbers

create table #Numbers (
Number int identity primary key,
Placeholder bit);

insert into #Numbers (Placeholder)
select top 1000000 null
from sys.all_objects t1
cross join sys.all_objects t2

-- Set output to text
;with
Numbers (Number, Mod3, Mod5, Mod15) as
(select
cast(number as varchar(10)),
number%3,
number%5,
number%15
from #Numbers)
select
case
when Mod15 = 0 then 'BizzBuzz'
when Mod5 = 0 then 'Buzz'
when Mod3 = 0 then 'Bizz'
else Number
end
from Numbers;