I would say that's generally true. SQL Server, or relational database engines in general, are ideal for applications where the entities are normalized tables and data access patterns are predefined. If you're wanting to build a text keyword search engine or store binary blobs or JSON documents, then there better NoSQL alternatives. Likewise the NoSQL alternatives are no good for transactional applications like CRM, inventory, or accounting.

However, I see you're posting in a SQL Server 2008 forum. Versions 2012, 2014, and 2016 have introduced many new features for working with non-relational data and NoSQL queries.