Using functions to encapsulate functionality

Question

Using functions to encapsulate functionality

Cbo Spikes

Valued Member

Points: 58
More actions
July 27, 2009 at 9:28 am

#208683

Members,
I'm starting a new project am considering different approaches to improve my coding.
I'm also a ASP.NET dev so I tend to want encapsulate/modularize to a fault.
This maybe a simple question but undoubtedly I will get some good perspective beyond its simple answer.
I was considering taking some table update and insert statements and storing them inside of functions.
If it is a batch, that I would run either by itself or in sequence with other batches, then I would sequence them by calling the functions from procedures.
Theoretically, this would reduce rewriting code but I would hate to get down the path and realize the repercussions were to great to continue.
Beyond the basic, are there issues you see using this approach?
Will I lose functionality like Transactions for the sequence of functions being called from a procedure?
Anything as far as security or performance I should be aware of?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

First of all User-Defined functions are read-only constructs, they CANNOT modify data. With that said you CAN use stored procedures to encapsulate insert/update logic. It could be you simply were not using the right term.

Clear?

CEWII

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

If you mean that you intend to use functions instead of referring to the tables or views, excercise a great deal of caution here. An in-line table valued function can work very well, but probably won't add they kind of functionality you're hoping for. Proper use of stored procedures will do as much for you as in-line UDF's will. A multi-statement table valued function has a number of very serious performance repercussions that could destroy an application. Trust me, I've seen it done.

The key issue is that the multi-statement table valued UDF doesn't have statistics. This means it's treated as a one row table by the optimzer. As long as it's actually only moving a single row, everything is good. As soon as you use it, especially in joins or where clauses, to move large amounts of data (and by large, I mean more than 10's of rows), you run into enormous performance bottlenecks caused by the bad execution plans predicated on the single row estimate.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Cbo Spikes Valued Member Points: 58 More actions · Answer 3

Thanks for the responses.

In the 3 years I have been using MSSQL I have never attempted to modify data in a table with a function and I've just learned that I can't. Feel a little silly for asking but thanks for the info.

I'll stick with procedures!

Also, I have used table valued functions a bit and will heed your advice on the number of rows I would be expecting to return and how I will be using the results.

After your responses I have rethought my question and would pose it a different way.

I seek to be as structured as possible in the coding of all aspects of applications I'm writing.

In my web apps I use folder structure, classes, projects, etc. to organize my work.

I'm having trouble creating this clear structure in my databases. Seems the only tool I have is my naming convention of objects.

Is this the extent of options available? If so, is there some Holy Grail of naming that will keep me from regretting using a particular convention after I've created half my application using it?

How do you all keep everything organized?

Thanks in advance for your time.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

I have yet to see someone eliminate code duplication within a database. It's just too easy to start doing... whatever. Generally a good naming convention will tend to inform the next person along what a procedure does so that they can reuse it. We've also relied on some code generation to ensure uniformity of approach. But... stuff still creeps in. I still find the guts of a procedure copied and pasted into a new procedure for no good reason. Code reviews, deployment reviews... same way you keep people from creating bad client code.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning