I was having a horrible time today trying to get a query to perform the way I would expect. I had to make a slight change to a table valued function that lives in the query yesterday and that change created a huge performance impact on the query. After evaluating the execution plan and looking at statistics IO and Time I found that because I changed the function to return a table variable instead of just a result set it was doing a full scan on one of the tables being queried.

My question is why would having it return the table (TableVariable) instead of just a Select / Result set cause such a big change to the plan?

Stumped....

sql-server sql-server-2005 sql-server-2008
Edit
Report