Basic Anatomy of Sql Server, part II

This is the second in a series of articles examining some basic
concepts in Sql Server.

Basic Anatomy of Sql Server, part I - What is a stored procedure?

The unit test as part of the database.

If Sql Server is operating with the genie in the bottle what would a system
look like with the genie out of the bottle. Here we take the sql procedure
dbo.GroupByShipCountry from part I and use it in a system that takes the
definition beyond the statements that define it

What does such a system look like and what exactly can we expect such a
system to be aware off. Here we use a system called D4, a system with a
kinship to sql but fundamentally different.

For reference we repeat the sql procedure dbo.GroupByShipCountry from part I:

CREATE PROCEDURE dbo.GroupByShipCountry 
       @Employee Integer
AS
SELECT ShipCountry,Count(*) Cnt,Min(Freight) MinFrt,Max(Freight) MaxFrt
FROM Orders
WHERE EmployeeID=@Employee
GROUP BY ShipCountry

Here is the sql procedure as it would be defined in D4:

create operator GroupByShipCountry (Employee:Integer):
           table{ShipCountry:String,Cnt:Integer,MinFrt:Money,MaxFrt:Money}  
begin
result:=
      Orders 
       where EmployeeID=Employee
        group by {ShipCountry}
          add{Count() Cnt,Min(Freight) MinFrt,Max(Freight) MaxFrt} ;
end;

While the differences in syntax between the two languages are beyond the
scope of this article it should be obvious that the definition of result in 
D4 is similar to the SELECT statement in the sql procedure. Semantically they
are identical. The sql procedure and D4 operator each have a parameter to 
define an employee. The obvious difference between the two is the construct:

table{ShipCountry:String,Cnt:Integer,MinFrt:Money,MaxFrt:Money}  

in the beginning of the D4 operator. This says the result of the operator
is a table with the specific type of:

ShipCountry:String,Cnt:Integer,MinFrt:Money,MaxFrt:Money

Lets execute the operator with syntax as close as possible to the sql sp:

Execute('GroupByShipCountry(8)');   

What is returned? Absolutely nothing. A D4 operator can also be specified
with just its name and input parameter(s) values. So lets try:

GroupByShipCountry(8);

What we get with this is the following system message:

Possibly incorrect use of expression as a statement.

What is going on here? Why doesn't the operator display something like the
sql procedure? Lets go back to the sql procedure. At run-time the procedure
is still only a bunch of t-sql statements so 'execute' is appropriate as 
in 'execute these statements'. But if the D4 operator is a table at run-time
it makes no sense to 'execute' a table! So lets try to use the 'table' in
its appropriate context. In sql we would simply say: 

SELECT * FROM <table>

Which corresponds to the D4 statement:

select <table>;

We now substite the D4 operator with an input parameter for the '<table>':

select GroupByShipCountry(8);

And now we get a similar display as the sql procedure:

ShipCountry Cnt MinFrt MaxFrt  
----------- --- ------ ------- 
Argentina   3   $0.33  $217.86 
Austria     5   $25.22 $353.07 
Brazil      9   $5.32  $110.87 
.
Venezuela   9   $4.41  $141.06 

Clearly the D4 system is aware of two distinct things. The definition of
the operator, its statements, and then at run-time a table. And now it
should also be clear that sql is only aware of statements.

How does D4 guarantee that the GroupByShipCountry(8) table will be
of the type:

table{ShipCountry:String,Cnt:Integer,MinFrt:Money,MaxFrt:Money} 

Because that is the only type of table possible. If the object (table)
exists at run-time it can only be of this type. And if the table is
derived it must have come from compatible datatypes. Another way of
saying this is that a system can distinguish between tables or it can
support implicit conversion. If the system can be aware of a table type
it cannot support implicit conversion. Only explicit conversion, an
intervention on the part of the user, can be consistent with a table type.
Sql supports implicit conversion and therefore cannot tell one table
from another. Sql therefore cannot guarantee the integrity of a table. 
It is object (table) types that offers integrity. For sql to support
implicit conversion means it has no concept of a table of a specific
type as an object. For example, this sql illustrates conversion, from
a money type to an integer type:

CREATE TABLE TestConv
                    (ShipCountry varchar(15),Cnt int,MinFrt int,MaxFrt int,
                     PRIMARY KEY (ShipCountry))
GO
INSERT TestConv (ShipCountry,Cnt,MinFrt,MaxFrt)
EXEC GroupByShipCountry 8 

It also undermines the integrity of each table and therefore the database.
This is what would happen in a D4 operator if a table with incompatible
datatypes with the source was defined:

create operator GroupByShipCountryI (Employee:Integer):
          table{ShipCountry:String,Cnt:Integer,MinFrt:Integer,MaxFrt:Integer}  
begin
result:=
      Orders 
       where EmployeeID=Employee
        group by {ShipCountry}
          add{Count() Cnt,Min(Freight) MinFrt,Max(Freight) MaxFrt} ;
end;

The error returned is a compile error (as opposed to run-time):

Cannot convert a value of type "System.Money" to a value of type "System.Integer".

Not only does the database offer integrity through type compatibility but
the entire framework is exposed to the developer for use as part of the
application itself. A value can be checked for its type with an operator
that accepts any type (a generic). We can make a comparison of the value to
a specific type which returns a boolean value of True/False.

create operator WhatIsIt(aValue:generic):String
begin
result:=
  if aValue is String then
   'String'
     else 
      if aValue is Integer then
       'Integer'
        else 
         if aValue is Money then
            'money'
           else
             'Some other type';
end;          

select 
 WhatIsIt((GroupByShipCountry(8) adorn{key{ShipCountry}})['Canada'].MaxFrt); 

money

We have created the GroupByShipCountry(8) table with a key on ShipCountry
and directly accessed the row where ShipCountry is 'Canada' and extracted
the value of MaxFrt to use as the argument to operator WhatIsIt. The type
of that value is money. And we can extend the same type recognition logic
to a table.

var TryTable:Boolean:=false;
if WhatIsIt(GroupByShipCountry(8))='Some other type' then
  TryTable:=GroupByShipCountry(8) is generic table;
select TryTable; 

True

We have used the GroupByShipCountry(8) table as the argument to the WhatIsIt
procedure. The procedure returned the string 'Some other type' and
in the if statement we asked if the GroupByShipCountry(8) table is a
generic table. In other words we are testing if GroupByShipCountry(8) 
represents some specific type of table. And we can go from a generic type
of table to a specific type of table:

var TryTable:Boolean:=false;
if WhatIsIt(GroupByShipCountry(8))='Some other type' then
 if (GroupByShipCountry(8) is generic table) then
   TryTable:=
      (table of typeof(GroupByShipCountry(8)){})
       is
        table{ShipCountry:String,Cnt:Integer,MinFrt:Money,MaxFrt:Money};
select TableDee add{TryTable Is_GroupByShipCountry8_this_table_type};   

Is_GroupByShipCountry8_this_table_type 
-------------------------------------- 
True        

After determining that GroupByShipCountry(8) is some type of table we have
asked the question, is the type of the GroupByShipCountry(8) table the
specific type represented by the pairs of columns and their datatypes of:

ShipCountry:String, Cnt:Integer, MinFrt:Money, MaxFrt:Money

The statement: table of typeof(GroupByShipCountry(8)){}
extracts just the type information from GroupByShipCountry(8) as information
independent from the values in the rows of the table.

var TryTable:Boolean:=false;
if WhatIsIt(Orders)='Some other type' then
 if (Orders is generic table) then
   TryTable:=
      (table of typeof(Orders){})
       is
       table{ShipCountry:String,Cnt:Integer,MinFrt:Money,MaxFrt:Money};
select TableDee add{TryTable Is_GroupByShipCountry8_this_table_type};    

Is_GroupByShipCountry8_this_table_type 
-------------------------------------- 
False                                  

We have confirmed that the Orders table is not the same particular type of
table as GroupByShipCountry(8). 

And finally we show the ability to make a functional test rests on the
ability to make a unit test:

var C:Boolean:=True;          
if
   (
     table of typeof(GroupByShipCountry(1)){}
      is
       table{ShipCountry:String,Cnt:Integer,MinFrt:Money,MaxFrt:Money}
    ) 
     and
   (
     table of typeof(GroupByShipCountry(8)){}
      is
       table{ShipCountry:String,Cnt:Integer,MinFrt:Money,MaxFrt:Money}
    )   
      then 
       if GroupByShipCountry(1)<>GroupByShipCountry(8) then
         C:=False;
          select TableDee add{C Are_Tables_of_Same_Type_the_Same};
    
Are_Tables_of_Same_Type_the_Same 
-------------------------------- 
False                     

We have first verified that the two tables are the same type of table and then
asked if the two tables are the same. In other words, we can only test the
equality of two tables (they have the same set of rows) if they are the same
table type just as we can only compare two numbers or two dates. It is
obviously nonsensical and illogical to compare two objects of different types.
(I note in passing that the fact that an sql database is aware of individual
 columns and their datatypes is fundamentally different from being aware that 
 such pairs can be encapsulated to define a type of table. It is the notion of 
 encapsulation that defines a type (table) independent of any column(s) as a 
 scalar type that sql is aware of. Any place in t-sql that a table definition
 appears ie. CREATE TABLE statement, a table valued function or a table variable
 (a confusing and misleading construct) whatever this definition is the important
  point is this definition does not signify a table type).

Can we make a test of equality between the sql procedure dbo.GroupByShipCountry
and the D4 operator GroupByShipCountry? As just previously shown we can easily
make such a functional test if both are recognized as table objects.
We can elevate the mere t-sql statements of dbo.GroupByShipCountry by bringing
in the statements to D4 with a pass-thru query. The following D4 query displays
the same result as executing the procedure in Sql Server.

select SQLQuery('Execute dbo.GroupByShipCountry 1');

ShipCountry Cnt MinFrt  MaxFrt   
----------- --- ------- -------- 
Argentina   1   63.7700 63.7700  
Austria     5   74.6000 351.5300 
Belgium     1   29.5900 29.5900  
.
Venezuela   8   0.9300  148.6100 

Is the display a 'result set', as in Sql Server, or a table in D4? Let us
apply a unit test to confirm or reject whether it is a table in D4:

select 
 table of typeof(SQLQuery('Execute dbo.GroupByShipCountry 1')){} is generic table;    
 
True

which confirms that D4 is aware of the dbo.GroupByShipCountry t-sql result 
set as an object of some type of table at run-time. We can now try to test
the equality of the two tables assuming they are of the same specific type. 
The following query should return a boolean, either True or False, on the
equality (sameness) of the tables:

select GroupByShipCountry(1) = SQLQuery('Execute dbo.GroupByShipCountry 1') ;

But on checking the query at 'compile', two errors are returned:

Cannot convert a value of type "System.Decimal" to a value of type "System.Money".
No signature for operator "iCompare" matches the call signature
"(System.Money, System.Decimal)".

Both errors indicate a type mismatch. It is clear we cannot test for equality
because the two tables are of different types. We know the specific type of
table for the D4 operator. Let us go back to the unit test to find out what
type of table is the sql procedure:

var TypeofTable:=table of typeof(SQLQuery('Execute dbo.GroupByShipCountry 1')){}; 
var TypeString:String;
if TypeofTable 
 is table{ShipCountry:String,Cnt:Integer,MinFrt:Decimal,MaxFrt:Decimal} then
  TypeString:='table{ShipCountry:String,Cnt:Integer,MinFrt:Decimal,MaxFrt:Decimal}'
 else
  if TypeofTable 
   is table{ShipCountry:String,Cnt:Integer,MinFrt:Money,MaxFrt:Money} then
    TypeString:='table{ShipCountry:String,Cnt:Integer,MinFrt:Money,MaxFrt:Money}'
    else
     TypeString:='A type where MinFrt and MaxFrt are differnt than Decimal and Money';
select TypeString;     

table{ShipCountry:String,Cnt:Integer,MinFrt:Decimal,MaxFrt:Decimal}  

Now we understand what's happening. The money datatype in Sql Server is
really a decimal type significant to 4 digits. The money type in D4 is
a totally different type. When D4 interacts with a server table it reads
money for datatype. When the sql procedure is brought into D4 as a table
the MinFrt and MinFrt are interpreted as decimal (and this also accounts
for the different displays of the server procedure and the D4 operator).
To compare the two tables we have to make an explicit conversion. We can
convert the server decimal types to D4 money types.

select 
        GroupByShipCountry(1) 
        = 
        (SQLQuery('Execute dbo.GroupByShipCountry 1')
           {ShipCountry,Cnt,ToMoney(MinFrt) MinFrt,ToMoney(MaxFrt) MaxFrt});

True

Clearly, the idea of a unit test in D4 can be used by a developer to provide a 
programmable form of integrity within an application. 

A database that recognizes, supports and exposes a unit test for a table
type is an objects database. An objects database that has an algebra for
tables is a relational database. D4 is a relational database.

Basic Anatomy of Sql Server, part I 
What is a stored procedure?

Basic Anatomy of Sql Server, part II 
The unit test as part of the database.

Basic Anatomy of Sql Server, part III 
What does deferred name resolution really mean?

Some related articles of interest:
All tables are typed variables 
A table as a parameter
Reusable procedures