Re: theory and practice: ying and yang

From: Misha Dorman <misha_at_no_mishapen_spam.co.uk>
Date: Tue, 07 Jun 2005 22:51:34 +0100
Message-ID: <11ac5j2gj9uf0a3@corp.supernews.com>

Alfredo Novoa wrote:

> On Wed, 01 Jun 2005 10:53:52 +0200, Alexandr Savinov
> <savinov_at_host.com> wrote:

>>Hm. Here again it is not so clear. There is an opposite wide spread
>>opinion that the best way to design your database consists in
>>implementing everything via stored procedures (which then may use SQL or
>>other means to access data).

>
> But it is a profoundly uninformed opinion.

It appears to be a corruption based on pragmatics of poor declarative support in SQL DBMSs.

The basic principle ("keep business logic in the DB") is sound, but historically with popular SQL DBMSs that has meant "implement everything as SPs" and the original principle has been forgotten/de-emphasised.

It is more important to remember the basic principle. For example, with more recent SQL DBMSs (with better support for updateable views, parameterised views and table-valued UDFs, for example, as well as better DRI and procedural [trigger] support) the "practical" advice to use SPs needs to be re-evaluated, since the declarative approaches are better (primarily because they can be combined more simply - a join between two views is trivial to write, and the CBO will generally do a decent job, whereas a join between two SPs that return tables involves temp table vars etc. and will rarely be as efficient). Received on Tue Jun 07 2005 - 16:51:34 CDT