条件联合都在表函数中

So use case is as follows - there're some parameter, based on which I want to select data from one table or another.

用例如下-有一些参数，我想从一个表或另一个表中选择数据。

create table dbo.TEST1 (id int primary key, name nvarchar(128))
create table dbo.TEST2 (id int primary key, name nvarchar(128))

So I've created function like this:

我创建了这样的函数:

create function [dbo].[f_TEST]
(
    @test bit
)
returns table
as
return (
    select id, name from TEST1 where @test = 1

    union all

    select id, name from TEST2 where @test = 0
)

When I run it with constant, the execution plan is great - only one table is scanned

当我使用常量运行它时，执行计划很好——只有一个表被扫描

select * from dbo.f_TEST(1)

But, then, when I use variable, plan is not that good - both tables are scanned

但是，当我使用变量时，plan不是那么好——两个表都要扫描

declare @test bit = 1

select * from dbo.f_TEST(@test)

So are there any hints (or tricks) to force SQL Server to understand that in a certain query only one table should be scanned?

那么，是否有任何提示(或技巧)来迫使SQL Server理解在某个查询中只应该扫描一个表?

5 个解决方案

#1

If your function is inline-TVP(as in example) then you could use:

如果您的函数是inline-TVP(例如)，那么您可以使用:

declare @test bit = 1
select * from dbo.f_TEST(@test) OPTION (RECOMPILE);

Then in both cases you will get single clustered index scan.

然后，在这两种情况下，都将得到单个群集索引扫描。

DBFiddle Demo

DBFiddle演示

From Option RECOMPILE:

从选项编译:

When compiling query plans, the RECOMPILE query hint uses the current values of any local variables in the query and, if the query is inside a stored procedure, the current values passed to any parameters.

在编译查询计划时，重新编译查询提示使用查询中任何本地变量的当前值，如果查询位于存储过程中，则传递给任何参数的当前值。

#2

It works fine as is. Take a look at "Number of Executions" on the relevant table, as you change your parameter value. The tables that will be excluded appear in the plan, because they must be considered, but that does not mean they will be scanned.

它运行得很好。当您更改参数值时，请查看相关表上的“执行次数”。将被排除的表格出现在计划中，因为它们必须被考虑，但这并不意味着它们将被扫描。

Also, look at the Startup Expression on the Filter:

另外，看看过滤器上的启动表达式:

#3

You may wish to try

你可以试试

select top (@test*100) percent id, name from TEST1 

union all

select top ((1-@test)*100) percent id, name from TEST2

#4

Use a stored procedure instead of table function, but beware of parameter sniffing. You can use dynamic SQL instead inside a stored procedure to produce the same result you seek using a table function.

使用存储过程而不是表函数，但要注意参数嗅探。您可以在存储过程中使用动态SQL来生成与使用表函数所查找的结果相同的结果。

This article will explain why what you are doing works the way that it does. https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/user-defined-functions

这篇文章将解释为什么你所做的事情会以它所做的方式进行。https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/user-defined-functions

I'm sure that you want to do something more with the function, which is why you may not want to create a stored procedure instead. There is a way to use the results of a sporc execution in a query. But, that would be a different question than what you've logged here.

我确信您希望对这个函数做更多的事情，这就是为什么您可能不希望创建一个存储过程。有一种方法可以在查询中使用sporc执行的结果。但是，那将是一个不同于你在这里所记录的问题。

#5

-2

OPTION (RECOMPILE) won't help you here when your function is used against a table. This will query, for example, will scan both tables

当对表使用函数时，选项(重新编译)不会在这里帮助您。例如，这个查询将扫描两个表

-- 3rd table to test against
create table dbo.TEST3 (id int primary key, test bit);
insert dbo.TEST3 values(1,1),(2,1),(3,0),(4,1);
GO

select TEST3.* 
from TEST3 
CROSS APPLY dbo.f_TEST(test3.test) 
OPTION (RECOMPILE);

That's okay though. I'm short on time (otherwise I'd include a screenshot) but if you run these three queries with the actual execution plan on you'll see that the optimizer see's these as having the same cost:

不过没关系。我的时间不够(否则我会包括一个截屏)，但是如果你运行这三个带有实际执行计划的查询，你会发现优化器认为它们的成本是一样的:

DECLARE @test int = 1

select * from dbo.f_TEST(1)
select * from dbo.f_TEST(@test)
select * from dbo.f_TEST(@test) OPTION (RECOMPILE)

The second query will appear that it's twice as expensive as the 1st and last but, when you hover over the SELECT operator, you'll see that it's because the optimizer is estimating two rows instead of 1 (as is the case with the other two).

第二个查询的开销将是第一个和最后一个查询的两倍，但是，当您悬停在SELECT操作符上时，您将看到，这是因为优化器估计的是两行，而不是1行(与其他两行一样)。

If you do some performance testing you'll see that, in this case, the optimizer is probably correct.

如果您进行一些性能测试，您将看到，在本例中，优化器可能是正确的。

The bigger issue with your code is that a table scan is guaranteed for each table because you have no filter on either query. Adding a filter, if possible, will enable to you to index this both tables in a way where a seek happens instead of a scan.

代码中更大的问题是每个表都保证有表扫描，因为这两个查询都没有过滤器。如果可能的话，添加一个过滤器将使您能够以查找而不是扫描的方式对这两个表进行索引。

#1