Basic Anatomy of Sql Server

A series of articles examining some basic concepts in Sql Server.

Basic Anatomy of Sql Server, part I
What is a stored procedure?
http://beyondsql.blogspot.com/2007/11/basic-anatomy-of-sql-s erver-part-i.html

Basic Anatomy of Sql Server, part II
The unit test as part of the database.
http://beyondsql.blogspot.com/2007/11/basic-anatomy-of-sql-s erver-part-ii.html

Basic Anatomy of Sql Server, part III
What does deferred name resolution really mean?
http://beyondsql.blogspot.com/2007/11/basic-anatomy-of-sql-s erver-part-iii.html

Re: Basic Anatomy of Sql Server

steve wrote:

> A series of articles examining some basic concepts in Sql Server.
>
> Basic Anatomy of Sql Server, part I
> What is a stored procedure?
> http://beyondsql.blogspot.com/2007/11/basic-anatomy-of-sql-s erver-part-i.html
>
> Basic Anatomy of Sql Server, part II
> The unit test as part of the database.
> http://beyondsql.blogspot.com/2007/11/basic-anatomy-of-sql-s erver-part-ii.html
>
> Basic Anatomy of Sql Server, part III
> What does deferred name resolution really mean?
> http://beyondsql.blogspot.com/2007/11/basic-anatomy-of-sql-s erver-part-iii.html

Regarding this specific idea, you could work around this issue by
performing a unit test one level up (writing a bit of stub code if
needed). Yes, it'd be nice to have more support for it in the DB
layer, but that alone doesn't justify the (apparent) huge migration
cost for the large number of existing systems out there. (I asked
you about this before, got no response.)

Regarding your general project, I was going to ask about LINQ (which
will presumably have the huge advantage of ubiquity due to being an
Official Microsoft Thingy), but I see you've already dismissed it at
http://beyondsql.blogspot.com/2007_08_01_archive.html

Re: Basic Anatomy of Sql Server

Hello Ed,

Your participation is appreciated.

There is a general misconception about what I'm publishing.
I'm not playing the mousetrap game. As in here what your doing
is silly use this. All I'm doing is trying to put a proof of concept
out in the hopes that it will strike a cord with developers and even
more importantly strike a cord in a major vendor to pursue these
ideas. After all the ideas in net trumped any notion of a migration
hardship.
All I can do is give you different slices of these ideas. There
is no one reason for doing anything, but a cumulative batch of
evidence is another story.
To most of the sql community all that 'theory' stuff is just
for classroom exercise. I'm trying to show what that 'theory'
stuff actually looks like. The rush to code in sql has always
outpaced the thought of what's really behind it. I'm trying
for a little more balance:)
As for an access layer like linq what is the real point? No
matter what linq is or what it can do where does it end up?
It ends up in sql server. And that is the problem:) Rather
than spend the effort to placate net users who want as little
to do with sql and sql coding as possible why not bring a database
itself in line with the net environment. If you do that your talking
about a new type of database. Your talking a relational db
and that's what my stuff is trying to describe.

Regards,
steve

Re: Basic Anatomy of Sql Server

steve wrote:

> There is a general misconception about what I'm publishing.
> I'm not playing the mousetrap game. As in here what your doing
> is silly use this. All I'm doing is trying to put a proof of concept
> out in the hopes that it will strike a cord with developers and even
> more importantly strike a cord in a major vendor to pursue these
> ideas. After all the ideas in net trumped any notion of a migration
> hardship.

Then you might want to write examples in a pseudo-syntax that
/looks/ like SQL. I know this is a matter of taste, but your
examples look ugly to me. Consider:

-- Your example of a stored procedure that returns a result set, the
-- format of which can only be deduced by reading through the code.

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

-- Your example of the same stored procedure rewritten in D4.

create operator GroupByShipCountry (Employee:Integer):

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

-- My example of the same stored procedure rewritten in a
-- pseudo-extension of T-SQL.

CREATE PROCEDURE dbo.GroupByShipCountry
@Employee Integer,
@ResultSet Table (
ShipCountry varchar(15),
Cnt int,
MinFrt money,
MaxFrt money
) output
AS
SELECT ShipCountry,
Count(*) Cnt,
Min(Freight) MinFrt,
Max(Freight) MaxFrt
INTO @ResultSet
FROM Orders
WHERE EmployeeID=@Employee
GROUP BY ShipCountry