NoSQL is a buzzword, originally to describe "distributed open source databases" but it's catchy enough that pretty much every nonrelational db uses it.
So saying "NoSQL gives you [or doesn't give you] X" is meaningless.
And for the record, SQL is great, but trying to keep using it once your data set has outgrown a single machine is like "trying to build a bookshelf out of mashed potatoes." (Gotta love jwz.)
but NoSQL kinda gives you memcache like awesomeness with some level of persistence. Think of cassandra as a more feature-rich, persistent memcache. :)