TSQL Challenge 7 Winners
First of all, I want to thank all challengers for their contribution. There is more and more people participating, contributing and giving great feedback. All of this, encourage us to continue and help us developing TSQL Challenges community. I hope that everyone have fun with these challenges and always learn something.
Back to the TSQL Challenge 7, this is the list of the winners:
We will publish in a few days the details for each solution.
As for TSQL Challenge 4, the key point of this challenge was to write the shortest code.
You should ask me about the interest of writing the shortest code. You should argue that writing short and obscure code is rarely a symptom of quality and you will be right!
The idea is not to write the shortest-shortest code without any limitation of sense but to try through the challenge to see if there isn’t a more optimal solution than the first simple solution we have in mind. It is also the opportunity to see some tips and tricks from experts or some functions less well known.
As you will in the details of the solutions, this challenge was the occasion to deal with some undocumented functions of SQL Server: sp_MsForEachDb and sp_MsForEachTable. One may argue that these functions are not documented nor supported. In fact, you should use them carefully in your projects, but currently they are present and very useful, mainly for managing tasks like the one in this challenge.
For their defense, I should add, that their source are available, so you can verify by yourself the content and adapt it to your own procedure if it is not what you want. In all the cases, they are interesting learning points.
To find the code of each of them, navigate on your database to Procedures, System procedures, sys.sp_MSforeachdb and sys.sp_MSforeachtable, then right click Modify. This is the signature for these two procedures:
ALTER proc [sys].[sp_MSforeachdb]
@command1 nvarchar(2000),
@replacechar nchar(1) = N'?',
@command2 nvarchar(2000) = null,
@command3 nvarchar(2000) = null,
@precommand nvarchar(2000) = null,
@postcommand nvarchar(2000) = null
as
...
The 3 commands are used in the process for each table/db, then pre and post commands are used outside the iterative process. They use the same worker process which is defined by the procedure sys.spMSforeach_worker.
By the way, most people use only the @command1 parameter with the default ‘?’ char for replacing table/db names. The tip here was to use different replace chars for each one and write a double dynamic query. You should also have troubles with the quotes…
One solution should be to use simple quotes and double quotes. This is an example that should print all tables for all non-system databases
exec sp_MSforeachdb '
select "$",0,0,0,0,"x";
if db_id("$")>4
exec $..sp_Msforeachtable "exec sp_spaceused ''?''";',N'$'
For the table size calculation of the challenge, there is different methods for calculating that. You should do this by hand using the data from the view sys.dm_db_partition_stats. But it is not always easy or needed to have a deep dive in it. There is also two very useful system procedures for this too:
- sp_spaceused @table_name
- sp_MStablespace @table_name
the first one is more verbose and usually for printing ‘as is’. The second is more useful for additional computation. They return both the Kbytes of data used and number of rows asked by the challenge.
Then the last problem of the challenge was to return an ordered table with both database and tables names. This means that you need to mark the database name in your iteration and be able to store data in a temp table that you can query and reorder. This not obviously the most difficult part but if you want to create the shortest query you need to get the data directly out of the stream produced by the MSforeach procedures…
One more interesting tip if you need to manage the output table of a procedure is to introduce an identity column at the end. If you create a temp table that maps to the output of the procedure, you have the possibility to add an identity column at the end that can help you managing the output data (you can see it in action in the example at the end of this post).
In all the cases you will need to use a temp table. For these kind of problems I prefer to use table variables but temp tables or global temp tables should also work too. Global temp tables should be useful to store and exchange data between the main context and dynamic queries context.
You will see in the next posts the solutions by each challengers, they are all based on these principles.
A special mention to Barry, that create a compressed version of its query with decompression on the fly! He has not get the shortest query but its compression solution made him win 30% of the query size! That’s really amazing ;-)
Just to finish, this the solution I made while writing the challenge that uses the principles described here:
--fill data into a temp table
declare @a table(
a char(128),b int,c char(16),d char(16),e char(16)
,f char(16),id int identity(1,1))
insert into @a
exec sp_MSforeachdb '
select "$",0,0,0,0,"x";
if db_id("$")>4
exec $..sp_Msforeachtable "exec sp_spaceused ''?''";',
N'$'
-- filter and order final data
select top 5
BASE=(
select top 1 a from @a
where id <= db2.id - 1 and f='x'
order by id desc),
[TABLE]=a,SIZE=d,ROWS=b
from @a db2 where db2.f<>'x'
order by str(rtrim(replace(d,'KB','')))desc
If you have questions or comments about this solution you should discuss it on the dedicated forum: http://beyondrelational.com/groups/tsqlchallenge/forum/t/186.aspx.
If you have generic questions about this challenge the dedicated is still open to discuss: http://beyondrelational.com/forums/t/62.aspx
Congratulations to the winners, thank you to all challengers and stay tuned for next challenges!
Get notified when a new challenge is available or the evaluation result published either by subscribing to the 
RSS feed or subscribing to the Email Notification.