|
|
Posted by steve on 11/21/07 04:22
On Nov 20, 2:57 am, Ed Murphy <emurph...@socal.rr.com> wrote:
Hello Ed,
I appreciate your questions. I think some of things that bother
you have been brought up by others especially those who have a lot
of experience in sql. Are you an sql expert?:) I think if you continue
to investigate the relational model a light will go on and all of
a sudden it will make perfect sense:) I hope you stay with it.
Let me try to cut thru the semantics with a few examples that I
hope will clarify some of the points I'm trying to make.
In t-sql this should be perfectly clear:
DECLARE @X INT
SET @X=5
The variable @X can only take one value at any specific time.
In a relational system a procedure that returns some value at runtime
must behave exactly like @X. At runtime the procedure is a variable of
a particular type and has a specific value based on input arguments.
An sql sp has no such nature and behaves in an entirely different way.
create procedure SqlOne
@Y int
AS
SELECT COUNT(*) AS CNT,SUM(FREIGHT) AS SUMFRT
FROM ORDERS
WHERE EMPLOYEEID=@Y
SELECT *
FROM ORDERS
WHERE EMPLOYEEID=@Y
Therefore the idea that an sql procedure can return multiple results
is meaningless if the sp is realized as a variable where only a single
result makes sense. Add to this the idea of type where each result is
a different type and the difference between sql and relational should
be even clearer. Again the relational procedure is realized exactly
like the int @X. No programming language chooses among possibe
multiple definitions of the value of a variable. It would be
equivalent to:
DECLARE @X INT
SET @X=5 or @X=10
which makes no sense. It is because the sql sp is not realized as a
variable that multiple results 'can' be returned.
This sql sp:
CREATE PROCEDURE SqlTwo
@A INT OUTPUT,
@B INT OUTPUT
AS
SET @A=5
SET @B=10
DECLARE @C INT,@D INT
EXEC SqlTwo @C OUTPUT,@D OUTPUT
SELECT @C
SELECT @D
makes no sense relationally because, again, there are multiple
results. Now there are two scalar types (int) returned instead of sql
'resultsets'. Relationally there is no such thing as more than 1 thing
(think a variable of a type) at a time. Two scalar results are
realized as a 'row' type relationally, ie. 'one' thing.
create operator D4Two():row(A:Integer,B:Integer)
begin
result:=row(5 A,10 B);
end;
In this case at runtime D4Two is a variable of type row with 2 scalar
columns.
From the relational perspective a table/row/list is a variable that
behaves exactly like a variable in a programming language. Its value
can be assigned to other values just like a t-sql integer variable
can.
It can be compared to other variables (for equality) just like a t-sql
integer variable. It can be passed as an argument to a procedure just
like a t-sql integer variable. For these reasons why MS decided to
call
something a 'table variable' remains a mystery. It behaves like no
other 'variable' in any programming language on the face of the
planet:) If it doesn't quack like a duck, doesn't behave like a duck,
doesn't waddle like a duck it sure as hell isn't a duck:) What MS
calls a table variable is surely not a table variable as the idea
exists in any programming language or relationally. Whatever one wants
to call an sql table the table variable is the same thing. Its
phyiscally implementation may be different but that does not change
the fact it is not a variable of a specific table type.
Sql distinguishes between user defined functions and procedures. But
sql user defined functions are on the same exact level of procedures
when looked at from the point of view of 'variables'. Neither one
has anything to do with the idea of a relational variable. All this
artificial distinction does is serve to make it harder for users to
understand the relational model :) (Why sql choose to create a user
define function/procedure dichotomy is another topic. But think of
'where' and 'having').
Rather than center on particular synatax or pseudo-syntax I think it
is
the ideas that the relational model is based on that is important.
And what we're talking about here is just a slice of the relational
model. The relational model is not rocket science:) It's actually
quiet straightforward. Ironically it's sql that is out in left field.
The relational model is in line with all current programming
languages.
Unfortuneatly thats never been the case with sql:) This is one of the
reasons I find LINQ so unnecessary. Once you get the idea that a big
part of the relational model is all about the basic concepts of
variables and types I think (I at least hope) that what I've been
trying to explain will make perfect sense:)
best,
steve
www.beyondsql.blogspot.com
Navigation:
[Reply to this message]
|