Basic Anatomy of Sql Server, part III

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

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

What does deferred name resolution really mean?     

Essentially part II answered the often asked question, "why can't I
SELECT from an sql stored procedure?". Because there is nothing to
select from. There is an absence of the concept of a table type in sql
which is necessary to realize a table. 

Now we ask another simple question. How is it D4 can understand the assignment
of a table and a test of equality between two tables just as t-sql understands
the same things between numbers:

var aNewTable:= GroupByShipCountry(1) ;     //Assign the table for employee 1 based
                                            //on the D4 operator from Part I to 
                                            //aNewTable.
var Test1:= aNewTable=GroupByShipCountry(1);//Are the two tables the same? Yes.
                                            //Now change aNewTable with an update.
update (aNewTable adorn{key{Cnt,MinFrt}}) 
         set {MinFrt:=$0.00}
          where ((Cnt<11) and (MinFrt<$2.50));
var Test2:= aNewTable=GroupByShipCountry(1); //Now are the two tables the same? No.

select row{Test1 TestofEquality_before_Update, Test2 TestofEquality_after_Update};

TestofEquality_before_Update TestofEquality_after_Update 
---------------------------- --------------------------- 
True                         False                       

And how is it that t-sql can do with numbers what D4 does with tables:

DECLARE @X Integer, @Y Integer; 
SET @X=5; SET @Y=10;  
if @X=@Y
  print '@X=@Y'
   else
    print '@X!=@Y'
    
@X!=@Y    

The answer is obvious and simple. We can only assign values and make comparisons
with variables. And we cannot do anything with a variable unless it is typed.
The unit in 'unit test' is the awareness of the database of a table as a variable.
To say the idea of a unit test is part of the database is to recognize that
a table as a variable and its type is the only way to distinguish one table
from another. The unit is the same for distinguishing among numbers and strings
and tables. And it is this basic unit that sql server lacks for tables. 

Sql server bol describes deferred name resolution as:

'This process is called deferred name resolution because table objects referenced
 by the stored procedure need not exist when the stored procedure is created, 
 but only when it is executed.'
 
What is this really a statement of? 

When t-sql parses these statements:

IF @X=5 
  PRINT '@X=5'
   ELSE
    PRINT '@X!=5'

it returns the error: Must declare the scalar variable "@X".

In D4 when this statement is parsed:

select aNonExistentTable;

it returns the same error as t-sql: Unknown identifier "aNonExistentTable".

These errors are the same. Each system is aware that a variable has not been
defined with a type. There is the intent at parse in each system to check
that an identifier (@X, aNonExistentTable) has been appropriately defined. 
And where the identifier is recognized as a variable and nothing else. This
intent has nothing to do with the context that surrounds the identifier. And
it is this intent that lies at the heart of a relational database. A statement
of just the variable results in the same error.

t-sql   D4
-----   -------
@X      aNonExistentTable;

Must declare the scalar variable "@X"., Unknown identifier "aNonExistentTable".

Now these are all examples of so called t-sql deferred name resolution, there
is no error raised on parsing only execution:

SELECT * FROM aNonExistentTable

SELECT aNonExistentTable

IF aNonExistentTable=anotherNonExistentTable
       print 'YES'

CREATE PROCEDURE theTableDoesNotExist
AS
SELECT * FROM aNonExistentTable

Given that there is computer science and not one computer science for t-sql
and one for everything else, how is it that these statements can be parsed
without error? Because at parse-time if there is no concept of an
identitifier as a variable there is only context to check, syntax. Absence
the idea of a variable there is nothing to resolve a database object to, 
hence it is as if these objects/identifiers do not exist at parse-time. 
How bol describes deferred name resolution is a consequence of working with
tables in a variable-less system. It is a price paid, in terms of sacrifice
of integrity and sacrifice in management of table objects, by the developer
for working in a type-less and variable-less and therefore a non-relational
database. Explanations like deferred name resolution are quite common in
sql but they are all just different sides of the same central issue. For
example there is the issue of impedance mismatch. This is just an expression
of the inherent difficulty of communication between a client, which 
understands what a variable is, and an sql server which does not. And finally
there is the idea of relational division. Since comparisons require variables
sql forces an inference to be made about a comparison of table(s) it cannot
do. It forces the use of constructs that are obtuse and complex compared
to simple and direct comparisons that can be made in a relational database
where the table exists as a variable.
(And now you know why you can't pass a table as a parameter. There is no 
table variable to pass ☺ .

The history of sql is a history of a language trying to exist outside the
mainstream of the rest of IT. It's long overdue that application development
use a database that has the same foundation as other programming languages.
And that kind of database is relational.

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