"Bart op de grote markt" <warnezb (AT) googlemail (DOT) com> wrote

You're "wrong".

You can't really separate the two. That's like saying that wheels on a car
have to do with the design, not with the actual driving.

If you design your database properly, your queries follow from that.


Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?




--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

This is nice in theory, but in practice I have seen many occasions
where reporting requirements simply don't align with the database
(which you often have no control over and may have been designed for
an input system for example). Short of designing a new database and
ETL'ing your data across (which there certainly is a market for but in
a lot of cases would be overkill to meet a single requirement),
sometimes you have to write "non-standard" queries.

Why would a stored procedure not qualify as a middle layer? It
provides a convenient interface between the front-end and the database
and still allows the use of this type of query which, in my opinion,
is neat and easy to implement in SQL. Does it matter if your entire
data structure underneath the stored proc changes as long as the proc
continues to serve up the same results?

J

On 26 nov, 14:09, "Greg D. Moore \(Strider\)"
<mooregr_deletet... (AT) greenms (DOT) com> wrote:
I have not said that database design has nothing to do with querying a
database... But a query of a database is combining the available data
to hava a certain result. Putting the normal forms into your database
is a way to avoid data loss in your database when you update or delete
your data. If I query a database for a report, then the result won't
interfere with the database itself, it just gives a view on your data.
I don't want to be offensive or so, but I'm not convinced yet.

And ok, I did not design the database... it is a database from a new
application my company bought. (In fact it's about two databases from
two different applications that have to be linked in a report, but I
won't go too far to explain that :-) )

As has been said by J above, the Stored Procedure acts as middle layer
between the database and the reports. If there is an update of the
database (e.g. new product version), I will adapt the stored
procedure, so that the user doesn't even notice that anything has
changed.


Kind regards and thx for all your comments

Bart

Actually Greg - You're "wrong".

SQL Server is a data engine and not just a relational data storage method.

There are lots and lots of extensions and features in SQL Server to help us
gain more performance, more simplicity instead of having to code stuff in
the middle tier all the time.

For instance, if I was writing a data export why on earth would I want to
use a second programming langauge that adds complexity when I can easily use
the functions and features in T-SQL.

There is a move more to putting business logic in the data engine rather
than just using the data engine as a put and get object - see research by
Jim Gray.

It would be a bigger change if you had done it in the middle tier - both the
data access queries would change AND the middle tier source code. That's a
lot more testing, development - it's higher risk, more complicated etc...

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]