Do SQL Server 2008 functions that return tables get optimized like stored procedures?

After being an ADO.NET disciple for many (, many, many) years, I'm finally trying to figure out Linq. In Linq, table data returned from an SQL Server function is type as IQueryable, whereas data returned from 开发者_C百科a stored procedure is an ISingleResult.

Since IQueryable is somewhat easier to deal with, I'd like to use that. My question: is an SQL function that returns table data as efficient as an equivalent stored procedure?

My question: is an SQL function that returns table data as efficient as an equivalent stored procedure?

Yes the underlying code will eventuate in TSQL that goes through the Query Optimizer the same way, so for "efficiency equivalence", it would have to be a solid yes. However, some things are in the table valued function's favour, especially if the function can be inlined (outer filters taken into the query inside the function).

Whereas an SP that generates a result set (or multiple) can only take input from parameters, an inline table function can bring in additional filters from the outside query. Consider this:

create function dbo.testif() returns table as return
select * from information_schema.columns

and the query

select * from dbo.testif() where TABLE_NAME like 'S%'

The condition TABLE_NAME like 'S%' is actually brought into the query inside the function, which you cannot do with a stored procedure.

LINQ-to-SQL is very performant for simple queries, but when executing more complex statements you create a compiled query for reuse and improved performance.

Check out the System.Data.Linq.CompiledQuery class.

The below link has some performance tests and further discussion: http://www.codeproject.com/KB/linq/LINQquery.aspx

The answers from Cyberwiki and Kirk both helped me out, but using stored procedures in Linq just seems very awkward. I did discover that Linq will treat views just like tables, so I can use a view to shape my results, so I can still do much of that processing on the server, instead of joining the tables inside my application's code.

I think that using views, along with Linq compiled queries for greater performance (as Kirk suggested), will give me the results I want.