Sql - What does it mean to set a value?

Sql - What does it mean to set a value?

Using t-sql in Sql Server it's perfectly acceptable to use a SET statement to set a value
for a variable. For example, this batch sets the variable @MyRows to the number of rows in
the Orders table from the Northwind sample database:

DECLARE @MyRows int;
SET @MyRows = (SELECT COUNT(*) FROM Orders);
PRINT LTRIM(Str(@MyRows)); -- Returns 830

Now this operation makes perfect sense. Use an aggregate function (count) to derive a scalar
value and set it to a variable.

In the relational language of Dataphor, D4, the same operation can be expressed as:

var MyRows:Integer:=Count(Orders);

In D4 we can confirm that the expression Count(Orders) returns a scalar value of type
integer by using the Clintonian 'is' construct which tests for a particular type and
returns a boolean (true/false).

select Count(Orders) is Integer;//Returns True

We can also dispense with the Integer declaration and allow the integer type for variable
MyRows to be derived from the expression:

var MyRows:=Count(Orders);

Now lets suppose we want to set a value based on the value of a column in the Orders table
for a particular row. The following batch makes use of OrderID as a primary key and targets
a particular row for its Freight value:

DECLARE @MyFrt MONEY;
SET @MyFrt = (SELECT Freight FROM Orders WHERE OrderID=10249);
PRINT CAST(@MyFrt AS VARCHAR(8)); -- Returns 11.61

So the same operation that sets an aggregate value, a value derived about a table, also
sets a value from within a table. This state of affairs seems to be accepted without
question by sql folks. Well lets take a closer look at this from a relational perspective.
The closest D4 construction to the sql batch would be:

var MyFrt:=(Orders where OrderID=10249 {Freight});

And if the D4 operation is the same as the sql operation the MyFrt variable should be a
scalar value of type Money.

var MyFrt:=(Orders where OrderID=10249 {Freight});
select MyFrt is Money //Returns False.

Of course MyFrt isn't a scalar, it's a table!

var MyFrt:=(Orders where OrderID=10249 {Freight});
select MyFrt;

Freight //A table with a single row and column.
-------
$11.61  

select MyFrt is table{Freight:Money}; //Returns True confirming MyFrt is a table.

Variable MyFrt is a table with a single column named Freight with a type of Money. The table
happens to have only a single row but it is still very much a table! ☺ Now what could lie
between a table and a scalar value from within the table? There's only one possible thing,
a row ☺ What's needed is to extract out a row from the table, to transform the table type
to a row type. We do this with a thingie called a pure row extractor, '[]'.

var MyFrt:=(Orders where OrderID=10249 {Freight})[];
select MyFrt;

Freight //A row with a single column whose name is Freight and type is Money.
-------
$11.61

select MyFrt is row{Freight:Money}; //Returns True confirming MyFrt is a type of row.

Once we have zeroed in on a row we can get to a scalar value, a value of a column in the row.
We do this by using a column extractor which is the dot ('.') followed by the column name.
So to finally set the variable to the Freight value of the row we have:

var MyFrt:=(Orders where OrderID=10249 {Freight})[].Freight;
select MyFrt;

$11.61 //A scalar value of type Money.

select MyFrt is Money; //Returns True confirming the type of MyFrt is a scalar type (Money). 

We can set the value using a more succinct expression by using the primary key value (OrderID)
to directly extract the row (eliminating the WHERE clause) and then extracting the Freight value:

var MyFrt:=Orders[10249].Freight;
select MyFrt; //Return $11.61

Going back to the sql version we can even assign the Freight value to a string:

DECLARE @MyFrt varchar(10);
SET @MyFrt = (SELECT Freight FROM Orders WHERE OrderID=10249);
PRINT @MyFrt; -- Returns 11.61

What we have here is an example of three implicit conversions in one statement! To accept
the idea that sql is even relational like is to accept the implicit conversion of a
table to a row followed by a row to a scalar (money) followed by a money type to a string.
This of course is logically ridiculous. There is no such thing as an implicit conversion in
a relational system. There is no principle in any theory or in contemporary computer science
to derive a scalar value from a column(s) in a table without explicit transformations. There
is no logic to what sql is doing here only pure exigency. Which is what you get when essential
types like table and row are missing. In Sql when there are multiple rows that satisfy a
a WHERE clause the variable is left undefined and an error generated:

DECLARE @MyFrt MONEY;
SET @MyFrt = (SELECT Freight FROM Orders WHERE OrderID<=10249);
-- Subquery returned more than 1 value. This is not permitted when the subquery follows
-- =, !=, <, <= , >, >= or when the subquery is used as an expression.

This is really a valid logical operation, an attempt to set (assign) a table to a variable.
This is precisely what D4 does:

var MyFrt:=Orders where OrderID<=10249 {Freight};

select MyFrt;

Freight //A table.
-------
$11.61
$32.38

select MyFrt is table{Freight:Money};//Returns True confirming MyFrt is a table type.

The sql situation is nothing but logical confusion. The operation has nothing to do with
setting a scalar value. It is a table assignment. But sql doesn't know from such a thing
and makes up a spurious error. The so called aggregate concatenation query
(http://support.microsoft.com/default.aspx/kb/287515) is also illogical. That a query, in
the absence of a dedicated aggregate function (ie. concatenate), that returns a table is
supposed to behave like looping over a cursor and concatenating values to a variable is silly.

Perhaps the best that can be said of sql in situations like this is that results, even a
correct result, is based on whimsy instead of a logical foundation. But it's not hard to
understand the attraction of the whimsical nature of sql ☺ The prevalence of sql based on
the susceptibility of users to science fiction, as opposed to computer science, and the
spreading of the infection is why sql is the unchallenged viral language of databases.
Hopefully logic and common sense will halt its spread. But its arrest remains a challenge ☺

Is there really a table variable in sql server?

Is there really a table variable in Sql Server 2005?
I say no! ☺

The following is a response to the authors comment in his blog post:

Louis Davidson (drsql)
2008: Declaring and instantiating a value
http://sqlblog.com/blogs/louis_davidson/archive/2008/03/01/2008-declaring-and-instantiating-a-value.aspx 

concerning his assertion of a table variable. As of now the author has not yet accepted my comment
for his blog ☺ You can read the full blog article for another interesting comment I made
that was accepted ☺

The authors (drsql) comment:

drsql said:

Steve (and yes it was repeated, but I just kept the last one),

I think you missed the point of the comment (I know I did when I first read it.)  It isn't :

DECLARE @CashMoney TYPE_OF(<table>);

it is

DECLARE @CashMoney TYPE_OF(<table>.<column>);

In other words, you have a table:

CREATE TABLE fred

(

   value varchar(10)

)

DECLARE @value  TYPE_OF(fred.value)

So what you would end up with would be a variable of type varchar(10).  Now do:

ALTER TABLE fred ALTER COLUMN value varchar(100)

When the code gets recompiled, @value would be varchar(100).

>>And all the connect feedback in the world won't change sql to be able to support a type of table variable.<<

We already have the concept of a table variable in 2005.  You can declare:

declare @fred table(value char(1))

Taking the type_of ide a bit further along these same lines though, you would might get to do:

DECLARE @fred  TYPE_OF(fred)

Which would declare a table variable with the structure of the fred table, rather than having to type it out again., 
but either way, you wouldn't be talking about just the type.  

Taking it one step further, you would be able to do:

DECLARE @fred  TYPE_OF(fred) = (select * from fred)

and get a copy of the table into the variable, without having to code the tedious bits of SQL.  

You cannot use a query like this on a table instantiation, but you can use a query on a variable 
instantiation, which I have updated the post to note.
March 3, 2008 10:18 AM 

Here is my response:

Hello Louis,
>I think you missed the point of the comment 
Correct, but my comment is still very relevant. The whole thrust of your comment was right on! 
And proves my point of the existence in your head of a relational programmer trying to pop out from sql☺
Now the idea of  'DECLARE @value  TYPE_OF(fred.value)' is really just a special case of the idea
of ' DECLARE @fred  TYPE_OF(fred) = (select * from fred)'. But the idea behind the ability to
have these type of constructs rests on the truth of this:
>We already have the concept of a table variable in 2005. 
But do we really have such a concept? Given two t-sql 'table variables':

declare @Fred table(value char(1))
insert @fred values ('A')
declare @Ethel table(value char(1))
insert @Ethel values ('C')

Now neither assignment nor comparison is possible:

set @Fred=@Ethel
if @Fred=@Ethel print 'Yes'

And the error message in both cases:
--Msg 137, Level 15, State 2, Line 11
--Must declare the scalar variable "@Fred".

underscores the idea that in the context of assignment and comparison only scalar values are
recognized. So now you could live with the idea that a table variable is a different beast 
from an integer or string which both support assignment and comparison. Two different kinds
of variables? But that's kind of crazy ☺ Now the only way for a compiler to perform assignment 
and do comparisons is to recognize the 'type' of variables involved. A variable can only be a 
variable of a specific type. And therein lies the answer. In declare @x int, we know int is the type.
In 
declare @fred table(value char(1))
the assumption is 'table (value char(1))' is the type just like integer is the type. Wrong! 
This is where we've been had, a big gotcha! ☺ It is not a type, if it was we could do 
assignments and comparisons. And even better @fred isn't even a variable! How could a variable
exist without a type? It may be some sort of reference/pointer but it's not a variable in any 
way recognized in cs. There is no concept of a table variable in t-sql and no such concept of 
table type in sql. So what you rightfully wish for 'DECLARE @value  TYPE_OF(fred.value)', 
'DECLARE @fred  TYPE_OF(fred) = (select * from fred)' makes no sense if there's is no type/variable
for a table. This is why there's no 'TYPE_OF' of in t-sql/sql. Type of what? ☺ What your after
are relational ideas like:

var Fred:typeof(Orders {OrderID,CustomerID,EmployeeID}):=
         Orders where ShipVia=3 {OrderID,CustomerID,EmployeeID}; 

which defines a variable of type table with a specific heading (columns/datatypes) and populates
it with rows of data (of the same type). The idea of setting a variable based on a column in a
table is based on recognizing a table type:

var Freds:typeof(Orders[].OrderID):=2;

Variable Fred is set to the type of scalar of OrderID, integer, and then set to a value. 
Welcome to my world ☺ I hope this all makes sense to you! 
  
best,
steve