It's all about speed. When you need really fast OLTP processing, you go with the in-memory tables and the compiled procedures. The limitations are too severe for any small or simple implementations.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning