最近调优过程中遇到一个问题，就是表值函数作为连接中的一部分时，可能会引起麻烦，本文会简单阐述表值函数是什么，和表值函数连接引发的性能问题。

表值函数

SQL Server中提供了类似其他编程语言的函数，而函数的本质通常是一段代码的封装，并返回值。在SQL Server中，函数除了可以返回简单的数据类型之外(Int、Varchar等)，还可以返回一个集合，也就是返回一个表。

而根据是否直接返回集合或是定义后再返回集合，表值函数又分为内联用户定义表值函数和用户定义表值函数(下文统称为表值函数，省去“用户定义”四个字)。

内联表值函数    内联表值函数和普通函数并无不同，唯一的区别是返回结果为集合(表)，而不是简单数据类型，一个简单的内联表值函数如代码清单1所示(摘自MSDN)。

CREATE FUNCTION Sales.ufn_CustomerNamesInRegion

( @Region nvarchar(50) )

RETURNS table

AS

RETURN

SELECT DISTINCT s.Name AS Store, a.City

FROM Sales.Store AS s

INNER JOIN Person.BusinessEntityAddress AS bea

ON bea.BusinessEntityID = s.BusinessEntityID

INNER JOIN Person.Address AS a

ON a.AddressID = bea.AddressID

INNER JOIN Person.StateProvince AS sp

ON sp.StateProvinceID = a.StateProvinceID

WHERE sp.Name = @Region

GO

代码清单1.一个简单的表值函数

用户定义表值函数

而用户定义表值函数，需要在函数开始时定义返回的表结构，然后可以写任何代码进行数据操作，插入到定义的表结构之后进行返回，一个稍微负责的用户定义表值函数示例如代码清单2所示(摘自MSDN)。

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

RETURNS @retContactInformation TABLE

-- Columns returned by the function

ContactID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL

AS

-- Returns the first name, last name, job title, and contact type for the specified contact.

BEGIN

DECLARE

@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information

SELECT

@ContactID = BusinessEntityID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Person

WHERE BusinessEntityID = @ContactID;

-- Get contact job title

SELECT @JobTitle =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM Person.Person AS p

WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM')

THEN (SELECT JobTitle

FROM HumanResources.Employee AS e

WHERE e.BusinessEntityID = @ContactID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Person.Person AS p

WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC')

THEN (SELECT ct.Name

FROM Person.ContactType AS ct

INNER JOIN Person.BusinessEntityContact AS bec

ON bec.ContactTypeID = ct.ContactTypeID

WHERE bec.PersonID = @ContactID)

-- Check for store

WHEN EXISTS(SELECT * FROM Person.Person AS p

WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC')

THEN (SELECT ct.Name

FROM Person.ContactType AS ct

INNER JOIN Person.BusinessEntityContact AS bec

ON bec.ContactTypeID = ct.ContactTypeID

WHERE bec.PersonID = @ContactID)

ELSE NULL

END;

-- Get contact type

SET @ContactType =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM Person.Person AS p

WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM')

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Person.Person AS p

WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC')

THEN 'Vendor Contact'

-- Check for store

WHEN EXISTS(SELECT * FROM Person.Person AS p

WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC')

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Person.Person AS p

WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'IN')

THEN 'Consumer'

-- Check for general contact

WHEN EXISTS(SELECT * FROM Person.Person AS p

WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'GC')

THEN 'General Contact'

END;

-- Return the information to the caller

IF @ContactID IS NOT NULL

BEGIN

INSERT @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

END;

RETURN;

END;

GO

代码订单2.表值函数

为什么要用表值函数

看起来表值函数所做的事情和存储过程并无不同，但实际上还是有所差别。是因为表值函数可以被用于写入其他查询，而存储过程不行。此外，表值函数和Apply操作符联合使用可以极大的简化连接操作。

如果存储过程符合下述条件的其中一个，可以考虑重写为表值函数。

•存储过程逻辑非常简单，仅仅是一个Select语句，不用视图的原因仅仅是由于需要参数。

•存储过程中没有更新操作。

•存储过程中没有动态SQL。

•存储过程中只返回一个结果集。

•存储过程的主要目的是为了产生临时结果集，并将结果集存入临时表以供其他查询调用。

用户定义表值函数的问题

表值函数与内联表值函数不同，内联表值函数在处理的过程中更像是一个视图，这意味着在查询优化阶段，内联表值函数可以参与查询优化器的优化，比如将筛选条件(Where)推到代数树的底部，这意味着可以先Where再Join，从而可以利用索引查找降低IO从而提升性能。

让我们来看一个简单的例子。下面代码示例是一个简单的和表值函数做Join的例子：

首先我们创建表值函数，分别为内联表值函数方式和表值函数方式，如代码清单3所示。

--创建表值行数

CREATE FUNCTION tvf_multi_Test ( )

RETURNS @SaleDetail TABLE ( ProductId INT )

AS

BEGIN

INSERT INTO @SaleDetail

SELECT ProductID

FROM Sales.SalesOrderHeader soh

INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID

RETURN

END

--创建内联表值函数

CREATE FUNCTION tvf_inline_Test ( )

RETURNS TABLE

AS

RETURN

SELECT ProductID

FROM Sales.SalesOrderHeader soh

INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID

代码清单3.创建两种不同的函数

现在，我们使用相同的查询，对这两个表值函数进行Join，代码如代码清单4所示。

--表值函数做Join

SELECT c.personid ,

Prod.Name ,

COUNT(*) 'numer of unit'

FROM Person.BusinessEntityContact c

INNER JOIN dbo.tvf_multi_Test() tst ON c.personid = tst.ProductId

INNER JOIN Production.Product prod ON tst.ProductId = prod.ProductID

GROUP BY c.personid ,

Prod.Name

--内联表值函数做Join

SELECT c.personid ,

Prod.Name ,

COUNT(*) 'numer of unit'

FROM Person.BusinessEntityContact c

INNER JOIN dbo.tvf_inline_Test() tst ON c.personid = tst.ProductId

INNER JOIN Production.Product prod ON tst.ProductId = prod.ProductID

GROUP BY c.personid ,

Prod.Name

代码清单4.表值函数和内联表值函数做Join

执行的成本如图1所示。

图1.两种方式的成本

从IO来看，很明显是选择了次优的执行计划，BusinessEntityContact选择了121317次查找，而不是一次扫描。而内联表函数能够正确知道扫描一次的成本远低于一次查找。

那问题的根源是内联表值函数，对于SQL Server来说，和视图是一样的，这意味着内联表值函数可以参与到逻辑执行计划的代数运算(或者是代数树优化)中，这意味着内敛表可以进一步拆分(如图1所示，第二个内联表的查询，执行计划具体知道内敛表中是SalesOrderHeader表和SalesOrderDetail表，由于查询只选择了一列，所以执行计划优化直到可以无需扫