And... to the extent that people will say "it's got it now"... this the same situation people have with mysql - spouting off about stuff that it didn't do 10 years ago, and don't use it (or postgres from X year ago, etc). Once you hit some rather stupid/useless limitation, it tends to stick with you and influence your choices in the future.
I've used SQL Server on projects since then, but it's never been my choice to start a project with it. Clients have used it, and I've used it because that's what they had opted for, but it wasn't my choice or suggestion.
As someone else noted, connecting to it from outside the world of Windows/.Net is a bit of a pain. The PHP connector situation is weird - it's changed in 5.3, and you seem to have to download a separate connector from MS directly. It'd be super swell if they'd just open source that connector so it could be distributed around (or provide a binary and license terms so at least WAMP-stacks could distribute it more easily).
The SQL standard is missing couple more features I think, like identity/sequences (I even had to use different code in .NET to get the newly inserted id), upserts, ...
Writing cross-database software isn't even worth it. And I say that as a developer of a medium sized project supporting 4 databases. It would have been much better if we just decided to use one and use all the features properly
Also in SQL 2012 now you can use the offset-fetch clause to implement paging, check this: http://sqlwithmanoj.wordpress.com/2011/12/30/creating-stored...
I worked someplace that was primarily a SQL Server shop, although was still largely Java and some PHP at the time. I was tasked with building something to paginate through records. There were several hundred thousand to paginate through, and I had to resort to getting the DBA to build some weird sproc with cursors and junk in it.
"I just need some of the rows"
"Why would you need that?" (honest to goodness question from the DBA)
"Well, because I only need to show 30 items on the screen, not 400,000. Showing 400,000 might take a while."
"Just use TOP. SQL Server lets you do SELECT TOP(30) already!"
"Umm... but I might need to see the second 30, or the 99th 30."
"Why? No one does that!"
Insane. They experimented with some monstrosity of nested TOP() queries. They refused to allow MySQL to be used ("it's just a toy"), when it was perfectly capable for the requirement, and had developer-useful stuff like LIMIT in it. I got some overly engineered sproc that had to be updated whenever I needed a change, and I ended up leaving a few months later. Not specifically because of that, but that culture was one I couldn't fit in to. I've heard it's gotten better, but I don't really believe it's better so much as the people who've stayed there have adapted and worked around the cognitive dissonance it triggered.