Just a quick post of some benchmarking code for our good friend Guayo at TheDailyWTF, in regards to this discussion. The debate is, essentially: Assuming everything is properly indexed, which is faster/more efficient?
Read more →
When you have two tables (or resultsets from SELECT statements) that you wish to compare, and you want to see any changes in ANY columns, as well as to see which rows exist in 1 table but not the other (in either direction) I have found that the UNION operator works quite well.
Read more →
I just wanted to take a minute to highlight this thread in the sqlteam forums:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39736
It's really interesting stuff, and a bunch of us worked together to come up with some really cool ideas about using T-SQL to solve the classic sliding tile puzzle -- you know, where tiles on a square board are mixed up, with one missing, and you slide them around until the picture is complete or they are in the proper order.
Read more →
As some of you may know, I recommend to avoid using CASE expressions in the WHERE clause of a query to express conditional logic. I prefer to have everything translated to simple ANDs, ORs and NOTs to keep things a) portable, b) easier to read and c) efficient.
Read more →
I recently helped someone with performing some simple cross tabs (or pivots) in SQL, such as:
Select CustomerID, SUM(CASE WHEN type = 'Land' THEN Amount ELSE 0 END) as Land, SUM(CASE WHEN type = 'Sea' THEN Amount ELSE 0 END) as Sea, SUM(CASE WHEN type = 'Air' THEN Amount ELSE 0 END) as Air FROM SomeTable GROUP BY CustomerID
Read more →