ryan.blosser (5/18/2013)
This is what I have been looking for and forgive me if this is a simple question (I am trying to learn some of these concepts, like in-line table-valued functions), but how do you easily apply this to an entire table? How do you use this, even? It returns a table (not permanent), so I selected the result set into a new table, but I don't know if that defeats the point of using the TVF in the first place.This:
select * into dbo.testtable from
(SELECT t1.id as id, t1.title as title, clean.cleaned as cleaned from t1
cross apply dbo.if_cleanwithreplace(t1.title) clean) a
fixes one column
You can simplify that query quite a bit. No need for the nesting.
SELECT t1.ID, t1.Title, clean.Cleaned
INTO dbo.TestTable
FROM t1
CROSS APPLY dbo.if_cleanwithreplace(t1.Title) clean
;
The purpose of the iTVF, in this case, is to function more like an iSF (Inline Scalar Function) which is typically about 7 times faster than a run-of-the-mill scalar function. See the following article for more on that.
http://www.sqlservercentral.com/articles/T-SQL/91724/
Putting the results in a table doesn't negate the value of such functions. It depends on what you want to do. The results could just as easily be returned directly.
--Jeff Moden
Change is inevitable... Change for the better is not.