Mostly when you work with single Sql Server or monolithic application , there is a less worry about consistency model. Once you start working in distributed system you need to consider consistency models.

Data-Centric Consistency Models

1. Strict Consistency model
2. Sequential Consistency
3. Linearizability
4. Causal Consistency
5. FIFO Consistency
6. Weak consistency
7. Release Consistency
8. Entry Consistency
9. Processor consistency
10. General consistency
11. Local consistency
12. Cache consistency

Client-Centric Consistency Models

1. Eventual Consistency
2. Monotonic Reads Consistency
3. Monotonic Writes
4. Read Your Writes
5. Writes Follow Reads