I do think NoSQL has a place in the world. There are domains of problems that I'm sure Riak, MongoDB, and others, solve in a more efficient way than SQL Server, Oracle, MySQL, and other relational systems. I'm not sure what they are, and to some extent, I haven't seen good guidance on where particular platforms excel.
This might be a nice start:
http://readwrite.com/2011/02/03/the-big-list-of-nosql-use-case[/url]
One case I personally like that calls for a non-sql solution is a need for fast RBAR. With SQL Server, you either get pokey single row selects, inserts and updates, or nice and fast set oriented operations. What about fast RBAR?
I do hold out hope that SQL Server 2014 might address this.