gsexton at mhsoftware.com
Thu Jun 29 10:11:57 MDT 2000
I would be happy to present on SQL Anywhere.
Wayde: Just let me know the proposed date and I will coordinate with Sean.
Here is my take on why the features I listed are important.
1) Declarative Referential Integrity
This means that you define the relationships in the data and the database
enforces them. If you have a coding error that would violate the
relationship, then the update or insert fails. This just makes programs
faster to develop and results in fewer coding bugs. Postgresql supports this
in version 7.0. MySQL does not.
2) True Transaction Log
The transaction log stores a history of updates. If the drive containing the
database fails, and you have the transaction log on another drive (which I
do), then you can restore from your last backup and apply the transaction
log. IOW, you lose almost 0 data.
3) Stored Procedure Language
On any sufficiently complex database you will have reports that cannot be
created without resorting to procedural code. People will want some complex
set of queries that cannot be coded in a single statement. The simplest way
to handle this is to use a stored procedure and call that stored procedure
from your reporting tool. It can also be useful for performing data
intensive operations because only the result set is returned by the server.
If you have some very complex operation that requires a table scan of a
400MB table, this can really boost performance. The down side to using
stored procedures is that if you want to change databases, this is code that
must be re-written.
4) Outer Joins
Again, given any sufficiently complex database you will need these. A normal
or equijoin, says something like:
Inner Join Invoice
or in older style
Select * from Customer, invoice where Invoice.Cust_ID=Customer.Cust_ID
This join fails in the instance for customers where there is no invoice. A
typical report might be: Prepare a summary report of customers that shows
all customers, the number of invoices, and the sum of sales for a given date
range. With an outer join you would write this as:
count(Invoice.Invoice_No) as InvCount,
sum(Invoice.Total) as InvSum
More information about the LUG