Lots of good tidbits and examples in there - I really enjoyed reading that. There are several not-quite-inaccuracies-but-not-really-how-you-would-think moments in it but, overall, it's very well written. The pictures were annoying after a while haha.
Just to clarify, this isn't "specific to SQL Server" - SQL Server was originally a straight-up "licensed by Microsoft" version of Sybase SQL Server and thus it's early code base (things that defined the Transact-SQL language) has almost all to do with Sybase and naught to do with Microsoft. Things like the plus sign as a string concatenator, the way numbers are handled when compared to strings - all of that was the way Sybase did it and, for whatever reason, Microsoft didn't change it (we're talking way back in the late 80s and early 90s). In fact, there's not a single thing in that article that has changed or been implemented by Microsoft - nearly every one of the nitpicks/topics referred to by OP refer to the original choices made by the Sybase engineers. You could say, "Yes, but Microsoft hasn't changed this despite having 20+ years with the source code + standards!" and that's a valid point.[0]
1) TRIM - This has been asked for and discussed ad nauseum in the SQL Server and Sybase community. It always circles around the idea of VARCHAR though - the variable character type automatically trims trailing blanks thus, years ago, developers said, "Ahhhh, screw it - no need for a TRIM() function! They just should use the LTRIM() function to get rid of the leading blanks." It's not correct IMO but that's always been the logic.
2) "This behavior violates the standard and is specific to SQL Server." - I hate this statement. Which standard? What section? This is a case of it being easy to say something but, without backing it up with a citation, it implies that I trust the OP. I dont' know OP - maybe he's right but maybe he's interpreted "the standard" incorrectly, or is referring to an outdated standard, or is comparing SQL Server 2000's interpretation of a later-written standard. There's no way to know - it's just too ambiguous.
3) ORDER BY in subqueries - I laughed when I saw this:
SELECT * FROM
(SELECT TOP 100 PERCENT* FROM mytable... ORDER BY id) q
In nested queries and inline (single-query) table valued functions, ORDER BY is not supported unless TOP is used with the SELECT.
Kinda, sorta - it depends on which version of SQL Server you are referring to. SQL 2012 - yes. SQL 2008 - yes. The "original" SQL Server would have done what OP talks about - sorting w/ TOP - but, at some point, MSFT changed it to give the behavior he talks about. I can't remember when - maybe SQL 2005 or, at worst, 2008 - but this is an example where using specific version names would be helpful.
Just to clarify, this isn't "specific to SQL Server" - SQL Server was originally a straight-up "licensed by Microsoft" version of Sybase SQL Server and thus it's early code base (things that defined the Transact-SQL language) has almost all to do with Sybase and naught to do with Microsoft. Things like the plus sign as a string concatenator, the way numbers are handled when compared to strings - all of that was the way Sybase did it and, for whatever reason, Microsoft didn't change it (we're talking way back in the late 80s and early 90s). In fact, there's not a single thing in that article that has changed or been implemented by Microsoft - nearly every one of the nitpicks/topics referred to by OP refer to the original choices made by the Sybase engineers. You could say, "Yes, but Microsoft hasn't changed this despite having 20+ years with the source code + standards!" and that's a valid point.[0]
1) TRIM - This has been asked for and discussed ad nauseum in the SQL Server and Sybase community. It always circles around the idea of VARCHAR though - the variable character type automatically trims trailing blanks thus, years ago, developers said, "Ahhhh, screw it - no need for a TRIM() function! They just should use the LTRIM() function to get rid of the leading blanks." It's not correct IMO but that's always been the logic.
2) "This behavior violates the standard and is specific to SQL Server." - I hate this statement. Which standard? What section? This is a case of it being easy to say something but, without backing it up with a citation, it implies that I trust the OP. I dont' know OP - maybe he's right but maybe he's interpreted "the standard" incorrectly, or is referring to an outdated standard, or is comparing SQL Server 2000's interpretation of a later-written standard. There's no way to know - it's just too ambiguous.
3) ORDER BY in subqueries - I laughed when I saw this:
SELECT * FROM (SELECT TOP 100 PERCENT* FROM mytable... ORDER BY id) q
In nested queries and inline (single-query) table valued functions, ORDER BY is not supported unless TOP is used with the SELECT.
Kinda, sorta - it depends on which version of SQL Server you are referring to. SQL 2012 - yes. SQL 2008 - yes. The "original" SQL Server would have done what OP talks about - sorting w/ TOP - but, at some point, MSFT changed it to give the behavior he talks about. I can't remember when - maybe SQL 2005 or, at worst, 2008 - but this is an example where using specific version names would be helpful.
[0] http://en.wikipedia.org/wiki/Microsoft_SQL_Server#History