GilaMonster (8/28/2015)
Ed Wagner (8/28/2015)
I've seen the TOP 100 PERCENT...ORDER BY hack used in actual view definitions. I know it worked in SQL 2005, but I seem to recall it not working in a later version. I wish I could remember which version, but it's still a hack.It worked in SQL 2000. It was one of the breaking changes to 2005 that the optimiser could ignore ORDER BY with non-row limiting TOP. There was a traceflag to allow the SQL 2000 behaviour in 2005, I can't remember what versions the traceflag worked in.
Sweet! Thank you, Gail. The semi-ironic part is that the database where I saw it is still on 2005. I'm setting up the new 2012 server next week, after which we're going to be migrating the databases. The whole exercise should be a lot of fun. 😉