|
|
Posted by Erland Sommarskog on 03/22/06 14:05
Herb Caudill (herb@caudillweb.com) writes:
> Maybe the problem here is that I want SQL to be something more than it
> is. As an object-oriented programmer, I expect to be able to encapsulate
> logic at the lowest possible level.
An error which is not uncommon to make when you have a very good hammer,
is to also use it to draw screws.
The object-oriented model is very elegant, and has its benefits.
But a relational database is not in Kansas, and applying too much O-O
thinking in a relational database is going to lead you into problems.
Not because O-O is inferior or relational is better, but just because it's
different. Object-oriented databases have seen the light of day, but they
never took off, for reasons unknown to me. The reason relational databases
have become so dominating is that they have proven to be able to handle
huge volumes of data with good performance to reasonable development costs.
> A similar problem having to do with encapsulation - perhaps a topic for
> another thread - is that I can't write a generic function to perform
> consistent operations against multiple tables. For example: Most of my
> applications have a number of tables that are all subject to the same
> versioning and approval logic, which is a little complex. A table-valued
> function like this would be very useful to me:
>
> Create Function GetApprovedItems(TableName as varchar(50)) returns Table
I don't know about your databases, but usually when people want to
parameterise on the table name, there is a design flaw. Many tables should
really have been one table, with one more key column.
It's important to understand that in a relational database, tables are
very unique entities. If you have two functions that have exactly the
same code, save the table name, that does not mean that they will execute
the same. In fact, even if the table name is the same, but the databases
are different, the query plan will be different.
Here is a very important difference to traditional programming, including
O-O. Here the program code is a very detailed instruction on what operations
to do, in which order to access data etc. There is an optimizer, but it
mainly manages smaller parts.
But in SQL you basically only describe what result you want, the optimizer
will find out the best way to compute that result. All tables in SQL Server
has statistics, and the statistics are unique to the table, because the
statistics describe the data in table, and different statistics gives
different query plans.
An implementation or table-name parameters would certainly lead to that
the same module could have multiple query plans, and it could be quite
confusing for pprogrammer that do not understand this.
Again, while you don't care about performance, most users of SQL Server do,
and focus on performance will always be very stronng in SQL Server.
> can't include non-deterministic blah-blah-blah, but I don't care. The
> point is that I don't have any good options. Here they are:
There is one more that you did not mention: you can use a pre-processor.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|