Hmm... Two problems here

a) Performed has a typo

b) "hack" is a really awful parameter name, possibly it should be 'overridequerycheck' which even it did escape into production wouldn't be too embarrassing.

I hate it when something like this escapes into the outside world and a parameter name of "hack" is already digging the tunnel under the wall and begging to be an embarrassment in the first client demo.

Very cool. Can't wait to see what you will come up with. You do record videos on this, right?

Thank you very much.

Interesting post oren.

on a side note, I would like to mention my biggest problem with Rob's example: Just like so many other developers he makes the all-too-common mistake of misusing stored procedures.

Stored procedures are for side effects and not for read-only operations. UDFs are for parameter-based select statements. This has been around since SQL 2000, and its the same in 2005.

Why are UDFs better for read-only? Think of UDFs as views with parameters. They have strongly typed values while stored procedures expose no explicit output contract.

I can say 'select * from <UDF>' but I cannot say 'select * from <storedprocedure>'

Schema binding aside, this is important when you want to reuse the return values from your function in other database code. Its also better because a UDF has similar syntax to a view.

Cheers

-p

Well, UDFs can be defined to return a single value, or a table.

If the UDF returns a table, then you can handle multiple result sets just like you would a table, with a slight syntax change.

for example

select * from UDF1(@val);

select * from UDF2(@val);

go

or you can join them

select * from UDF1(@val) <alias1> inner join UDF2(@val) <alias2> on <some common column between the tables>

go

so UDF1 and UDF2 can be treated like tables provided UDF1 and UDF2 are defined to return tables in their signature. The alias names are frequently used with UDFs, as you can imagine.

I'm not sure if you knew this already, but due to the nature of UDFs, they are read-only, while anything can happen inside of a sproc. This is better because there is no way to enforce a sproc to be read-only (triggers anye? ;) )

@Ayende

I just about spit my coffee when I read that image...haha

UDF's (User Defined Functions) aren't really meant for direct data access. They are useful in simplifying complex joins or sub queries. Also, useful for joins or subqueries that are used in multiple data access queries. (http://www.15seconds.com/issue/000817.htm)

It could see using UDF's in a direct sql call or in a stored proc, but calling a UDF directly doesn't seem right not to mention asking for performance problems. Just use the SQL statement from the UDF or call a sproc.

Here's a big performance warning! It is pretty common to return table variables from UDF's, but avoid using that for high volume reads. I recently ran into major locking/blocking in the Master DB because of a UDF that was getting called very often.

Oh and, personal opinion, triggers are evil and rarely necessary.

LOL I literally am wiping the Dr. Pepper off my laptop! That's hysterical! I can see smoke coming out of your server now... "help me I'm melting...."

@pete: Perhaps, while i'm in Redmond, I can put you in touch with the SQL team so you can tell them how SPs are supposed to be used :p.

Your example of using a Function is nice, but UDFs are for internal ops, not for direct access and using them as such is poor design for any application. Gotta say I'm surprised that you would 1) suggest such a thing (and moreover complain about not being able to say "select from sp" - that's silly) and 2) not back anything up with numbers or citations.

OK I'm surprised that I'm the only one using them in this manner, but I'll be happy to share with you where I learned this trick!

I dont have the exact quote handy, but Rob Vieira's professional SQL books recommends UDFs for fast read-only operations, custom aggregations and parameterized views, this was when I started to use it, and I was pleased by the results.

@josh:

UDFs are like sprocs in the sense that their query plans are compiled and stored for reuse, so they have the same speed benefits as sprocs, and I dont see a reason why they should be called any less directly than sprocs. But I do agree with you on triggers, they are only useful for audit/logging purposes IMO.

@rob:

I would refer to this link as a backup:

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/stored-procedures-are-not-parameterized-views.aspx

so maybe you could enlighten me on the sql team's opinion of my "foreign ways". Seriously, I would be excited to hear what they say!

thanks for reading

-p

Hi, I am also very much interested in what the sql team would have to say as to what would be a best practice to handle parameterized read only queries (SPs or Table funcs?). Rob if you could give us some feedback on this that would be great :)

I'm disappointed that Rob didn't write us back :(

in the meantime, I'll keep writing UDFs.

Hi,

Just thought i'd let you know I am using this technique to great effect.

In addition, I added an appSetting key that lets you enter that start of a query eg. "select foo0", and in the appender I check if the query I am logging starts with this string, and if so, break into the debugger.

This lets you quickly find where are particular query is being executed in your codebase.

Cheers,

Adam.