Depends on hat you need.
The main important difference is that SQL Consistency is "one data-blob" on one machine that doesn't shard. That's why they guys started the #nosql movement with a twitter hashtag. google and other companies moved away from 1 big data storage to a lot of small machines so the problem was:
A SQL transaction is over when the hardisk tells the machine "everything is persisted now" this means all data need to be on the same "machine". So if another machine needs the data it always has to wait for it. With NoSQL the data can be distributed on different machines because they have different transaction models.
Besides that I always think about how I need the data stored and how I have the least transformations on it. Or what Vector PACELC I do need.
Do I need consistency over availability ? And so on :)