In SQL Server 2014 CTP2 in the AdventureWorks2012 database, execute the following batch:

 
USE [AdventureWorks2012];
GO

SELECT [PersonID], [FirstName], [LastName], [JobTitle], [BusinessEntityType]
FROM [dbo].[ufnGetContactInformation](2)
OPTION (QUERYTRACEON 9481);  -- Legacy CE

SELECT [PersonID], [FirstName], [LastName], [JobTitle], [BusinessEntityType]
FROM [dbo].[ufnGetContactInformation](2)
OPTION (QUERYTRACEON 2312); -- New CE

The first query uses the legacy cardinality estimator and the second query uses the new cardinality estimator.   Both queries are referencing a multi-statement table valued function.

Looking at the plan tree view in SQL Sentry Plan Explorer for the legacy CE plan, you’ll see the following (estimating 1 row for the function operators):

image thumb MSTVF Fixed Cardinality Value in SQL Server 2014

Looking at the new CE version of the plan tree, we see the following (estimating 100 rows for the function operators):

image thumb1 MSTVF Fixed Cardinality Value in SQL Server 2014

SQL Server 2014 uses a new default fixed cardinality for multi-statement table valued functions.

A few thoughts:

  • Whether 1 row or 100 rows, we’re still using a fixed guess that may or may not reflect reality
  • I’m very wary of using MSTVFs in scenarios where the estimate is critical for plan quality (and oftentimes it is)