Hey Val, a couple of bones to pick :-)

>> 1.)Stored procs are more portable. If I create a middle tier to talk with SQL server, I can use the same logic regardless of front end. If I use RVs I have to rewrite the app for a different interface.

That's true in some circumstances, not in others. The reuse benefit depends on how common the front-end apps are. If we have "N" front end applications that mostly have different motives on the database, then we have app-specific code for "N" apps in stored procedures, and not otherwise encapsulated with the clients or otherwise in separate abstractions. All this to say that this isn't a silver bullet -- you can never completely insulate layers from changes in other layers.

On a related subject (maybe this deserves another thread), I'm theoretically troubled by the notion that with SPs you need to know about the needs and implementations of clients. That's in violation of a generally good rule that services should not need to know about their clients (a fundamental principle of layered architectures). This principle is, in practice, violated all the time, but rarely to the extent that SPs must in order to completely serve the client applications. I do know this: any claim that SPs reduce coupling with client layers is obviously bogus. So in summary, is there a better architecture than blind use of vanilla SPs that eliminate the coupling between the database layer and its clients?


>> 2)... If I’m using stored procs, then I’m looking for everything in the same place. Like triggers for example. They’re on the server. They can’t be in VFP since VFP is the interface. Additional data manipulation is simplified by calling other stored procs on the server.

Actually, the SP's are the interface, VFP is the client. The code is not in one place, it's in two places. The client access code is in VFP, the implementation code is on the data server, and both these codebases are potentially dynamic. I can see few topologies with 1-to-n applications accessing 1-to-n databases where "the code is in one place" is true unless you don't use VFP or any other front end at all.

>> 4.) I think stored procedures are easier to debug and develop. I see what’s going on and can make other adjustments accordingly.

That's not always going to be true. Of course more elaborate SP's are easier to develop in the database <s> but the 99% use case, creating a SQL statement, is as easily done in either layer or in the query analyzer and thereafter pasted in one place or another.

>> 5. It is my opinion that each table should have a primary key and clustered index in SQL Server. The maintenance issues for indexes are minimal and SQL Server is built to handle this. Table scans even use clustered indexes.

I'm not sure if you saw my posts with Rod. Our situation is clearly not benefiting from a clustered index on PK or anything else. I can imagine other situations in science and engineering where a clustered might be ill advised. Simulations with transactional tables with N-dimensional data, or multivariate timeseries data come to mind, but I can't be sure because I don't play there much anymore.

Generalities are generally true. Anything more and I'm just naturally skeptical <s>.

Also, as if all this weren't enough to think about, I'm not impressed with our collective ability on this thread to distinguish the needs between development and deployment. Not all applications are meant to be deployed to non-IT users or to serve non-IT or non IT-system purposes, and in my experience this changes everything.


**--** Steve


>Hi John,
>
>Interesting thread. Here's my two cents:
>
>1.) Stored procs are more portable. If I create a middle tier to talk with SQL server, I can use the same logic regardless of front end. If I use RVs I have to rewrite the app for a different interface.
>2.) If I’m using stored procs, then I’m looking for everything in the same place. Like triggers for example. They’re on the server. They can’t be in VFP since VFP is the interface. Additional data manipulation is simplified by calling other stored procs on the server.
>3.) Large apps will bog down with RVs and SPT for that matter due to recompile issues on SQL Server. There are memory issues when there are lots of recompiles going on. This applies to large apps with lots of users so small apps likely won’t see this. In this event it’s important to look at what’s getting recompiled when. Profiler comes in handy here.
>4.) I think stored procedures are easier to debug and develop. I see what’s going on and can make other adjustments accordingly.
>5.) There was a comment earlier in this thread that stated not every table requires a clustered index, particularly high transaction tables. It is my opinion that each table should have a primary key and clustered index in SQL Server. The maintenance issues for indexes are minimal and SQL Server is built to handle this. Table scans even use clustered indexes.
>
>
>- Val Matison