Dataphor SQL RAC (Relational Application Companion)

A site of hope for those looking for a true relational database system

Wednesday, October 17, 2007

Sql - Constantly struggling

The subject of this article is called (by others) database constants.
So lets be sure we're on the same page with what a constant is.
It is just a value.

X = Substring(Y,1,5);

X and Y are variables, 1 and 5 are values.

X = Y * 5;

X and Y are variables, 5 is a constant. It is a number whose value is 5.
A table in D4 is just as much a variable as X and Y. A table in 
sql is like the value 5, a constant. The difference between a constant
and a variable is also the difference between D4, a relational
database, and sql!

The following is part of an exchange I had with someone on the
issue of database constants. The idea of database constants
was raised in the article:

A New (and Hopefully Better) Approach to Constants

Here I go ☺ :

Sql Server lets users define a 'table variable'. Now you really
don't believe it's a table 'variable' do you?:)  Of course not.
They're just playing with words. There is no such thing in sql
server as a table variable. But because they call it that most
users really believe it. It's like believing in the tooth fairy:)
Now we're going to play the same game with database 'constants':)
There is no such thing as a database constant. There are only
values (which can't be updated or assigned other values) and
variables (which can be updated and assigned other values).
But in sql there is no real talk of these basic things. And
that's why I talk about Dataphor ☺. There is no difference between
what this guy is calling a 'constant' and a table or 'resultset' in
sql. They are all (constant) values and not variables. I'm trying
to show what sql would be like if there really were table 'variables'.
One way was with the CTE article, 'Do you know what the Sql CTE really is?'
But people seem to be having a hard time wrapping their head
around it:) This sql stuff just gets crazier. Now people are
going to believe that you can pass a table as a parameter to a
stored procedure. That is nonsense. One reason people believe it
is they have nothing to compare/constrast it with. So I show what
it really means to pass a table to a sp:

But to do these kind of things requires a foundation different than
sql. Hence the name of my site ☺ .
The so called 'constants' are basically a table without a key, ie a row.
So I do:
create table Y

insert row{1 A,'Micheal' B} into Y;

select Y[].B
select Y[].A
where the '[]' is called a row extracter.
Or I can define a 'list':
var LList:=list(String){'Michael','Steve','Jimmy'};
select LList[0];

Or I can define a table (ie a row) with lists.
create table Z
 Strings:list(String),  //The column holds a list of strings.
 Integers:list(Integer),//The column holds a list of integers.

insert row{{'Micheal','Steve','Jimmy'} Strings,{2,10,22,40} Integers}
into Z;

select Z[].Strings[2];
select Z[].Integers[1];
where the [2] and [1] refer to the ordinal position of the item (value)
in the list.
But sql doesn't have a table 'type' or a list type so it couldn't
understand these things. The only thing sql can do is try to simulate
these types. And it winds up in a mess of gooblygook that few understand
and robs the user of the integrity that the database should provide
by supporting these things directly. All this stuff is based on values,
variables and types. The only types that exist is sql are numbers and
strings. Again there is no type for tables, row or list. And therefore
no variables for these things because without a type you can't define
a variable. Make sense ☺ .
var X:= Orders //X is a variable of type table, specifically the type
               //of the Orders table (the column names and there data types).
This is the huge step forward from sql. In sql the result of a query
is no different than the 'Constant' that guy was trying to talk about ☺ .
It is also the difference between a CTE and a variable that holds the
result of the 'CTE'. You can't reuse a CTE in sql because it's a 'constant',
a value. You can only reuse a variable.

No comments:

About Me

My photo
Phoenix, Arizona, United States