- Performance
- Consistency
- Security
Basically, you want to construct a public API in the form of stored procedures, functions and views, and then funnel all clients through that API, by granting execute/select permissions only on the artifacts which are part of the public API.
That can be good for performance not just because computation is close to data so you don’t pay the communication cost, but also because it opens optimization opportunities that might be impractical on the client, e.g. by returning several interdependent resultsets at once, or by cleverly using temporary tables or cursors.
It also ensures that a rogue client can’t circumvent the consistency rules implemented by your API. If you care about your data, this is a big one!
And finally, the API may be a natural place for performing authorization in case your security needs cannot be readily met by the database’s built-in security.
—
Perhaps the biggest argument against it is the fact that most SQL-based languages are firmly stuck in the 80s. For the most part, they’re just pain to work with. But enduring that pain might very well be worth the end result.