SQL Server and Table-Valued User-Defined Function optimizations

Question

If I have an UDF that returns a table, with thousands of rows, but I just want a particular row from that rowset, will SQL Server be able to handle this effciently?

DECLARE @pID int; --...
SELECT * FROM dbo.MyTableUDF(@pID)
WHERE SomeColumn BETWEEN 1 AND 2 OR SomeOtherColumn LIKE 'this_pattern&'

To what extent is the query optimizer capable of reasoning about this type of query?

How are Table-Valued UDFs different from traidtional views if they take no parameters?

Any gotchas I should know about?

Answer 1

Wouldn't you pass in the ID that you require as a parameter rather query the entire table?

Something like this:

CREATE FUNCTION dbo.MyTableUDF(@ID int)
RETURNS @myTable TABLE 
(
    ID int PRIMARY KEY NOT NULL, 
    FirstName nvarchar(50) NULL, 
    LastName nvarchar(50) NULL
)
as begin

Insert Into @myTable (ID, FirstName, LastName)
Select ID, FirstName, LastName
From Users
Where ID = @ID


return

end
go

Select * From MyTableUDF(1)

For this scenario, it would be a far better approach.

EDIT:

Ok well as you're using a Table UDF rather than a view I will assume that it a multi statement table UDF rather than an inline. I am pretty sure that the performance won't be affected by using the UDF in this way.

The performance will really be hit if you used the UDF in a Select statement or Where clause. This is because the UDF will be called for each row returned from the table. e.g Select col1, col2, dbo.MyUDF(col3) From MyTable

or

Select col1, col2 from dbo.MyTable Where dbo.MyUDF(col3) != 1

So if you MyTable contained 100,000 rows, your UDF will be called 100,000 times. If the UDF takes 5 seconds to execute that is where you will run in to issues.

As far as I can make out you don't intend on using the UDF in this manner.