TSQL Challenges

TSQL Challenges 16 – Accepted Solutions

This is to give a quick update to those of you who are eagerly waiting for the results of the previous challenges. All the solutions we have received so far for challenge 16 are listed at http://databasechallenges.com/SQLServer/TSQL/Challenge16/Submissions. A few submissions were rejected because they used #temp tables, table variables or local variables.

The list of solutions accepted for further processing are listed at http://databasechallenges.com/SQLServer/TSQL/Challenge16/accepted. If you see your solution in ‘all solutions’ but do not see it in ‘accepted solutions’ it means that your solution is rejected due to one of the reasons given above. If you think that your solution does not fall into the categories I mentioned earlier, and still not listed in ‘accepted solutions’ please let us know and we will double check what is wrong.

While the evaluation of TSQL Challenge 16 is still in progress, we started also the evaluation of Challenge 17. We will post the list of submissions for your review soon. As I had mentioned last week, we are using the new software for the evaluation and the evaluation process for the first couple of solutions will still be slow. However, we will catch up with the challenges soon and our goal is to publish the results of a challenge within 2-3 weeks of publishing it.

TSQL Challenge 22 - Filling balls into boxes based on rules and preferences

Who among us wouldn't want to go back to our childhood and enjoy those lovely playful time once again? Well, here is a challenge that might take you back to your childhood and might keep you busy sorting and filling colorful balls into different boxes. Here is an interesting challenge submitted by our new contributor RamiReddy.

As I have mentioned a few times in the past, we are working very hard on improving 'TSQL Challenges' and a number of people from the SQL Server community have been helping us to come up with some improvements.

We were getting a large number of comments against the 'all-in-one-query' requirement that we put on the challenges. They argue that not all real-world problems are solved with the 'single-query' approach. There are also times when a 'multi-step' query performs better than a 'single-query' approach. They are right.

On the other side, we do get a lot of feedback from people who say that they like the 'one-query' requirement because that is the real 'fun' in these challenges. The 'single-query' requirment makes them think beyond the regular problem solving methods and they find it very interesting. They are right too.

We are still working on setting up a plan that makes both the groups happy. Till then we will allow 'multi-statement' solutions selectively. It means that some challenges will allow multi-statement solutions and some will not. If the challenge allows multi-statement solutions, we will specifically mention it along with the challenge description.

To improve the quality of the challenges and the clarity of the rules of the challenges, we started inviting volunteers to do an 'early challenge review' where they get a chance to review the challenge description and rules and give us some suggestions to improve the quality of the challenges. I am very glad that a few people already came forward to help us and if any of you would like to volunteer, please contact me privately.

Starting with this challenge, we will be providing sample data in physical tables instead of table variables. Your solution should not include the code to populate the sample tables. You can assume that the sample tables with data exist in the current database.

Starting with this challenge, we are also introducing a 'Challenge Moderator'. The moderator of a challenge will be helping you with your questions in the forums and everything else that you would need related to a certain challenge. RamiReddy is the Moderator for this challenge and he will be available to help you with any questions related to this challenge.

We have got some boxes and balls. Our job is to put balls into those boxes. But, wait a second! The balls should be filled into the boxes based on some rules and preferences configured by the user. Here are the rules.

• A box can have only one ball
• A ball can be placed only in one box
• Number of balls and number of boxes will always be the same.
• All boxes should be filled and all balls should be used
• There will be a configuration table where the preferences of the user will be stored. The preference setting should be followed when putting a ball into a box.

In addition to this, there will be a configuration table where the preferences of the user will be stored. The preference setting should be followed when putting a ball into a box.

Here is the sample data for this challenge.

Table: TC22_Boxes

This is the table that stores the boxes. Each row in this table represents a box. Boxes in this table will always be unique.

BoxId       BoxName
----------- --------------------
1           Box 1
2           Box 2
3           Box 3
4           Box 4
5           Box 5
6           Box 6

Table: TC22_Balls

This table stores the balls. Each row in this table represents a ball. Balls in this table will always be unique.

BallId      BallName
----------- --------------------
1           Ball 1
2           Ball 2
3           Ball 3
4           Ball 4
5           Ball 5
6           Ball 6

Table: TC22_Preferences

This table stores the preferences set by the user. There may be multiple preferences configured for each box or ball. For example, the second and third rows indicate that you can put ball #1 or ball #3 into box #2. You can take any one of the preferences but need to make sure that all boxes will be filled and all balls will be used.

PreferenceId BoxId       BallId
------------ ----------- -----------
1            1           1
2            2           1
3            2           3
4            3           2
5            3           3
6            4           1
7            4           2
8            4           3
9            4           4
10           4           5
11           4           6
12           5           4
13           5           5
14           6           5

Here is the expected results based on the above sample data.

Box         Ball      
----------- ----------- 
Box 1       Ball 1           
Box 2       Ball 3           
Box 3       Ball 2           
Box 4       Ball 6           
Box 5       Ball 4           
Box 6       Ball 5           

User the following scripts to generate the sample data for this challenge

-- --------------------------------------------------
-- Create the 'Boxes' table
-- --------------------------------------------------
IF OBJECT_ID('TC22_Boxes','U') IS NOT NULL 
BEGIN
	DROP TABLE TC22_Boxes
END
GO

CREATE TABLE TC22_Boxes
(
    BoxId INT IDENTITY PRIMARY KEY,
    BoxName VARCHAR(20)
)
GO

INSERT INTO TC22_Boxes (BoxName)
SELECT 'Box 1' UNION all
SELECT 'Box 2' UNION all
SELECT 'Box 3' UNION all
SELECT 'Box 4' UNION all
SELECT 'Box 5' UNION all
SELECT 'Box 6' 


-- --------------------------------------------------
-- Create the 'Balls' table
-- --------------------------------------------------
IF OBJECT_ID('TC22_Balls','U') IS NOT NULL 
BEGIN
	DROP TABLE TC22_Balls
END
GO

CREATE TABLE TC22_Balls(
    BallId INT IDENTITY PRIMARY KEY,
    BallName VARCHAR(20)
)
GO

INSERT INTO TC22_Balls (BallName)
SELECT 'Ball 1' UNION all
SELECT 'Ball 2' UNION all
SELECT 'Ball 3' UNION all
SELECT 'Ball 4' UNION all
SELECT 'Ball 5' UNION all
SELECT 'Ball 6' 

-- --------------------------------------------------
-- Create the 'Preferences' table
-- --------------------------------------------------
IF OBJECT_ID('TC22_Preferences','U') IS NOT NULL 
BEGIN
	DROP TABLE TC22_Preferences
END
GO

CREATE TABLE TC22_Preferences(
    PreferenceId INT IDENTITY PRIMARY KEY,
    BoxId INT,
    BallId INT
)
GO

INSERT INTO TC22_Preferences (BoxID, BallID) 
SELECT 1,1 UNION all
SELECT 2,1 UNION all
SELECT 2,3 UNION all
SELECT 3,2 UNION all
SELECT 3,3 UNION all
SELECT 4,1 UNION all
SELECT 4,2 UNION all
SELECT 4,3 UNION all
SELECT 4,4 UNION all
SELECT 4,5 UNION all
SELECT 4,6 UNION all
SELECT 5,4 UNION all
SELECT 5,5 UNION all
SELECT 6,5

Notes

1. Read the Submission Guidelines and make sure that your solution follows them.
2. The solution should work on SQL Server 2005, 2008 or later versions
3. Use this forum for any questions related to TSQL Challenge #22 

Unlike the previous challenges, this one allows multi-statement solutions. Your solution does not need to be a single-query solution. You can use RBAR, cursors, loops etc in your solution. However, we need to apply some restrictions on your code so that your solution can be evaluated using our automated evaluation script.

Your code should not create objects like views, functions or stored procedures. Your solution should be a single batch. There should not be any 'GO' statements between your solution. We will put the entire content of your solution to the body of a stored procedure and if we do that, your code should not produce an error. For example, you need to make sure that the following works:

CREATE PROCEDURE TC_Eval
AS
BEGIN
SET NOCOUNT ON
-- your code here
END
GO

Your code should not include the code to create the sample data. You can assume that the sample tables already exist in the current database.

We will close this challenge for evaluation on 8 February 2010 Midnight GMT. All the solutions we receive till that date will be processed and the results will be announced in the format given here. However, you can continue to send us your solutions till the day we announce the evaluation results.

1. Are Boxid, BallID and PreferenceId  sequential with no gaps ?
• The column is an identity column and identity columns are not expected to be sequential. There can be gaps/missing-ids.
2. Is there a maximum number of balls and boxes?
• No, there is no maximum number of balls and boxes. The number of balls and boxes will always be the same so that if you fill the boxes correctly, all boxes will be filled and all balls will be used.
3. What sort order is the final result set ?
• Sort the output by Box ID
4. Is there any priority order if there are 2 Balls going into 1 box ?
• No, there is no priority order, because there is only one ‘correct’ box for each ball.
5. Will there be a case in which two balls are preferenced only to one box? For example, can ball3 and ball5 be referenced only to box7 ?
• No, two balls can't be preferenced ONLY to one box. It can happen that ball 3 and 5 are referenced to box 7. But in such a case, either or both of them will also have another preference to another box.
6. Is it guaranteed that the sample data have one and only one valid solution?
• Yes, There will be only on ‘correct’ solution for a given set of sample data. If you assign a ball to an incorrect box, you will end up with another box that does not have any ball to fill in.
7. Can we create indexes on the sample tables?
• No, No modifications should be done to the sample tables.
8. Can I create and use a tally/number table in my queries?
• No, you are not allowed to create and use your own tally tables. However if you need a tally table, you can use the script given here to create a tally table. When you send us your solution, do not include the script to create the tally table. Your query can assume that the tally table already exists on the database where we will run your queries.
9. Can I use a CLR procedure/function
• No, CLR code is not allowed.

• dave ballantyne
• Aurelien Verla
• lmu92
• Sergejack

The Early Review team did a great job by reviewing the challenge description, rules and helped to compile the FAQ list. We have made the required updates to the challenge based on the recommendations of the Early Review Team.

About the Author

Challenge Idea by

TSQL Challenge 16 – Evaluation Started

I kept promising that the speed of the evaluation process will improve soon and some of you may be surprised seeing that the time it takes to publish the results of a challenge is still outside the acceptable time window. The truth is that we are building a work flow system that improves the speed of the evaluation process tremendously and we will be able to publish the results very soon after the deadline of a challenge.

We have thought of delaying the evaluation of challenge 16 so that we could put this challenge through the evaluation work flow system. Though the new system is expected to make the process faster, the first run will take some time because we will have to closely monitor, test and review each step.

We have published the list of submissions we received for challenge 16 at http://databasechallenges.com/SQLServer/TSQL/Challenge16/Submissions. The number of solutions we received for this challenge is surprisingly less. I would invite everyone to take a look at the above location and verify that their submission is listed there. If you don’t see your submission there, please send your solutions again and we will add them quickly.

TSQL Challenge 21 - Reading, recognizing and processing graphical representation of digits.

Here is a challenge that takes you away from those repetitive boring type of queries that you write over and over again, several times a day. All of us, the database people, are familiar with thinking in set based manner as well as row by row style. Here is something that is very interesting where you might need to process records in a 'three-line-at-a-time' fashion.

For the purpose of this challenge, imagine that you are working for a bank which just decided to scan all the banking documents. Assume that they have an old fashioned scanner which scans the documents and produces a text file with the customer number. So far so good. Well, not really! Unfortunately the scanner produces a graphical representation of the customer number using three lines of symbols: space, unerscores and pipe characters. Here is an example of the output produced by the scanner.

    _  _     _  _  _  _  _
  | _| _||_||_ |_   ||_||_|
  ||_  _|  | _||_|  ||_| _| 
 

Here are the rules to keep in mind while reading and recognizing the output generated by the scanner.

• Each digit is represented using 9 cells (3x3)
• Only spaces, underscores and pipe characters are used
• The number of digits in each account number may vary.
• The Scanner is not 100% reliable and it might produce some digits that are invalid

Your job is to read the output produced by the scanner and identify the the customer number represented by each image. Remember that the scanner is not very reliable and it might produce invalid digit representations. For each digit that is not valid, set the value to 'X'

Here is the sample data for this challenge. Please take care with spaces, tabs and carriage returns as each digit is represented by three lines of text and if a space, tab or carriage return is misplaced, the whole image will be distorted.

Id          ScanNumber
----------- ---------------------------
1            _  _  _  _  _  _  _  _  _ 
            | || || || || |  || ||_ |_|
            |_||_||_||_||_|  ||_| _| _|
                           
2               _  _  _  _  _  _     _ 
            |_||_|| || ||_   |  |  ||_ 
              | _||_||_||_|  |  |  | _|
                           
3            _  _  _     _  _  _  _  _ 
            |_ |_|| || ||_ |_| _|  ||_|
            |_||_||_||_||_||_||_   | _|
                           
4               _  _  _  _  _  _     _ 
            |_||_|| ||_||_   |  |  ||_ 
              | _||_||_||_|  |  |  ||_|
                           
5               _  _  _  _  _  _     _ 
            | ||_|| ||_||_   |  |  ||_ 
              | _||_||_||_|  |  |  ||_|
                           
6            _     _  _     _  _  _  _ 
            | |  | _| _||_||_ |_   ||_|
            |_|  ||_  _|  | _||_|  ||_|

Based on the sample input and the rules discussed earlier, here is the expected output.

Id          Value
----------- ---------
1           000007059
2           490067715
3           680X68279
4           490867716
5           X90867716
6           012345678

Use the following script to generate the sample data for this challenge.

DECLARE @t TABLE (Id int, ScanNumber NVARCHAR(116))

INSERT INTO @t
SELECT	1,--> 000 007 059
' _  _  _  _  _  _  _  _  _ 
| || || || || |  || ||_ |_|
|_||_||_||_||_|  ||_| _| _|
                           
' UNION 
SELECT  2, --> 490 067 715
'    _  _  _  _  _  _     _ 
|_||_|| || ||_   |  |  ||_ 
  | _||_||_||_|  |  |  | _|
                           
' UNION
SELECT	3, --> 680 X68 279
' _  _  _     _  _  _  _  _ 
|_ |_|| || ||_ |_| _|  ||_|
|_||_||_||_||_||_||_   | _|
                           
' UNION
SELECT	4, --> 490 867 716
'    _  _  _  _  _  _     _ 
|_||_|| ||_||_   |  |  ||_ 
  | _||_||_||_|  |  |  ||_|
                           
'  UNION
SELECT	5, --> X90 867 716
'    _  _  _  _  _  _     _ 
| ||_|| ||_||_   |  |  ||_ 
  | _||_||_||_|  |  |  ||_|
                           
' 
UNION 
SELECT 6, --> 012 345 678
' _     _  _     _  _  _  _ 
| |  | _| _||_||_ |_   ||_|
|_|  ||_  _|  | _||_|  ||_|
                           
' 

Notes

1. Each record may have more than three lines of data (each line is separated by a CR and LF). Your code should consider only the first three lines.
2. The length of the first three lines of each recrd will always be the same and will be divisible by three.
3. There may be 3x3 blocks of spaces in the string. In such a case, you should generate an empty space in the output. If a 3x3 block does not create a valid digit (except for the case of a 3x3 block of spaces), you should generate an "X".
4. The number of 3x3 blocks in each record may vary
5. Read the Submission Guidelines and make sure that your solution follows them.
6. The solution should work on SQL Server 2005, 2008 or later versions
7. Use this forum for any questions related to TSQL Challenge #21 

We will close this challenge for evaluation on 25 January 2010 Midnight GMT. All the solutions we receive till that date will be processed and the results will be announced in the format given here. However, you can continue to send us your solutions till the day we announce the evaluation results.

You can submit solutions for challenges even after we publish the results. However, there are a few things you should do before submitting your solutions.

• Do a basic testing of your solution using the sample data we post along with the results of the evaluation. See an example here.
• If your solution passes the basic testing, do a logic testing of your solution. We will publish the data to be used for the logic testing along with the challenge results. You can see an example here.
• If your solution passes the logic testing, do a load testing of your solution using the load testing data that we publish along with the challenge results. You can see an example here.
• After load testing, match your results with the results we have published for that challenge. (see an example here). If the results of your solution comes in the top 10 solutions listed on the results page, go ahead and send us your solution. Make sure that you send us the results of your load testing along with your submission.
• We will re-evalute your solution and if it comes in the top 10, we will list it in the results page and you will be added to the winners page (see an example here) and will get a certain number of SQL Stars based on the grade of your solution.

About the Author

Congratulations to the Winners of TSQL Challenge #15

Results of TSQL Challenge 15 published!

We are glad to announce the results of TSQL Challenge #15. This challenge had comparatively more number of correct solutions and almost all of them were really good in terms of performance and best practices. Here are the top 3 winners of TSQL Challenge #15.

1. Dave Ballantyne
2. Antonio Facturan
3. Fabien Contaminard

The solution by Dave Ballantyne used comparatively very very lesser number of reads and cpu. However, it took more number of seconds than the other two solutions. You can see the performance statistics of all the solutions at http://databasechallenges.com/SQLServer/TSQL/Challenge15/load_testing_results

How to identify the best solution?

Identifying the best solution or ranking them based on the performance is really a challenge. We currently sum up the read, write, cpu and duration ranks of each solution. This method seems to be good, but there are a few arguments here.

If we take the example of this challenge, the solution by Dave Ballantyne came on the top when we calculated the combined rank. Dave’s solution has lesser number of reads and cpu usage. However, the duration is more than the other two solutions. On the other hand, the solution by Antonio has much much more reads and CPU by finishes faster than the other two solutions.

Some people argue that duration of a query is more important than reads/writes/cpu. They say that what people are concerned is only the performance, not reads or writes or even CPU. If the query executes within the expected duration, then it is good, even if the reads or CPU are more than expected.

There are other people who argue that reads/writes/cpu are as important as duration. If a query executes ‘super' fast’ but takes a large amount of CPU, reads and writes, then it will have adverse effect on other queries running on the server. The query being tested may be completing within the required duration limits, but the excessive cpu, reads and writes can slow down many other queries running on the server.

At TSQL Challenges, we currently look at the combined ranks (total of reads, writes, cpu and duration). However, we are eager to know what all of you think about this and are willing to make the required amendments. Please feel free to share your thoughts about this.

 TSQL Challenge 15 – Results

1. TSQL Challenge 15 - All Solutions
2. TSQL Challenge 15 - Accepted Solutions
3. TSQL Challenge 15 - Basic Testing
4. TSQL Challenge 15 - Basic Testing Results
5. TSQL Challenge 15 - Logic Testing
6. TSQL Challenge 15 - Logic Testing Results
7. TSQL Challenge 15 - Load Testing
8. TSQL Challenge 15 - Load Testing Results
9. TSQL Challenge 15 – Winners

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