Just to chime in on the UDF issue... I think they are amazingly cool, but should be used in select statements with great caution.

We were working on an application here and the other developer, who comes from a VB/CGI/web-development world, coded a lot of the logic into UDFs, then put the UDFs in the select and where clauses... You have to keep in mind that if the UDF does a select off of a large table to return it's result, then you put it into a select or especially a where clause, it has to execute one time for every row that the statement is going to return. With caching, it might not generate TOO much IO, but if your result set is large and it adds a tiny bit of time to each row, you can drag an application to it's knees waiting for it to return.

In our case, the developer's first attempt required over 10 to 15 minutes or more to return a few hundred rows out of a table with around one million (this was for a web application). I later recoded it without using the UDFs and got the entire thing to run in under three seconds. To be fair to UDFs, that wasn't the only problem the stored procedure had. This is an extreme case, but I think it makes the point.

Matthew Galbraith

ps: I do use UDFs, but usually only in cases where I can't do it any other way, or as a shortcut when I'm writing one-off ad hoc queries; I eschew them almost completely in where clauses though I do sometimes use them as 'smart' views in joins and every once in a while will put a simple one in the select clause.