sql server (alternate): Greetings to all database professionals and laymen,

Let us make a bold assumption that we have developed a software
tool for the SQL Server environment which simply acts as an interface
between an end-user in an organization and the database, through
the exclusive use of stored procedures which are authored by the
organization or by software developers.

All development work at the application software level may thereby
be conducted within SQL, by the development of TSQL stored
procedures and their coordination across an organization.

The question then needs to be asked what are the advantages of this
arrangement and what are the disadvantages. I would appreciate
your comments here, as it is difficult for folk heavily involved (eg: me)
in something to obtain objective opinion otherwise.


Potentially it is possible to construct an entire database application
software package using only TSQL stored procedures and this
tool. When a database backup is conducted not only the data is
backed up but also all the "program development" suite, in the
form of stored procedures (and their scheduling, for example).

One of the advantages from my perspective is that this arrangement
implies the possibility that all software external to the database may
be made redundant (except this tool), and along with it, all the other
redundancies of managing application development (life-cycles) within
the database and then coordinating these changes with software
external the database (particulary loading it to the client environment)

You see, I believe that (for example) any and every application written
in VB, C, etc, etc, etc (external to rdbms) expressly for database software
(ie: rdbms software such as SQL Server) involves a redundancy of data
definitions required ---- once within the VB and once within the db.


I would appreciate any feedback on any of the above issues, and
wish everyone the compliments of the season.



Pete Brown
Falls Creek
NSW
Oz
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
EDITOR:
BoomerangOutPost: Mountain Man Graphics, Newport Beach, {OZ}
Thematic Threading: Publications of Peace and Of Great Souls
Webulous Coordinates: http://www.mountainman.com.au


QuoteForTheDay:

"Consciousness is never experienced in the plural, only in
the singular. How does the idea of plurality (emphatically
opposed by the Upanishad writers arise at all? .... the
only possible alternative is simply to keep the immediate
experience that consciousness is a singular of which the
plural is unkown; that there *is* only one thing and that
what seems to be a plurality is merely a series of different
aspects of this one thing produced by deception (the Indian
maya) - in much the same way Gaurisankar and Mt Everest turn
out to be the same peak seen from different valleys."

- E. Schrodinger, "What is Life"
--------------------------------------------------------------------

[quoted text, click to view]

Hi,

I already write 80% of my code in stored procedures. I also use
xp_cmdshell inside SPs for FTP and other DOS commands. So I'm all for
the idea of encapsulating all development work inside TSQL stored
procedures. With the coming of Yukon and the ability to embed C# and
VB.net code directly in SPs, I would probably write even more of my
code inside stored procedures. The one area where I think code
belongs on the client, involves data-entry applications. Here I want
to instantaneously respond to user clicks & inputs. I don't want to
make a round-trip to the server every time the user clicks - so data
validation and instant totals need to remain on the client.

Ramblings. I have a lot invested in the DB backend and web client
paradigm. The web services and windows app paradigm is not something
I really want to explore. The MSSQLSERVER2000 reporting services
looks promising, but I'm somewhat wary as my users are happy and
comfortable going to our current web sites. If the reporting services
gives me the capability to develop something that looks like a web
site, with logins, bread crumbs, hyperlinks, etc then I'll pursue it.
The coming of Asp.net 2.0 is also promising. The new sqldatasource
and gridview tags will save me a lot of coding and allow me to
concentrate on business logic, instead of how to make ado.net work
with asp.net. I guess my major wish is for M$ to evolve asp.net into
a meta language for all web client functionality. If they do that,
I'll gladly brain dump html, style sheets, javascript ... and

mountain man (hobbit@southern_seaweed.com.op) writes:
[quoted text, click to view]

Might be that the stored procedures are backed up too, but stored
procedures in a database is to be regarded as binaries. The source
should be elsewhere, in a version-control system. (Which may be implemented
on the top of an SQL database.)

[quoted text, click to view]

And maybe once in the business layer.

And this is not without good reason. If you were a military commander
who were set to defend something important, would you settle on one
single defense line?

Take an example: order registration. The database needs to know that
customer ID must be registred, and this is probably enforced with
a NOT NULL constraint. But the user-interface also needs to know this,
so that it give the user a comprehensible error message. It is not
sufficient to slam "Attempt to insert NULL in column...".

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

[quoted text, click to view]


Of course.


[quoted text, click to view]


Absolutely, and then again possibly in an executive information
system of some format spanning everything above. You get
the drift.



[quoted text, click to view]


Really, it would entirely depend upon the nature of the battle and
what resources the commander had at his or her disposal.

For example, when faced with a superior foe, one's best chance
of survival might necessitate putting all the eggs into the one basket
and retreating to an emminently defensible location. (Helm's Deep ;-)


I think the reason for the redefinition of the data in multiple layers
mapped onto the database has been the historical development of
the technology --- not because it is any more secure or defensible.



[quoted text, click to view]


Data validation is obviously the weak link in the model I have
put forward for discussion, however the problems associated
with this issue need to be isolated from some of the benefits
to be gained by operating all development within one environment.

The user interface can be re-arranged as a data staging area.
Data validation can be engineered by multi-process steps and
managed as a separate task.

I appreciate the pin-point response.

Compliments of the season,




Pete Brown
Falls Creek
OZ
--------------------------------------------------------------------
BoomerangOutPost: Mountain Man Graphics, Newport Beach, {OZ}
Thematic Threading: Publications of Peace and Of Great Souls
Webulous Coordinates: http://www.mountainman.com.au

QuoteForTheDay:

"A skillful soldier is not violent;
An able fighter does not rage;
A mighty conqueror does not give battle;
A great commander is a humble man.

You may call this pacific virtue;
Or say that it is mastery of men;
Or that it is rising to the measure of God,
Or to the stature of the ancients.

- Lao Tzu (about 300BC) - The Way of Life
http://www.mountainman.com.au/tao_7_9.html
--------------------------------------------------------------------

[quoted text, click to view]

This is often documented as an exceedingly efficient practice
and for good reason due to the efficiency of the RDBMS software
layer dealing directly with the RDBMS software layer rather than
any other external software environment (layer).

One might easily make the hypothesis that there is a migration of
code from the environment external to the database, to the code
resident internal to the database. That is, from an historical and
developmental perspective.

This thread concerns the hypothetical end-point of such a migration
wherein all code relevant to the application is internal --- ie: stored
procedures.



[quoted text, click to view]

I will await the implementation to comment on this aspect.


[quoted text, click to view]


Data entry and its validation processes ---- the "user interface" --- is
indeed the traditional bastion of "client side code" due to its historical
development in that environment.

However, we both know that every single user interface screen of code
connected to an application represents a redundancy of definitions which
have already been categorically defined within SQL.

An application is consistent of a suite of say 999 user-interface programs
which connect in different manners to a great range of the data in an
extensive and comprehensive database.

There are 999 sets of redundant database definitions requiring attentive
and costly coordinated review possibly every new build. This is alot of
redundancy.


My point here is simply pointing out the (known) redundancies existent
in the current technological development associated with rdbms and
asking what would it be like operating under a methodology of zero
redundancies, where all the code is TSQL, with the exception of
some user-interface rdbms portal software which only deals in
allowing the user to effectively select stored procedures to be run,
or returning to the user the data set output of their execution.



[quoted text, click to view]


Without beating around the bush, it is really the huge efficiencies
and rich arrays of controls made available in the modern rdbms
to effectively and automatically manage data that make the modern
database environment a relative haven of well-greased bearings.

Outside of this environment there is great thrashing about of software
development driven a great deal by the marketing teams. Change
is piecemeally managed or extra change management utility software
applications are employed to track change.

The management of change in the IT environment for a few decades
has driven me to seek a theoretical solution to the enormous problems
associated with coordinating change external and internal to the
database.

My conclusion is all this change is being made far more difficult to
manage due to the fact that the code (ie: the quanta of business
intelligence) is in fact being formally defined across two system
software environments. (ie: one in the client (VB) code,
and once again in the database, repeatedly throughout the
entire extent of the code)

If the base of operations can be reduced to one environment
(ie: the database) then what is now known as the client application
environment will fall away from future technology like the booster
rocket in an outbound shuttle rocket.



[quoted text, click to view]


From my perspective, if M$ bundled some form of SQL Server
portal software which enabled all levels of database related ppl
to write applications in stored procedures, then there exists the
outlandish possibility that every single diverse form of application
that can be written for a database could in theory be run on the
one machine using the same user interface to many SQL databases.

It could unify the application software layer by subsuming it in
entirety within the database software layer. This is about the
theory of the benefits of a model and operations with a smaller
number of moving parts.

At any rate, I appreciate your response and was interested
to read your approach to this, and your plans.

Compliments of the season.




Pete Brown
Falls Creek
OZ
--------------------------------------------------------------------
BoomerangOutPost: Mountain Man Graphics, Newport Beach, {OZ}
Thematic Threading: Publications of Peace and Of Great Souls
Webulous Coordinates: http://www.mountainman.com.au

QuoteForTheDay:

"Nothing is rich
But the inexhaustible wealth of nature.
She shows us only surfaces,
But she is a million fathoms deep"

- Ralph Waldo Emerson
--------------------------------------------------------------------

mountain man (hobbit@southern_seaweed.com.op) writes:
[quoted text, click to view]

Writing applications is not about composing beautiful structures with
minimal redudancy, but to create applications that meets business needs,
and a standard business need is that the application provides compre-
hensible error message in case of user errors.

There are just too many programmers out there who fail to understand this.
--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

[quoted text, click to view]

I agree with your second claim, but cannot understand why you
cannot also institute both claims simultaneously. That is, granted
that the business application mandates integrity constraints, and
sometimes at various levels, if these are catered for then one is
bound to concientiously consider redundancies.

The minimisation of redundancies is imo a task relegated to the management
and coordination of the business software development cycles,
perhaps at the level of the IT manager of that business.

It might certainly not be a task for some appointed programmer,
but the existence of these redundancies - you must admit (?)---
represents an expense to the funding business that is directly
proportional to the number of redundancies.




[quoted text, click to view]


My aim is to engineer a tool by which any manner of application software
components can be quickly and easily created by TSQL programmers
using stored procedures alone.

Your earlier points concerning data validation are a key issue when first
examining this methodology I am proposing, and I will address them in
more depth elsewhere.

Thanks,




Pete Brown
Falls Creek
Oz

Hi Loius,

Here is a separate response on two separate issues central
to this thread (thanks for the dialogue) ....


[quoted text, click to view]


My object is to write 100% of code in stored procedures.
If this is achieved, then all client-side code will be obviated
as will its change-management costs --- which are considerable
to any given organisation in todays world.

The headaches associated will change management will not
be removed, however its domain will be moved from where
it is at the moment - in two software layers (rdbms & apps),
to one unified software layer (rdbms). This is simpler.



[quoted text, click to view]


Here I would like to say to you --- what if your SQL Server network
and database were given the sufficient resources whereby the roundtrip
to the server is "quite acceptable"?

Also, is not the database the final authority on data validation and
instant totals? Consequently, how are you going to ultimately avoid
database IO to get updated data?

Thanks again for your thoughts.




Pete Brown
Falls Creek
OZ

[quoted text, click to view]

....[trimmed]...

[quoted text, click to view]


What if the user interface queried the database and provided this
information
from a comprehensible error message table? Or better yet gave the option
to enable workflow screens for immediate customer registration.

Are you talking about saving database IO in reading the vendor table
and an error table?


Thanks for the dialogue.




Pete Brown
Falls Creek
Oz

Hi Pete,

I can only speak from my own experience. I work for a fortune 500
company. For example, to get reimbursed for our travel expenses --
I'm forced to use an intranet site called XMS. We're on a T100
network. But this site does server-side data validation on every
single user input. Depending on how many users are logged on, a
ten-minute task might take an hour. If our network is upgraded to a
T1000, then I can see server side data-validation as being reasonable.

I can see where your're coming from. Software change management is a
major headache. We've got legacy systems that date to the 1970s. We
often get consultants who say they'll just tap into our system with an
ODBC connection and do their magic. We all chuckle at that one. Some
of our operational system are running COBOL. First they have to
import the data into a database and then they'll get an ODBC
connection. Every now and then I still have to program in SAS and IBM
JCL mainframe stuff. He he hee hee. Our company has over 80K

mountain man (hobbit@southern_seaweed.com.op) writes:
[quoted text, click to view]

Configuring such a table is a major task, and not even a wise move. Just
because the database complains about NOT NULL violation, does not mean
that the user forgot to input the information. In stead the problem was
a bug somewhere in the food chain that mutilated a variable.

[quoted text, click to view]

You mean wizards? Bang! Here, I just laid 150 new customers that you
have to register. It should be done by lunch. I promise you, you don't
want a wizard. You want a form that permits you to work fast, and which
informs you if you miss something.

[quoted text, click to view]

I'm talking about user experience.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

mountain man (hobbit@southern_seaweed.com.op) writes:
[quoted text, click to view]

There may be a cost for redudancy. But there is also a cost for bad
error messages. I don't know many times I have cursed poor error
messages, which have caused me to spend hours to understand what
was wrong, only to find that when I eventually tracked down the error
that had there been a clear error message, I would have solved the
problem in minutes.

Yes, sometimes I have been responsible for those poor error messages
myself.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

This theory is not practical for any application with more than a very
few users in a very small network.

On Fri, 02 Jan 2004 11:47:31 GMT, "mountain man"
[quoted text, click to view]