Monday, 11 February 2013

Mating Patterns with Daniel King

On   In , ,    → No comments  edit
I have started a more formal chess training program, something that would possibly improve my skills as a chess player and finally getting me into that place where I can fully appreciate the beauty of the game. As part of this program I've watched the Daniel King Power Play DVD Part 1: Mating Patterns, in ChessBase format. It was a well presented material that made for very interesting time. The information contained is aimed at beginners such as myself and presents several chess mating themes, with some puzzles at the end, to test the knowledge gain. I can say that I liked it and I recommend it to other learning chess players as well. However, this post is aimed at summarizing the information, for future reference.

    Themes:
  1. Greek Gift
  2. Lasker Double Bishop sacrifice
  3. Lawnmower (Double Shotgun) mate
  4. Bishop See-saw
  5. H-file Rook distraction
  6. Knight mate
  7. Queen in on the pin
  8. Back rank mate

And then are the Puzzles.

For each theme I will post pictures with a position, let you think, then give you the opportunity to see the entire PGN. Same with the puzzles. Try to think things through before looking for the solution.

1. Greek Gift

The opposing king is castled short and you sack a bishop by taking the pawn in front of the enemy king. The king is forced to take, and then a knight check comes, followed by the arrival of the queen.

Netzer, Jean - Guezennec, Franck, 2000 (FRA-chT U20)

White to move. Click here to see the game.

Hartl, Rainer - Hecht, Christoph, 2000 (Landesliga Sued 0001)

White to move. Click here to see the game.

Langrock, Hannes - Gaede, Derek, 2000 (JBLN West 0001)

White to move. Click here to see the game.

Polugaevsky, Lev - Tal, Mihail,1969 (URS-ch37)

White to move. Click here to see the game.

Colle, Edgar - O'Hanlon, John,1930 (Nice)

White to move. Click here to see the game.

Thesing, Matthias - Borngaesser, Rene,1984 (NRW-ch)

Black to move. Click here to see the game.

Toulzac, Pierre Yves - Sokolov, Andrei,2000 (Mulhouse IM)

White to move. Click here to see the game.


2. Lasker Double Bishop Sacrifice

The light bishop is sacrificed just like in the Greek Gift, the queen comes around to check, then the dark bishop is sacrificed as well for the pawn in front of the enemy king. The mate is achieved by a rook lift.

Lasker, Emanuel - Bauer, Johann Hermann, 1889 (Amsterdam)

White to move. Click here to see the game.

Koenig - Cornforth, 1952 (London)

White to move. Click here to see the game.

Dizdarevic, Emir - Miles, Anthony , 1985 (Biel MTO op)

Black to move. Click here to see the game.

Jonkman, Harmen - Espig, Lutz, 1998 (Chemnitz op)

White to move. Click here to see the game.

3. Lawnmower (Double Shotgun) mate

Two rooks or a rook and a queen push the enemy king to the margin of the board, taking rank after rank or file after file until the king is mated

King, Daniel J - Krasenkow, Michal, 1989 (GMA Baleares op)

White to move. Click here to see the game.

Tkachiev, Vladislav - Watson, William N, 1993 (London Lloyds)

White to move. Click here to see the game.

Watson, William N - Merriman, John, 1993 (London Lloyds)

Black to move. Click here to see the game.

Bologan, V. - Van Haastert, E., 2005 (21st ECC)

Black moves h5, White to move. Click here to see the game.

4. Bishop See-saw

The queen has caught the enemy king at the corner of the board, his only escape blocked and continuously harassed by a bishop that gobbles all the pieces on its color and then gives mate.

From Nimzowitsch's book My System

White to move. Click here to see the game.

King, Daniel J - Kuijf, Marinus, 1982 (Amsterdam)

White to move. Click here to see the game.

5. H-file Rook Distraction

The enemy king seems safe, as your pieces appear uncoordinated, but here comes a suicidal rook bringing the king into the open and ready to be slaughtered.

Polgar, J. - Berkes, F., 2003 (Hunguest Hotels)

White to move. Click here to see the game.

Kuemin, Simon - Cebalo, Miso, 2003 (Biel MTO)

White to move. Click here to see the game.

Kudrin, Sergey - King, Daniel J, 1988 (London NWYM)

White to move. Click here to see the game.

Sorensen, Arne - Marciano, David, 1988 (Tecklenburg op)

White to move. Click here to see the game.

Kuzmin, Gennadi P - Zhuravliov, Valerij, 1992 (St Petersburg)

White to move. Click here to see the game.

6. Knight Mate

Mates with the knights, whether in conjunction with other pieces or with other knights.

Kotronias, Vasilios - King, Daniel J, 1990 (New York WFW)

White to move. Click here to see the game.

Wheeler, G. - Povah, N., 1977 (London)

White to move. Click here to see the game.

De Musset, A. - study

White to move. Click here to see the game.

Clemenz - Eisenschmidt, 1862 (Dorpat)

White to move. Click here to see the game.

Dumpor, Atif - Kosic, Dragan, 2001 (Ajvatovica IM)

Black to move. Click here to see the game.

Kortschnoj, Viktor - Karpov, Anatoly, 1978 (World Championship 29th)

White to move. Click here to see the game.

7. Queen in on the pin

The queen comes right next to the enemy king, where the poor protecting pieces are pinned by some other piece. The opposing king is stuck between his pieces and the troublesome queen.

Tatai, Stefano - Kortschnoj, Viktor, 1978 (Beersheba)

Black to move. Click here to see the game.

King, Daniel J - Costa, Jean Luc, 1987 (Bern)

From here, Black will move Ne4, then White to move. Click here to see the game.

Fazekas - Spielmann, 1938 (Prague)

White to move. Click here to see the game.

Peredy - Malanca, 2003 (Budapest)

Black to move. Click here to see the game.

8. Back rank mate

The poor enemy king is attacked and there is nowhere to go because of his own protectors.

Wolff, Patrick G - King, Daniel J, 1989 (London WFW)

White moves Rab1, Black to move. Click here to see the game.

Rovner - Kamyshev, 1947 (Moscow)

White to move. Click here to see the game.

Capablanca - Fonaroff, 1918 (New York - casual)

White to move. Click here to see the game.

Alden - Nilsson, 1972 (Sweden)

Black to move. Click here to see the game.

Adams, Michael - Giorgadze, Giorgi, 1997 (FIDE-Wch k.o.)

White to move. Click here to see the game.

Alekhine, Alexander - Colle, Edgar, 1925 (Paris)

White to move. Click here to see the game.

Bazan, Osvaldo - Fischer, Robert James, 1960 (Mar del Plata)

Black to move. Click here to see the game.

Puzzles

Think it through, prepare your moves in your head and only then look at the games.

Cinak, Nilufer - Novak, Ksenija, 2002 (Bled ol (Women))

White to move. Click here to see the game.

Bareev, Evgeny - Akopian, Vladimir, 2000 (Dortmund SuperGM)

White to move. Click here to see the game.

Sveshnikov, Evgeny - Sherbakov, Ruslan, 991 (URS-ch58)

Black will move g6, White to move. Click here to see the game.

Hillarp-Persson, Tiger - Hansen, Sune Berg, 2005 (Sigeman Chess Tournament 2005)

White to move. Click here to see the game.

Marin, Mihail - Kiselev, Sergey, 1997 (Ciocaltea mem)

Black to move. Click here to see the game.

Yates, Frederick - Reti, Richard, 1924 (New York)

White to move. Click here to see the game.

Jussupow, Artur - Ivanchuk, Vassily, 1991 (Candidates qf3)

White to move. Click here to see the game.

Bruzon, Lazaro - Jobava, Baadur, 2005 (Capablanca Memorial Elite)

White to move. Click here to see the game.

Vidmar - Euwe, 1929 (Karlsbad)

White to move. Click here to see the game.

Rozentalis, Eduardas - Kozul, Zdenko

White to move. Click here to see the game.

Azmaiparashvili, Zurab - Shirov, Alexei, 2002 (FIDE GP)

Black to move. Click here to see the game.

I know it has been a long read, but imagine how long it took me to write it! This is not something you read once, but a post that you return to again and again to rehearse the games and maintain the feel for these interesting mates.

Also, there are some inconsistencies between the start of the game and the pictures, also the puzzles are not very clear. It should have a text that explains what you are to try to do. And there are comments in German in the PGNs, as well. I am going to address this.

Wednesday, 6 February 2013

Existence, by David Brin

On   In , ,    → No comments  edit
Book coverI was waiting for the moment when I would be reading a new sci-fi book. Based on a suggestion from some source I have long forgotten, I chose David Brin's Existence, a futuristic hard science fiction book.

And at first it felt good. It described a cyberpunk near future, after some sobering disasters that rallied the world against nuclear war and global warming. It was dialog based, like the books of Asimov. It delved into the political, economical, technical and personal aspects of the world. It even started with the discovery of an alien artefact and started an exploration into the Fermi paradox, the philosophical conundrum that asks: "if there are many civilisation in the galaxy and they all reach the sophistication to go to space, where are they?".

But soon it started to feel all wrong. The different stories were fragmented, in some parts badly written, in some parts conflicting, some never connecting to one another, like Brin got some texts he had worked on and mashed them all into the book. Then the "chapters" all starting with a little media announcement or quote that explained bits of the world, but in a reporter editorial style that said nothing and brought nothing new to the table. And then it started with the goading: an idea was forming, the characters were reaching a realization or another, and before the reader got to see what it was, another chapter was starting.

My conclusion after finishing the book is that, while filled with interesting ideas and also having a main plot that is, indeed, ingenious, this was not a good book! I've spent the time to read the acknowledgements at the end only to find I was right: Brin did publish a lot of the stuff in the book in short stories here and there. And after finishing all that material, he speeded things up to show the "future", because he really had been disconnected from all the stories he started, reaching that annoying fast-forward effect one often finds in the works of writing amateurs. And the thing is, David Brin is not an amateur - at least he shouldn't be after all the books he has written and prizes he has won. One possible reason is that it is the first book after a hiatus of 10 years. I hate to say it, but it looks like he needed some money and this is the way he chose to do it: frame a couple short stories, a couple essays and an old Usenet into a longer novella that feels like the patched mess it actually is.

The bottom line is that I can't recommend this book. The main idea is interesting, but it can be summarized in a few words, which I won't do for fear of spoiling it for you in case you do decide to read Existence.

Friday, 1 February 2013

Faster and better blog

On   In ,    → No comments  edit
Windows 8 logo
Recently Microsoft released a tool that checks your site for web design issues, with focus on Internet Explorer 10. So I've added a few things, like more thorough gradients for the important quotes, touch behaviour for Microsoft Touch APIs and a color and logo for the tile that you guys will be placing on your Windows 8 desktop!

But even more importantly, I've found a bug in the chess board algorithm in IE: some of the boards would just show an ugly dialog error and not display anything. I fixed the bug and also made the boards appear only when the screen is scrolled on the PGNs. That means you will be able to open chess posts or even the chess category, even in IE, with the caveat that you will experience slower scrolling as each PGN becomes visible. On Chrome and Firefox and probably IE9 and IE10 there will be no visible delays, but I am still using Internet Explorer 8, so for you Windows XP guys, there is a huge usability increase!

Thursday, 17 January 2013

A Danish/Morra/Elephant system?

On   In , ,    → 1 comment  edit
As you may have guessed from my previous chess posts, I am a chess beginner and a gambiteer. I like to play the strange moves and see my opponents squirm in positions that they were not expecting and were not prepared for. That is why the proposition in this post is gambit galore and also to be taken with a grain of salt.

My idea is that there could be common themes for the three Gambits in the title, since they start almost the same way. You have the Smith-Morra Gambit, where White answers Black's Sicilian Defence with d4 and then c3 in the accepted version:1. e4 c5 2. d4 cxd4 3. c3 dxc3 4. Nxc3 * then you have the Danish Gambit, where White's move order is the same in response to King's pawn defence: 1. e4 e5 2. d4 exd4 3. c3 dxc3 4. Nxc3 * and then there is the similar response from Black to the King's pawn opening, called the Elephant gambit: 1. e4 e5 2. Nf3 d5 3. exd5 *

You might notice that in the Elephant, I did not offer up a second pawn on c6. This is because the main line is pawn to e4 and also White has the opportunity to take e5 with the knight, which is the computer recommendation as well. The problem is that after Black's response Qe2 to the main line or the Bd6 computer recommendation after Nxe5, the Elephant doesn't appear at all like the Danish/Morra and, instead, threads into its own territory, somewhere closer to the Latvian gambit, but not by much. However, in this post I will be stretching the imagination and will be trying to squish the big Elephant into the Morra mold and see where it takes me.

I have just finished watching a two hour video presentation of the Morra accepted line, by IM Andrew Martin, and there are also a lot of tutorials for the Danish, from beginner to very advanced levels. Not so for the Elephant, which seems to be even less favoured than the Latvian, to which GM Roman Dzindzichashvili answered with a refutation and some very rude words to its efficacity. All that I could find about it are lines that have no connection with the Danish/Morra style and that is because of that pesky White knight on f3.

If my thesis holds ground, then I will be talking here about a chess system that has some similar ideas and theory for at least three major openings: The Sicilian Defence and King's pawn for both White and Black! Also, threading on less travelled ground, there is a good chance online and club players will be unnerved by it.

So let's get into it. I will start with the Smith-Morra main line. This means the most played version in database games, by players who know theory and open that way because it was proven to be the best way. There is little chance you will see the same moves in club level games.

1. e4 c5 2. d4 cxd4 3. c3 dxc3 4. Nxc3 Nc6 5. Nf3 d6 6. Bc4 e6 7. O-O Nf6 8. Qe2 Be7 9. Rd1 e5 10. h3 O-O 11. Be3 a6 12. Rac1 *

This is the position in which White wants to get. I tweaked a little the game so that at move 11. Black moved to the second most used line in the database, the a6 system, rather than Be6 and exchange light bishops. What I want to evidence here is the position of the White pieces: both rooks are connected and on open or semi-open files, cramping Black's development, the bishops are out and about, aiming at the Black king, the knights are developed and the queen is on a very crucial square, controlling yet another file and the essential e2-c4 diagonal.Black has not finished development, has pinned pawns and weaknesses like b6 where a knight may find outpost. There is a lot of potential for attack and, even before reaching this position, a lot of possible traps in which Black could fall. Even the main line has only about 100 games in the database at this point, so it is not very common, even if it is a pretty solid opening.

Let's move to the Danish and compare.

1. e4 e5 2. d4 exd4 3. c3 dxc3 4. Nxc3 Nc6 5. Bc4 Nf6 6. Nf3 d6 7. Qb3 (7. O-O Be7 8. Qe2 O-O 9. h3 a6 10. Rd1 b5 11. Bb3) 7... Qd7 8. Ng5 Ne5 9. Bb5 c6 10. f4 cxb5 11. fxe5 *

Seems to be a different beast altogether. I've added a variation that plays like the Morra, the single game in the database like that (Sipek-Urbanec 1995). The b5 push is also found in the Morra. So, what are the differences here? The most important to me seems the absence of the Black pawn on e6. That means White can attack much quicker and Black must defend more aggressively, too. The e4 pawn is unopposed and, in some games, that proves decisive for White. The pawn on c7 is still there, so the Black queen will have to maneuver on light squares instead of dark.

A possible conclusion would be that, even if they seem similar, the Danish and Morra gambits are quite different. But are they?

A White queen on e2 would support the e4 pawn in its push forward and make room for a rook on d1, just like in the Morra. The dark bishop can pin or eventually trade with the knight on f6 or even move to e3 or f4, supporting the e4 pawn as it moves to e5 and making room for a rook on c1. A computer analysis on a Danish game played in the Morra fashion shows equality when Black still has an extra pawn.

Now, some of the readers may scoff and conclude that I am trying to fit the proverbial triangle shape into the round hole using brute force, that attempting to take one opening and play it like another is an imperfect chimera, destined to be an abomination. However, I must remind you that I am not a master player, nor a professional one. I have no time to learn tons of theory just to win a game. My purpose for this research (which may still fail to achieve anything) is to find a gambit based system that uses the same principles for any opponent response. In time, each variation can be improved and branched off from the main system, but at the start all I need is for it to work.

Let's get back, then. How about playing a Morra game in the Danish way? Well, the Danish gambit is even rarer than the Smith-Morra and the games in my database are primarily focused on the exposed f7 square. It could work, I guess, but it would seem even more unnatural and, lacking proper theory, a beginner like me could easily mess it up. I will, therefore, use the Morra as the template to which all others must conform.

Besides, if you think fitting the Danish to the Morra was difficult, the Elephant comes next!

In the database there are only two games that start like a Morra Elephant and they both are won by White, which is not good for us. Only when it gets to the standard position of knight protecting the single center pawn, the transposed games suddenly reach 73! It seems this position can be more easily achieved by playing the Scandinavian defence! In the next board I will present the main line for the Elephant, then the Morra Elephant and at the same time the way to reach the same position from the Scandinavian. The rest will continue from the Elephant line, but, actually, it will be based more on Scandinavian games.

1. e4 e5 (1... d5 {The Scandinavian Defence} 2. exd5 c6 3. dxc6 Nxc6 4. Nf3 e5 {And we reach the position would would have liked from the Morra Elephant.}) 2. Nf3 d5 3. exd5 e4 (3... c6 {And here is a Morrafication of the Elephant} 4. dxc6 Nxc6 5. Bb5 Bd6 6. O-O Nge7 7. d4 e4 8. Ne5 Bxe5 9. dxe5 O-O 10. Qxd8 Rxd8 11. Nc3 Nxe5) 4. Qe2 {This is how the Elephant is mainly played.} Nf6 5. Nc3 Be7 6. Nxe4 O-O 7. d3 Nxd5 8. Qd1 Nc6 9. Be2 Bf5 10. O-O Qd7 *

Ooh! This seems completely different. White still has that extra tempo and he uses it to pin the Black knight on c6, which leaves e5 undefended. Black's bishop on f8 did not have time to get out, so moving the queen on e7 like in the Morra would block it and the entire king side. The move Bd6 is the only one that can defend the pawn and this gives White at least the opportunity to swap the bishop with the knight and mess up the Black pawn structure on the queen side. If White does not take, as in the example above, then the only possible move to protect the knight is to use the other knight on e7, thus forever altering the structure of the game.

It seems no amount of force will twist the Elephant into a Morra gambit a tempo behind. A Morra with a lost tempo doesn't even appear to work! Besides, in order to get here, White had to ignore the opportunity in the beginning to take on e5 with the knight, as suggested by the computer; a much safer route to the same dysfunctional position can be achieved from the Scandinavian defence.

The Elephant hides some interesting traps that have nothing to do with the Morra or the Danish and has more in common with the Latvian gambit that with the two systems above. The Latvian, if you remember, offers up two pawns in order to gain the tempo White is awarded in the start of a chess game. The Elephant can be played in the same way, only to lose two center pawns, so not so good. The similarities with the Morra/Danish are deceiving. A tempo behind, Black cannot use the same ideas, having to defend instead of attack.

Conclusion: It is a very difficult thing to find a defence for Black that works the same way as an opening for White, because of the extra tempo. Even so, the Elephant only begins like the Morra, it has nothing else in common. The Morra gambit itself is only similar to the Danish and, while I think they can be molded in the same shape, it would be a tortuous adventure that I am not sure will get me where I want.

I hope you have gained a little understanding of the differences between the three gambits and how simple differences like the position of a pawn or an extra tempo can change a game of chess.

Thursday, 10 January 2013

The Drowning Girl, by Caitlín R. Kiernan

On   In , ,    → No comments  edit
Book coverWe live now in a world where people get the same education, see the same movies, read the same books - if at all. We then watch the ones around us and see only ourselves and we get bored. That is why, I believe, we start to see various mental illnesses or strange behaviours as interesting. That is why, I think, The Drowning Girl, by Caitlín R. Kiernan has received so wonderful reviews.

That doesn't mean the book is not brilliant. Kiernan paints the world as seen from the eyes of a lesbian paranoid schizophrenic, combining ideas from paintings, old legends and written stories into a whirlpool of staggering creativity. However, I do have to wonder, would the book have received the same amount of positive reviews if the main character was a straight man?

All that aside, I have tried to keep an open mind when reading the book and I have found that the way the author mingles stories and goes back and forth, keeping the reader on their tows, is both excellent and terribly irritating. It builds up a lot of tension that needs to be released into a grand finale. However, the climax of the book seemed to me to be somewhere in the middle, with the ending lagging and wasting into pointless mental delusions.

It is hard for me to recommend or not recommend this book. It is clearly well written and very inspired. It not only delightfully weird, but also draws information and data from all kinds of art fields and mingles them together in an interesting way. The construction of the book aside, though, leaves a plot that doesn't really mean anything. It's the maelstrom of thoughts and feelings of a mentally troubled person with a slight mystical component which, even till the end, is not really clear if it is only in her mind or has some factual truth.

I did enjoy one thing, though, the idea that something can be "true", but not "factual". If you think about it, it makes sense, but usually words like "truth" hold an objective mask on them, when most of the uses of those words are actually subjective. Yep, it's true :) I also liked the way details about the artists led to connections to other works and facts, that a thorough analysis of art can show hidden worlds and interesting perspectives.

As a conclusion, what leapt into mind when trying to find a book that is similar to this was Geek Love, by Katherine Dunn. In a word: freaky. The Drowning Girl is much more interesting, though, and doesn't try so hard to shock with the character's sexuality or personal weirdness. But in the end, having read it, I felt like it said nothing. An interesting journey towards nowhere in particular.

Wednesday, 9 January 2013

Why doubling single quotes is not enough to protect from SQL Injection

On   In ,    → No comments  edit
We all know that the best way to prevent SQL injection is to use parameters, either in stored procedures or in parameterized queries. Yet on some occasions we meet code that replaces every single quote with two single quotes. The question arises: OK, it's ugly, but isn't it enough?

I have recently found out of something called "Unicode Smuggling" which uses the database against itself to bypass protection as described above. More details here: SQL Smuggling , but the basic idea is this: if the replacement scheme is implemented in the database and uses VARCHAR or maybe the code uses some non-unicode string, then the protection is vulnerable to this by leveraging what is known as Unicode Homoglyphs. If you feel adventurous and want to examine thoroughly the ways Unicode can be used maliciously, check out UTR#36.

Here is an example:
CREATE PROC UpdateMyTable
@newtitle NVARCHAR(100)
AS
/*
Double up any single quotes
*/
SET @newtitle = REPLACE(@newtitle, '''','''''')

DECLARE @UpdateStatement VARCHAR(MAX)

SET @UpdateStatement = 'UPDATE myTable SET title='''  + @newtitle + ''''

EXEC(@UpdateStatement)

Note the use of VARCHAR as the type of @UpdateStatement. This procedure receives a string, doubles all single quotes, then creates an SQL string that then is executed. This procedure would be vulnerable to this:
EXEC UpdateMyTable N'ʼ;DROP TABLE myTable--'

The first character in the provided string is not a single quote, but the Unicode character U+02BC . SQL will silently convert this into a single quote when stored in a VARCHAR. The injection will work.

Small demo in MS-SQL:
DECLARE @nhack NVARCHAR(100) = N'ʼ;DROP TABLE myTable--'
DECLARE @hack VARCHAR(100) = N'ʼ;DROP TABLE myTable--'
SELECT UNICODE(@nhack),UNICODE(@hack) -- Results: 700      39

More discussing this here: Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes?

Monday, 7 January 2013

T-SQL conditional sort

On   In , ,    → 2 comments  edit
I've often encountered this situation: a stored procedure needs to display a list of records ordered by a dynamic parameter. In Transact SQL, the Microsoft SQL server, one cannot do this elegantly in any way. I will list them all and tell you what the problem with each is.

First of all, let's start with an example. Assume we have a table called Test with a lot of rows, which has a datetime column which has an index on it. Let's call that TheDate to avoid any SQL keywords. We want to do something like this:
SELECT TOP 10 * FROM Test ORDER BY TheDate ASC

Notice that I want to get the top 10 rows, which means I only need a small part of the total. I also order directly by TheDate. In order to release a piece of code we also need to test it for performance issues. Let's look at the execution plan:


Now, let's try to order it dynamically on a string parameter which determines the type of the sort:
SELECT TOP 10 * FROM Test ORDER BY CASE WHEN @sort='ASC' THEN TheDate END ASC, TheDate DESC

As you see, I've used CASE to determine the sort order. There is no option to give a parameter as the sort order. The execution plan is this:


Surprise! The execution plan for the second query shows it is ten times slower. What actually happens is that the entire table is sorted by the case expression in a intermediate table result, then 10 items are extracted from it.

There must be a solution, you think, and here is an ingenious one:
DECLARE @intSort INT = CASE WHEN @sort='ASC' THEN 1 ELSE -1 END
SELECT TOP 10 * FROM Test ORDER BY CAST(TheDate AS FLOAT)*@intSort ASC

I transform the datetime value into a float and then I use a mathematical expression on it, multiplying it with 1 or -1. It is the simplest expression possible under the circumstances. The execution plan is:


Bottom line, there is no exception to the rule: when you order by an expression, SQL Server does not use indexes, even if the expression is easily decompilable. Don't get mislead by the apparent functional programming style of SQL syntax. It doesn't really optimize the execution plan in that way.. Even if the column is an integer, it will not work. Ordering by TheInteger is fundamentally faster than ordering by -TheInteger.

And now the solution, ugly as it may be (imagine the select is a large one, with joins and where conditions):
IF @sort='ASC' 
BEGIN
  SELECT TOP 10 * FROM Test ORDER BY TheDate ASC
END
ELSE
BEGIN
  SELECT TOP 10 * FROM Test ORDER BY TheDate DESC
END

Yes, the dreaded duplication of code. But the execution plans are now equivalent: 50%/50%.

This post was inspired by real events, where the production SQL server went into overdrive trying to create and manage many temporary result tables from a stored procedure that wanted to avoid duplication using the CASE method.

Update: there is, of course, another option: creating an SQL string in the stored procedure that is dynamically modified based on the sort parameter, then the SQL executed. But I really dislike that method, for many reasons.

More information from another blogger: Conditional Order By. They also explore rank using windows functions and in one of the comments there is a reference to SQL 2008 "Grouping Sets" which I have not covered yet.