[dba-SQLServer] Article: LINQ and stored procedures

Arthur,

I have always agreed with that perspective (the DB does whatever it can)
however... I have to say that until the DB has modern programming tools to
accomplish these objectives then it simply takes more time that it is worth
to do that.  I understand the "every application that touches the data needs
to know the rules" issue, but in fact millions of very real and useful
databases only ever have a single application that touches the database, and
to be so pedantic is often not useful.  There is a time and a place for that
concept, but it is not every time and place.

There is also another thing to consider.  I know that we really in fact
seldom do so but in order to move an application from one data store to
another REQUIRES that the application logic NOT be in the data store, or
moving will be a non-starter.  There are often valid reasons for being able
to run on different platforms and data stores.  Having all the logic
embedded in SQL Server makes moving to MySQL completely impossible.  If only
the data and basic "they can all do this" kinds of things like constraints
and perhaps triggers actually reside in the data store then having an
application that can work on SQL Server and oracle and MySQL suddenly
becomes at least possible if still not always feasible.

And finally, I have a very interesting book, Expert VB 2005 business objects
by Rockford Lhotka.  Just a bit over my head but interesting none the less.
In that book Rockford designs a "execute where it belongs" method.  Class
objects are actually passed back and forth between machines, filled by the
SQL Server, processed by a business server, changes saved back to the data
store by the SQL Server.  The SQL Server does what it is supposed to do,
filling class instances with data, and storing them back into the tables.
All the logic of doing that including rules about what is legal (in terms of
data validation) are processed by the SQL Server.  Business rules OTOH are
processed by the Business server.  They can be on the same machine, or two
different machines, even on two different continents.

There is a very real case to be made for the fact that while the data may be
common, laws and customs, currencies and time zones and languages can make
it useful to have a program able to operate on a common set of data but
apply different business ruled based on location.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
Fuller
Sent: Saturday, March 29, 2008 9:57 AM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Article: LINQ and stored procedures

IMO we are coming to a crossroad here. I ought to indicate my perspective on
this at once, so that anything I write subsequently can be understood as
coming from said perspective.

Fuller's Sixth Law states "Anything the database can do, the database
shoulddo." This law applies to every db from dBASE to Access to MS SQL to
DB2 to Oracle to Objectivity and so on.

To rephrase it in practical terms, this means that the FE ought to be
stripped of everything the db engine itself can do. This would include FK
constraints, to name just one thing. The FE should never never never touch
tables directly, but instead always go through a query (in Access) or a view
(in MS SQL and others) or a table UDF (in MS SQL). Nobody but God (i.e. me)
should be able to touch the tables directly.

Db-oriented logic encased in the FE is a bad idea for several reasons.
Perhaps the most notable is that said logic would have to be encased in
every FE that touches the db, which is career suicide. On the opposite
extreme, if everything is in the db itself then all the FE has to do is fire
a sproc and pass the appropriate parms. It is not trivial to translate a
call from VB6 or Delphi or .NET or PHP or Python into any other of these
languages, but it is much less work than to rebuild the logic in any new
language. A call to a sproc is basically a one-liner in any language.
Populate the parms, call the sproc, deal with the result set.

Ok, that's my take. Linq is going in an entirely new direction, and to be
fair, I've read the docs (well some of them) and been impressed by some of
the new language techniques, and also been thankful that one can use Linq to
call sprocs. But Linq overall seems to me a method of eliminating the
profession of SQL Developer. It attempts and suggests to move increasing
amounts of business logic upwards, to either the middle tier or the FE, and
in both cases I deem this incorrect. The push ought to be down not up, IMO.
I want the db to be a magic black box and the FE to simply follow some basic
communication rules, and everything will automagically happen. Moving the
logic of result-set-handling up to the MT or the FE is a bad choice, IMO.

All this said, I am still investigating Linq big-time. When I noticed that
one did not have to construct the SQL statements inside it, but could
instead call a sproc or table-udf, I was much relieved and much more
intrigued. So I shall pursue this path but also stick to my previously
stated law until it has been refuted. I will pursue the path of setting up
parms and calling sprocs, and totally ignore the idea of dynamically
constructing SQL statements within Linq.

And finally, I think that any investment in Linq for SQL is a dead end. Linq
for Entities is clearly the future. Linq for SQL is dead in the water,
subsumed completely by Linq for Entities. I can see some future there, for
sure. The idea that anything is encapsulated as a data source has promise.
But I still want the real logic and business in the back end.

A.