The T-SQL Quiz

Question

The T-SQL Quiz

Viewing 15 posts - 16 through 30 (of 309 total)

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

duanxn-306048 Grasshopper Points: 11 More actions · Answer 1

Ok, fed up reading the thread. I would have failed all the interviewers just simplely in comparing the following two code pieces:

--====== A ========

Case when @i % 3 = 0 and @i % 5 = 0 then 'BizzBuzz'

when @i % 3 = 0 then 'Bizz'
when @i % 5 = 0 then 'Buzz'

else Str(@i)

End

--====== B ========

Case when @i % 5 = 0

case when @i % 3 = 0 then 'BizzBuzz' else 'Buzz' end
when @i % 3 = 0 then 'Bizz'
else Str(@i)

End

The method B will save half of the modular calculation!

George Ndicu Grasshopper Points: 10 More actions · Answer 2

Hi All

Below is my entry

declare @count int, @message nvarchar(10), @message1 nvarchar(10), @message2 nvarchar(10)

set @count = 1

while @count <= 100

BEGIN

set @message = '' set @message1 = '' set @message2 = ''

IF @count%3 = 0

set @message1 = 'BIZZ'

IF @count%5 = 0

set @message2 = 'BUZZ'

set @message = @message1 + @message2

select @count, @message

set @count = @count + 1

END

sorry i have to get back to work so i did not check how quickly it run

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Heh... you read into it, ol' friend... no where in the post does there say anything about a time limit.

[Edit] Ok, I see where you came up with the 2 minute limit... wasn't in Grant's article but in one of the links provided...

If there were a 2 minute time limit for such a thing and it was during an interview, then yes... skip the comments 'cause that's not what they're looking for, but consider finding a job somewhere else because they've just set the stage for how they're gonna operate all the time.

So far as the total number being limited by some business logic... how many times has that happened to you only to have that business logic change?

I'll say it again, write performance enabled, scalable, documented code even for the simplest things... unless you really enjoy treading water

--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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

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

--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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

And, you left out a THEN necessary to make it work And, although you think B will save half of the modular calculation, the code runs at the same speed as what you have for A.

--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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

I think I've found one of the articles that started this whole BizzBuzz line of questioning at...

http://tickletux.wordpress.com/2007/01/24/using-fizzbuzz-to-find-developers-who-grok-coding/

In that article, the author summarizes with the following... my responses are in black...

"Most good programmers should be able to write out on paper a program which does this in a under a couple of minutes.

Absolutely agree...

Want to know something scary ? - the majority of comp sci graduates can’t. I’ve also seen self-proclaimed senior programmers take more than 10-15 minutes to write a solution.

Yep... have seen that and worse...

I’m not saying these people can’t write good code, but to do so they’ll take a lot longer to ship it. And in a business environment that’s exactly what you don’t want.

Wrong! If it takes a bit longer to write scalable code that won't need to be changed, it's worth and extra minute or two for a piece of code this simple. The client will love the performance of the code and if they don't appreciate it, your company will because too much code comes back from the client saying "how come this is broken?" The people doing the bidding need to learn to bid good code.

This sort of question won’t identify great programmers, but it will identify the weak ones. And that’s definitely a step in the right direction."

Wrong again... the great programmers won't use a loop and they'll write it for scalablity, performance, readability, etc, etc. And, it won't take them much longer than writing undocumented, slow moving, garbage code. When I conduct interviews, I'm looking for the great programmers.

In fact, my instructions to the prospective employee for this test would have been to write the code for the highest performance possible with comments that explain what they're doing and that although the time it takes for them to do it is important, performance and scalability of the code and their ability to explain/document what they've done is much more important.

--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)

Roger L Reid SSCrazy Points: 2004 More actions · Answer 7

My reaction if asked to do this in an interview? I'd do it, but not until I made the point that there are much more appropriate languages for this job.

S.Q.L. is poor enough as a DML for relational databases (very nice, though, how MS has applied it to other things in the OS like logs. Much better for "flat" data).

Now to prove that I can write a really bad procedural program when the procedural stuff is all held onto the language with duct tape? Gevalt!

In an interview, I would not hold it against the candidate to not challange this. But if I said it to one of my programmers, I'd expect them to give me a strange look and say: "why?!"

At no point have you proven anything about the candidate's ability to use S.Q.L. to manipulate relational data. Better to ask them to write this as a procedure and tell them that you'll pass the values in as parameters - and then operate on some tables.

Roger L Reid

Sergiy SSC Guru Points: 110208 More actions · Answer 8

> Wrong! If it takes a bit longer to write scalable code that won't need to be changed, it's worth and extra minute or two for a piece of code this simple. The client will love the performance of the code and if they don't appreciate it, your company will because too much code comes back from the client saying "how come this is broken?" The people doing the bidding need to learn to bid good code.

------

Absolutely!

I'm probably the slowest typer around, and other guys can de;iver some kind of working solution by the time I'm sitting and staring into screen doing nothing (actually, thinking about proper design, but who can see it? ), but it's me who they call to fix their "quick" solutions when project finds its dead end.

> When I conduct interviews, I'm looking for the great programmers.

Unfortunately, they look for MS certifications.

And it's not easy to find worse programmers than MS certified ones.

In fact, the worst programmer in our company is the only one who's got MS certification.

Funny? Or not really?

_____________
Code for TallyGenerator

Adam Machanic SSCoach Points: 15370 More actions · Answer 9

Don't completely dismiss solutions that use a loop. The following SQLCLR stored proc beats your method by about 10% on my laptop.

And by the way, if I saw anyone in an interview spending nearly as much time thinking about this as we all have in this thread, I would probably assume that they would never get any work done by deadline, and would not hire them. Performance certainly has its place, but premature optimization is a huge time killer and I'd rather hire someone who is smart about when to apply performance optimization than someone who thinks they need to shave a few microseconds off of everything even if it means that the resultant code will be far less maintainable.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void bizzbuzz()
    {
        SqlMetaData[] md = new SqlMetaData[1];
        md[0] = new SqlMetaData("n", SqlDbType.NVarChar, 10);

        SqlDataRecord dr = new SqlDataRecord(md);

        SqlDataRecord dr1 = new SqlDataRecord(md);
        dr1.SetString(0, "bizzbuzz");
        SqlDataRecord dr2 = new SqlDataRecord(md);
        dr2.SetString(0, "bizz");
        SqlDataRecord dr3 = new SqlDataRecord(md);
        dr3.SetString(0, "buzz");

        SqlContext.Pipe.SendResultsStart(dr);

        for (int i = 1; i <= 1000000; i++)
        {
            if ((i % 15) == 0)
                SqlContext.Pipe.SendResultsRow(dr1);
            else if ((i % 3) == 0)
                SqlContext.Pipe.SendResultsRow(dr2);
            else if ((i % 5) == 0)
                SqlContext.Pipe.SendResultsRow(dr3);
            else
            {
                dr.SetString(0, i.ToString());
                SqlContext.Pipe.SendResultsRow(dr);
            }
        }

        SqlContext.Pipe.SendResultsEnd();
    }
};

--
Adam Machanic
whoisactive

Sergiy SSC Guru Points: 110208 More actions · Answer 10

Are you sure the code you wrote is written in SQL?

And front end envionmen is single user one.

That's a difference between it and databases.

Try to imitate 10 users run and compare performance. See the difference.

And did you include creation of Numbers table into the test? Remember, it suppose to be one-off task, ones it's created it must be used by all users without recreating it again and again.

_____________
Code for TallyGenerator

Adam Machanic SSCoach Points: 15370 More actions · Answer 11

I guess I stretched the requirements to "runs in SQL Server" ... why limit yourself to T-SQL if other, better options exist?

Note that the SQLCLR solution will easily outscale any solution involving a temp table, because it uses a lot less memory. I did come up with a faster, pure T-SQL solution (following, and it uses no temp table or table of numbers so will probably scale equally as well as the SQLCLR solution), but the point of my post was that loops can outperform set-based solutions, even in SQL Server (but perhaps not in pure T-SQL)...

with a (n) as
(
select 1
union all
select 2
),
b as
(
select a.* 
from a, a b
),
c as
(
select b.*
from b, b c
),
d as
(
select c.*
from c, c d
),
e as
(
select d.*
from d, d e
),
f as
(
select row_number() over (order by e.n) as n
from e, e f
)
select 
case 
when n % 15 = 0 then 'bizzbuzz'
when n % 3 = 0 then 'bizz'
when n % 5 = 0 then 'buzz'
else convert(varchar, n)
end
from f
where n <= 1000000

--
Adam Machanic
whoisactive

Sergiy SSC Guru Points: 110208 More actions · Answer 12

Unfortunately your solution does not run in SQL Server, at least in any of SQL Server installations we use for real product development.

SQL Server 2005 is not still an industry standard.

And SQLCLR is not SQL, I don't tell about "pure" SQL, I tell about language standards.

And CLR solutions always perform worse than "pure" SQL ones. It was proven in many tests, here and there.

> but the point of my post was that loops can outperform set-based solutions

I know, and there was a big discussion about best option of "split" function, and my version using loop outperformed Jeff's Numbers table solution.

I can tell you why. Because every bloody laptop is equipped with latest double-core huge GHz CPU and single hard drive from last century technology. That's why on your laptop "CPU heavy" loop solution outperforms table solution which involves disk operations.

BUT!

Do you know what does "SQL" stands for?

It's "Structured Query Language".

So, if you've been asked to write SQL solution you meant to write STRUCTURED QUERIES.

And loops don't belong here. Sorry.

So, Jeff's solution (as well as mine, they use the same approach) is only SQL solution amongst ones shown in the article and in this thread.

_____________
Code for TallyGenerator

Adam Machanic SSCoach Points: 15370 More actions · Answer 13

Oh, sorry, I didn't realize that the solution had to work on YOUR servers. I'll make sure to read between the lines next time!

--
Adam Machanic
whoisactive

Sergiy SSC Guru Points: 110208 More actions · Answer 14

If you don't specify the version that means it must work on EVERY version, not only the one you know.

_____________
Code for TallyGenerator

Adam Machanic SSCoach Points: 15370 More actions · Answer 15

Great, thanks for setting me straight!

--
Adam Machanic
whoisactive