I just switched jobs joining my third .NET shop.
My last shop was full of Database developers who built almost all
business logic into the databases. Most projects ended up with 100s of
Stored Procedures, Views, function. In some cases cursors were used,
etc.
My new shop is looking to implement a Development standard that says,
if and when possible, do not build code in the database. Instead
code .NET Business/Data classes that spit out collections and have SQL
code in the class.
Any opinions on the difference, where to draw the line and what might
advantages/disadvantages?
Thanks in advance for any help or information.
Take it to the logical extreme - to have the minimum amount of SQL
anywhere, just create one routine that does a "select *" against any
table in the database. Then do all of the joins, filtering,
aggregation, etc in the .Net code. That'll really fly when your
application needs 1 row from 50 million.
If you have a database that needs 100s of stored procedures, write
100s of stored procedures. If you have a database that only really
needs 10, or 50, then write just those. There's no right number.
What you should be doing in any circumstance is using the right tool
for the job. Cursors are rarely the right tool, but that doesn't mean
never. And although T-SQL has some limitations, it also has
considerable strengths. You tell SQL Server what data you want, and it
figures out the optimal way to get that data.
Sometimes the optimal way still isn't fast enough, and that, IMO, is
the point when you get the DBAs involved - "can you change anything to
make this work faster?" - which may lead to them re-writing your
procedure, or to them just changing some indexes on the server, or
introducing partitioning, etc. All of that depends on how big your
shop is and how it's split between devs/DBAs (and how much access devs
have to boxes where they can do the SQL work - i.e. Dev servers)
The advantage of putting code in the database becomes apparent as soon
as another application starts dealing with the database. If the code's
in the database, then you don't have to worry about it doing things
differently when it's making changes. And just because you don't write
a second application, it doesn't mean your customers won't want to.
For instance, where I work, we write a large amount of our software
ourselves. But one of our central systems is from a third party
supplier. Our website can process many types of updates and write them
into this 3rd party database. How I wish there were even foreign keys,
let alone well written stored procs with good validation, to stop *me*
from doing something dumb.
Damien
