I created one to return a simple list of ids, based on some authentication data, which I could then use to return only records that users had access to. I read a couple of articles which said table valued functions were great for performance, especially over scalar valued functions. They cautioned to use them in the joins though and not in the select or where clauses; the articles claimed that the select and where clauses would cause them to execute once for each record.
I followed the advice of the articles, which seemed logical, and we started having all sorts of speed issues with our application.
Through a little trial and error I discovered that my table valued function would often add minutes onto the execution time of a sproc when used in the join area of the sql statement. I tried moving it down to the Where clause using the In keyword and the sprocs began executing in less than a second again.
Maybe I was reading the articles backwards, but I have learned to use table valued functions in my where clause and not in the join part of a sql statement.
On a side note, I have lots of really small sprocs with the function in their join clause, so it is possible that the optimizer only has problems with it when there are at least a hand full of tables it is working with.
