Facebook Sign in | Join
Pushing database changes needn't be hard work with SQL Compare
Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge 14 – Winning Solutions

We are just done with the evaluation of TSQL Challenge 14 and the results are published. We received a large number of solutions demonstrating different ways of solving the given problem.

And the top 3 winners are:

  1. Aurelien Verla
  2. Mark Cowne
  3. Adam Machanic

Please join us to congratulate the winners! Their solutions will certainly help to learn better ways of writing TSQL code and solving real-world problems more efficiently.  You can see a list of all the winners here.

The Number Table (or Tally Table)

As I had mentioned a few times in the past, a Number Table (also called as a Tally Table) is an essential tool for every database developer. It helps to solve a number of common TSQL problems using a SET based approach. All the three winning solutions used a tally table to produce the expected output.

Creating and using a Tally Table

If you don’t have a Tally Table in your database, probably it is time to start thinking about building one. There are many ways to create and populate a Tally Table. Some times, for smaller operations, developers even generate a Tally Table on the fly to get a certain operation done. This article might give you some ideas by showing a few different ways of generating a Tally Table.

If you don’t have a tally table, and wants to write a query that uses a Tally Table, you could either generate a Tally Table on the fly using a CTE or Recursive CTE or use the system table master.dbo.spt_values. You can apply a filter for “type = ‘P’” on master.dbo.spt_values to get a sequence of numbers. You might find two issues when trying to use master.dbo.spt_values. First, there may be cases where you dont’t have access to master.dbo.spt_values and the other is that the number of rows in master.dbo.spt_values is limited.

Both Aurelien and Mark used master.dbo.spt_values to solve TSQL Challenge 14. Adam, on the other hand, decided to build a Tally Table on the fly and used it. You can find Aurelien’s solution here, Mark’s here and Adams solution here.

Tally Table for TSQL Challenges

If you wish to use a Tally Table in your solutions for TSQL Challenges, you can take one of the options given below.

  1. Use master.dbo.spt_values, if the number of rows in spt_values is sufficient for solving the given problem. However, you need to make sure that your code will not break when we test it with a much larger set of data during our evaluation process.
  2. Create and use a Tally Table on the fly. Note that you are not allowed to create table variables, temporary tables or table valued functions in your solutions.
  3. The next and probably the best option is to create a Tally Table using the script given here. If you decide to go with this option, you need to take care of the following:
    1. The table should be created exactly as per the script given here.
    2. The name of the table should be “dbo.tsqlc_Tally
    3. You should not include the code to build this table as part of your solution. You can assume that this table is present in the target system where we evaluate your solutions.

TSQL Challenge 14 – Evaluation Summary

We received 128 submissions for TSQL Challenge 14. Out of that, 41 solutions were rejected due to one or more of the following reasons.

  • Using Temp Tables
  • Using Table Variables
  • Using Local Variables
  • Using WHILE Loops
  • Using Cursors
  • Using UDFs

Note that the challenges are 'always ON', which means that you can submit a solution at any time. You can submit a new solution even after the results are published. If your solution is disqualified, you can re-submit a new version of the challenge at any time.

87 Solutions went through the Basic Testing using the sample data given along with the challenge and only 62 of them passed the test. Those solutions were put through the Logic Testing and only 49 of them passed the test.

The solutions that passed the Logic Test were send to a Load Test with a large table having 100,000 rows. Here is the script that we used to generate the test data for Load Testing. The Load Testing was done with a timeout value of 5 minutes per solution. Solutions that took more than 5 minutes to execute were rejected. Only 20 solutions passed the Load Test and the performance statistics of those solutions are posted here.

Winning Solutions

Solutions that pass the Load Testing will be assigned a rank based on their performance. Based on the rank of each solution, we will consider the top 10 solutions as winning solutions. You can find the Winning Solutions of TSQL Challenge 14 here.

Resubmitting a new solution

Though the results of this challenge is published, you can still submit a new solution. We will evaluate your submissions periodically and will refresh the final results if one of those solutions are better than the solutions already published.

Join SQL Server Challenge Idea Contest

We would like to welcome you to the SQL Server Challenge Idea Contest 2010-Q1. Submit an interesting challenge idea or details of a TSQL problem that you have seen, heard or solved. Authors of winning solutions will get a cash prize of $100 and 10 SQL Stars. Read More >>



# re: TSQL Challenge 14 – Winning Solutions

Monday, December 21, 2009 10:39 AM by Jeff Moden

How do I leave a comment on the page that tells how to use a Recurrsive CTE to do things instead of using a Tally Table?  I've just got to warn people that it's probably ok for a one-off thing but it's a terrible practice for anything that will be in regular use because it's horrible for performance.

My recommendation:  Never use recurrsion for anything except maybe for resolution of hierarchies and, even then, there are better ways.

--Jeff Moden

# re: TSQL Challenge 14 – Winning Solutions

Monday, December 21, 2009 9:28 PM by Jacob Sebastian

Hi Jeff,

Which page are you referring to? Usually you get the option to post a comment if you are logged in.

# re: TSQL Challenge 14 – Winning Solutions

Monday, December 28, 2009 7:34 AM by rob_farley

I think you missed this row from the output:

4 4660046610375530309

# re: TSQL Challenge 14 – Winning Solutions

Wednesday, December 30, 2009 9:59 AM by Jacob Sebastian

Hi Rob,

did you intend to post this on Challenge #20?



Copyright © Rivera Informatic Private Ltd.