Very Computer

J - For comparison information check out
http://www.microsoft.com/sql/productinfo/compare.htm .  SQL Server triggers
are post-modification (they are part of the transaction itself - meaning you
can rollback the transaction within the trigger).  You'll want to do your
data validation prior to the transaction via constraints, rules, defaults,
nullability of the column, the datatype and possibly (if not from the
others) via stored procedures prior to the transaction.  SQL Server gives
you the capability to autoincrement a field either using an IDENTITY
property on an integer field or in v7 by using a GUID of the
uniqueidentifier datatype wiht a default of NEWID().  For the detecting
changed values, SQL Server provides an IF UPDATE clause to the update
trigger to test if a column has been updated (in addition you can utilize
the INSERTED and DELETED logical tables (basically views to the log) to view
what modifications have taken place).

JS

Quote:> I received these critiques of MS SQL Server from an Oracle devotee doing
an
> appraisal for a client of merits of various db systems. They seem valid
and
> having never used Oracle, I am not really in position to compare the two.
> What would be the practical advantages/disadvantages of SQL vs Oracle,
> beside such things as TCO, interface etc. Or are there any whitepapers,
> articles comparisons recommended.

> Triggers. SQL Server does not have the ability to trigger both before and
> after an action has taken place. It can only trigger after an action. This
> means that for many actions, code must be written back at the client end
> rather than a trigger performing the action at the server. In turn this
> means a lot more coding, and much slower performance, and de centralised
> business logic.
> Generators or Sequences This concept does not exist in SQL Server. Instead
> they use Auto Incrementing Columns called Identities.  Although this is
> acceptable for low end products like access, it is not for a corporate
> database scenario. There are many negative consequences of this technology
> ommission.

> Stored Procedure Language and nesting SP's into another DML statement.
This
> is not possible in SQL 7 because the SQL7 SP language forces at least 3
> steps. Provide input parameters, Execute, Select the output values. An
> example of how this effects development is that batch inserts using stored
> procedure outputs are not possible. Eg insert into T1 select MyProcedure,
> Column1, Column2 from  T2 is not possible.

> Once again this can be worked around, but the result is always a
performance
> hit,  extra coding, and decentralisation of the business logic.

> Detecting changed values in specific columns in an Update Trigger

> With SQL Server this is done by coding bit evaluation arithmetic based on
> the column position. Not only is this somewhat clumsy,  but because it is
> based on the column absolute position, any table changes must be followed
by
> re development of the stored procedure library.

I received these critiques of MS SQL Server from an Oracle devotee doing an
appraisal for a client of merits of various db systems. They seem valid and
having never used Oracle, I am not really in position to compare the two.
What would be the practical advantages/disadvantages of SQL vs Oracle,
beside such things as TCO, interface etc. Or are there any whitepapers,
articles comparisons recommended.

Triggers. SQL Server does not have the ability to trigger both before and
after an action has taken place. It can only trigger after an action. This
means that for many actions, code must be written back at the client end
rather than a trigger performing the action at the server. In turn this
means a lot more coding, and much slower performance, and de centralised
business logic.
Generators or Sequences This concept does not exist in SQL Server. Instead
they use Auto Incrementing Columns called Identities.  Although this is
acceptable for low end products like access, it is not for a corporate
database scenario. There are many negative consequences of this technology
ommission.

Stored Procedure Language and nesting SPs into another DML statement. This
is not possible in SQL 7 because the SQL7 SP language forces at least 3
steps. Provide input parameters, Execute, Select the output values. An
example of how this effects development is that batch inserts using stored
procedure outputs are not possible. Eg insert into T1 select MyProcedure,
Column1, Column2 from  T2 is not possible.

Once again this can be worked around, but the result is always a performance
hit,  extra coding, and decentralisation of the business logic.

Detecting changed values in specific columns in an Update Trigger

With SQL Server this is done by coding bit evaluation arithmetic based on
the column position. Not only is this somewhat clumsy,  but because it is
based on the column absolute position, any table changes must be followed by
re development of the stored procedure library.

Jonathan,

Quote:>Triggers. SQL Server does not have the ability to trigger both before and
>after an action has taken place. It can only trigger after an action.

True.  Also the trigger fires once per statement, there is no option
to fire once per row.

Quote:>This
>means that for many actions, code must be written back at the client end
>rather than a trigger performing the action at the server. In turn this
>means a lot more coding, and much slower performance, and de centralised
>business logic.

To keep the logic on the server use a stored procedure.  Triggers are
just a special sort of stored procedure, so it is just a matter of how
the code is divided between the proc and trigger.

Quote:>Generators or Sequences This concept does not exist in SQL Server. Instead
>they use Auto Incrementing Columns called Identities.  Although this is
>acceptable for low end products like access, it is not for a corporate
>database scenario. There are many negative consequences of this technology
>ommission.

I am not familiar with Generators or Sequences, but I am willing to be
that they don't fit the relational model and can not be found in the
SQL-92 standard.

Quote:>Stored Procedure Language and nesting SP's into another DML statement. This
>is not possible in SQL 7 because the SQL7 SP language forces at least 3
>steps. Provide input parameters, Execute, Select the output values. An
>example of how this effects development is that batch inserts using stored
>procedure outputs are not possible. Eg insert into T1 select MyProcedure,
>Column1, Column2 from  T2 is not possible.

If this means that SQL Server does not support user written functions
it is quite true.

Quote:>Once again this can be worked around, but the result is always a performance
>hit,  extra coding, and decentralisation of the business logic.

All I have to go on is code I have seen posted on-line giving examples
of what has been done in user defined functions in Oracle.  In this
limited set of three or for examples, all have been possible to
implement in SQL Server without a user defined function.  They were
implemented with CASE expressions.  CASE is part of the SQL-92
standard which I believe is not implemented in Oracle.  It is
extremely powerful, and the feature I would cry over loosing if I
switched.

Certainly CASE can not substitute for really complicated function, but
it can help sometimes.

Quote:>Detecting changed values in specific columns in an Update Trigger

>With SQL Server this is done by coding bit evaluation arithmetic based on
>the column position. Not only is this somewhat clumsy,  but because it is
>based on the column absolute position, any table changes must be followed by
>re development of the stored procedure library.

I don't know where this came from, but it sure is not SQL Server.  Bit
coded column positions?  I've never heard of such a thing!

Roy

Quote:> I don't know where this came from, but it sure is not SQL Server.  Bit
> coded column positions?  I've never heard of such a thing!

> Roy

Roy,

Perhaps the COLUMNS_UPDATED() function?

(Pls note that there exist a UPDATED() function, which _do_ go by column
name.)

--
Tibor Karaszi
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.

Tibor,

Quote:>> I don't know where this came from, but it sure is not SQL Server.  Bit
>> coded column positions?  I've never heard of such a thing!

>Perhaps the COLUMNS_UPDATED() function?

Sure enough, one of those new features I keep missing.  I guess it
could make the IF test a bit simpler since it can match any number of
columns in a single mask.

Quote:>(Pls note that there exist a UPDATED() function, which _do_ go by column
>name.)

Which is of course what we have had as long as we have had triggers.

Thanks for the correction.

Roy

You can't look at just functionality or just cost. You need to consider
both.

Oracle has some extra features, but to get those few extra features, Oracle
will cost approx. 10x the $ of SQL Server.

Because of the huge cost difference, quite a few people are deciding they
either don't need or can work around things like before triggers within
their application code and don't feel the few extra features in Oracle are
worth the huge extra investment.

My opinion would be that SQL Server has 90% of the functionality of Oracle8
(and closing the gap quickly), but  at only 10% of the cost.  You have to
decide what that 10% is worth.

A couple of issues that you left out include:

DBCC CHECKDB, Books Online recommends that you run this process before each
backup to insure that your database isn't corrupt.  This idea is pretty much
foriegn to Oracle DBAs.  The idea that a database can be running in a
corrupt state without throwing any errors would probably be considered
unacceptable by many.

The other big difference that I see is how a select for data that is being
updated is handled.  It appears that the SQL won't allow reads until the
update is complete.  This means that the reader will be blocked.  Oracle
will provide a response based on the last consistent point of the data (just
before the transaction that is doing the update began).

I am new to SQL Server so if anyone out there sees that I am way off base
please correct me.

Eric...

Quote:> I received these critiques of MS SQL Server from an Oracle devotee doing
an
> appraisal for a client of merits of various db systems. They seem valid
and
> having never used Oracle, I am not really in position to compare the two.
> What would be the practical advantages/disadvantages of SQL vs Oracle,
> beside such things as TCO, interface etc. Or are there any whitepapers,
> articles comparisons recommended.

> Triggers. SQL Server does not have the ability to trigger both before and
> after an action has taken place. It can only trigger after an action. This
> means that for many actions, code must be written back at the client end
> rather than a trigger performing the action at the server. In turn this
> means a lot more coding, and much slower performance, and de centralised
> business logic.
> Generators or Sequences This concept does not exist in SQL Server. Instead
> they use Auto Incrementing Columns called Identities.  Although this is
> acceptable for low end products like access, it is not for a corporate
> database scenario. There are many negative consequences of this technology
> ommission.

> Stored Procedure Language and nesting SP's into another DML statement.
This
> is not possible in SQL 7 because the SQL7 SP language forces at least 3
> steps. Provide input parameters, Execute, Select the output values. An
> example of how this effects development is that batch inserts using stored
> procedure outputs are not possible. Eg insert into T1 select MyProcedure,
> Column1, Column2 from  T2 is not possible.

> Once again this can be worked around, but the result is always a
performance
> hit,  extra coding, and decentralisation of the business logic.

> Detecting changed values in specific columns in an Update Trigger

> With SQL Server this is done by coding bit evaluation arithmetic based on
> the column position. Not only is this somewhat clumsy,  but because it is
> based on the column absolute position, any table changes must be followed
by
> re development of the stored procedure library.

In regards to CHECKDB, that is really old info. If you are running SQL
Server 7.0, it is probably no longer necessary to run it. Corruption is very
rare.

As for Oracle, I have seen Oracle DBs run with corruption. In one case, a
really large warehouse with a huge set of clustered tables had a bad block
which pointed to a previous block instead of the next. You were ok as long
as you didn't do a scan through that block. If you did, your scan would loop
forever. Oracle did not have a utility similar to DBCC CHECKDB. All they
have is dbverify, which is a much lower (filesystem) level checker. Doesn't
do the same job.

Kerry