Working with Multi-statement table valued function can cause the SQL Optimizer to not estimate accurately the amount of rows. The SQL Optimizer cannot \ doesn’t know the amount of rows , building the execution plan on guesswork . This can result in an inefficient execution plan causing the Estimated number of rows = 1
Similar problems are experienced when working with Table Variables or Recursive CTEs. Read up on SQL Table variable and temporary table
This basic example illustrates the problem. Below the code , I’ve included the execution plan from Test 1 (using the select statement calling function) and Test 2 ( a straightforward SELECT statement)
In Test 1 – the estimated Rows are 1 and in Test 2 – the estimated rows are 999.
Avoid using Multi-statement table valued function in these situations. Focus on creating code which allows the SQL Optimizer to make an accurate estimate .
use master
go
--create a table to store the values
create table jvtest
(ID INT,
values_insert nvarchar(100)
)
--add rows to the table
declare @cnt INT
SET @cnt = 0
while @cnt < 1000
begin
INSERT INTO jvtest
select @cnt, @cnt
set @cnt = @cnt + 1
end
--create a multi statement table valued function
CREATE FUNCTION dbo.estimatedRows(@ID INT)
RETURNS @IDS TABLE
(ID INT NOT NULL,
values_insert NVARCHAR(100) NOT NULL
)
AS
BEGIN
INSERT INTO @IDS
select ID,values_insert from jvtest where ID > @ID
RETURN
END
GO
--Test 1 : execute select using the multi table valued function
select * from dbo.estimatedRows(0)
--Test 2 : execute a standard select statement
select ID,values_insert from jvtest where ID > 0
--drop the table
drop table jvtest
SQL Execution Plan - Comparing Estimated Rows and Actual Rows
Performance Tuning - Save hundreds of hours
How to troubleshoot a slow running query - SQL Server DBA
