Re: Need help with Tricky SELECT statements please.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 04/21/04 

Date: Wed, 21 Apr 2004 10:27:24 -0500

> . and where it does not belong in a properly tiered architecture. But
> it sure gets the newbies, who fall into the trap, committed to just
> Microsoft forever, don't it?

It might get us hooked :) I was also taught early that it was best to do as
much manipulation using SQL as possible. I completely buy the idea of not
formatting data in the data layer, that is clear, but not shaping the output
seems very wrong to me. I gathered from my early learning (including a
seminar I attended with Dr David Rozenshtein, which really turned me from
average SQL Guy to a real believer in relational programming, including his
book The Essense of SQL) to use SQL to its fullest potential, because we can
take tasks that are tedious in a procedural programming language and turn
them into single SQL statements that replace thousands of lines of
procedural code. The best example was tree processing. Using a breadth
first algorithm, tree manipulations went from this recursive tree walking
with arrays to keep up with intermediate results, a simple query grouping by
level of tree and using sets of data to hold intermediate results.

The real question is can we build a properly tiered architecture that
includes a data manipulation tier that has the power of a relational
language, rather than a procedural one? It would seem to me that any kind
of shaping that we could do using set based manipulations should be done
that way. It is almost painful for me to consider taking a set row by row
and doing any kind of code on it. 

-- 
----------------------------------------------------------------------------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)
"Joe Celko" <jcelko212@earthlink.net> wrote in message
news:uytq671JEHA.2456@TK2MSFTNGP12.phx.gbl...
> >> So are you saying that we shouldn't use SQL to do grouped
> (aggregated) mathematics? <<
>
> Not at mixed levels of aggregation; that would not give us a table since
> a table is a set of things of the same kind.
>
> >> This is going to become more murky as time goes by because the next
> release of SQL Server will include far more powerful functionality to do
> even more of the data-output shaping in the data tier, where it is so
> much easier to code. <<
>
> . and where it does not belong in a properly tiered architecture.  But
> it sure gets the newbies, who fall into the trap, committed to just
> Microsoft forever, don't it?
>
> --CELKO--
>  ===========================
>  Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Three Kinds of Logical Trees
    ... >> That strikes me as a nonstardard definition of the use of metadata, ... I would be surprised if all you really care about is strings. ... Having the tree as the only possible structure is worse than having ... > SQL does not handle well. ...
    (comp.databases.theory)
  • Re: Self-join question
    ... I am not an advanced SQL user and it's hard ... > There are many ways to represent a tree or hierarchy in SQL. ... > called an adjacency list model and it looks like this: ... > Another way of representing trees is to show them as nested sets. ...
    (microsoft.public.sqlserver.programming)
  • Re: Complex SQL problem
    ... CREATE TABLE OrgChart ... SQL is a set oriented language, this is a better model than the usual ... lft and rgt columns is called the adjacency list model, ... To show a tree as nested sets, replace the emps with ovals, then nest ...
    (microsoft.public.access.formscoding)
  • Re: T-SQL create self-reference in depending table
    ... Joe Celko's SQL Puzzles and Answers 5 1-55860-453-7 ... There are many ways to represent a tree or hierarchy in SQL. ... CREATE TABLE OrgChart ... To convert an adjacency list to a nested set model, ...
    (comp.databases.ms-sqlserver)
  • Re: accessing data mining model via web/PDA
    ... there is a decision tree in my sql 2000 server, from this tree, i need to ... Although these samples are for SQL 2005, ... > basic concepts of how to connect and retrieve data from the server are the ... i m a beginner in handling data mining analysis ...
    (microsoft.public.sqlserver.datamining)