主页 / user-317543

Suleyman Essa's questions

Martin Hope
Suleyman Essa
Asked: 2025-04-13 20:55:46 +0800 CST
  • 5

我正在阅读这篇文章 并做了以下事情：

go
create table fiirst (
col1 int,
col2 int
);
create table seecond(
col1 int,
col2 int
);
with
n1(c) as (select 0 union all select 0 ),
n2(c) as ( select 0 from n1 as t1 cross join n1 as t2),
n3(c) as ( select 0 from n2 as t1 cross join n2 as t2),
n4(c) as (select 0 from n3 as t1 cross join n3 as t2),
ids(id) as (select ROW_NUMBER() over (order by (select null)) from n4)
insert into fiirst(col1,col2)
select id,id 
from ids;
with
n1(c) as (select 0 union all select 0 ),
n2(c) as ( select 0 from n1 as t1 cross join n1 as t2),
n3(c) as ( select 0 from n2 as t1 cross join n2 as t2),
n4(c) as (select 0 from n3 as t1 cross join n3 as t2),
ids(id) as (select ROW_NUMBER() over (order by (select null)) from n4)
insert into seecond(col1,col2)
select id,id
from ids;
----Craig Freedman's query

select *
from fiirst
where fiirst.col1 > (
    select min(seecond.col1)
    from seecond
    where seecond.col2 < fiirst.col2
);

尽管表本身是堆，但我还是得到了一个索引假脱机。问题是，这是怎么发生的？为什么我会在堆上得到一个索引假脱机？在上面链接中提到的例子中，没有行，所以没有假脱机，但在这里我却看到了？

sql-server
Martin Hope
Suleyman Essa
Asked: 2025-01-21 23:01:17 +0800 CST
  • 9

在 AdventureWorks2016 中执行以下查询时：

USE AdventureWorks2016

BEGIN TRAN;
SELECT 
    Sales.SalesOrderHeader.SalesOrderID
FROM 
    Sales.SalesOrderHeader
JOIN 
    Sales.Customer 
    ON Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID
OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 8606, QUERYTRACEON 8607, QUERYTRACEON 3604);
COMMIT TRAN;
GO

我对 CustomerID 进行了非聚集扫描（计划），因此消除了外键，它也适用于采购模式（计划），但在这个查询中（计划）：

BEGIN TRAN 
SELECT P.ProductModelID
FROM Production.Product P
JOIN Production.ProductModel PM
ON P.ProductModelID = PM.ProductModelID
OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 8606, QUERYTRACEON 8607, QUERYTRACEON 3604);
COMMIT TRAN

或者（计划）：

BEGIN TRAN 
SELECT P.ProductID
FROM Production.Product P
JOIN Production.ProductModel PM
ON P.ProductModelID = PM.ProductModelID
OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 8606, QUERYTRACEON 8607, QUERYTRACEON 3604);
COMMIT TRAN

我得到了带有连接的扫描。在 SSMS 21 中的图表编辑器中：

(name)
FK_Product_ProductModel_ProductModelID
description
Foreign key constraint referencing ProductModel.ProductModelID.

我在发布问题之前重新启用了它我仍然有相同的计划外键是

  • 值得信赖
  • 未残疾
  • 不允许空值
  • 不是一个不同的架构

问题是：为什么我没有消除外键？

sql-server
Martin Hope
Suleyman Essa
Asked: 2025-01-21 20:03:05 +0800 CST
  • 6

常量折叠是否有两个级别，一个在转换树中，一个在简化阶段？

如果我运行以下查询

SELECT 
    P.[ProductID]
FROM 
    Production.Product AS P
WHERE
    P.[ProductID] = 1 + 2 * 3
    OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 8606, QUERYTRACEON 8607, QUERYTRACEON 3604);

在转换树中，我发现一个常量，因此我将其称为基本常量折叠？

就像Paul Holmes在这个例子中所说的那样，但是如果我运行Paul White的这个查询

use AdventureWorks2016
begin tran 
SELECT
    P.[Name]
FROM Production.Product AS P
WHERE
    P.[Name] LIKE 
        SUBSTRING(LEFT(CHAR(ASCII(CHAR(68))), 1) + '%', 1, 2)
OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 8606, QUERYTRACEON 8607, QUERYTRACEON 3604);
commit tran

和

begin tran 
SELECT
    P.[Name]
FROM Production.Product AS P
WHERE
    P.[Name] LIKE 
        'd%'
OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 8606, QUERYTRACEON 8607, QUERYTRACEON 3604);
commit tran

就转换树而言，我没有发现两者之间有太大区别，所以常量折叠发生在这里，并且它们的简化树具有相同的运算符？

常量折叠有不同的阶段吗？它们在哪里？我在这里做错了什么，所以我在简化阶段没有看到它？

sql-server
Martin Hope
Suleyman Essa
Asked: 2025-01-14 22:41:23 +0800 CST
  • 7

因此，algebrizer 中的简化应该将一些子查询转换为内连接或外连接。例如，我尝试了这个

DBCC TRACEON(3604)
USE ContosoRetailDW
BEGIN TRAN 
SELECT ProductKey
FROM DimProduct 
WHERE ProductKey IN (SELECT ProductKey 
                     FROM FactSales
                     WHERE SALESKEY <2) 
OPTION(RECOMPILE,QUERYTRACEON 8605)
COMMIT TRAN

输出是

*** Converted Tree: ***
    LogOp_Project QCOL: [ContosoRetailDW].[dbo].[DimProduct].ProductKey

        LogOp_Select

            LogOp_Get TBL: DimProduct DimProduct TableID=117575457 TableReferenceID=0 IsRow: COL: IsBaseRow1000 

            ScaOp_SomeComp 2

                ScaOp_Identifier QCOL: [ContosoRetailDW].[dbo].[DimProduct].ProductKey

                LogOp_Project

                    LogOp_Select

                        LogOp_Get TBL: FactSales FactSales TableID=1602104748 TableReferenceID=0 IsRow: COL: IsBaseRow1002 

                        ScaOp_Comp x_cmpLt

                            ScaOp_Identifier QCOL: [ContosoRetailDW].[dbo].[FactSales].SalesKey

                            ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=2)

                    AncOp_PrjList 

        AncOp_PrjList 

*******************

如果我把它改写成

BEGIN TRAN;
SELECT DISTINCT dp.ProductKey
FROM DimProduct dp
JOIN FactSales fs ON dp.ProductKey = fs.ProductKey
WHERE fs.SalesKey < 2
OPTION (RECOMPILE, QUERYTRACEON 8605);
COMMIT TRAN;

我明白了

*** Converted Tree: ***
    LogOp_GbAgg OUT(QCOL: [dp].ProductKey,) BY(QCOL: [dp].ProductKey,)

        LogOp_Project

            LogOp_Project QCOL: [dp].ProductKey

                LogOp_Select

                    **LogOp_Join**

                        LogOp_Get TBL: DimProduct(alias TBL: dp) DimProduct TableID=117575457 TableReferenceID=0 IsRow: COL: IsBaseRow1000 

                        LogOp_Get TBL: FactSales(alias TBL: fs) FactSales TableID=1602104748 TableReferenceID=0 IsRow: COL: IsBaseRow1001 

                        ScaOp_Comp x_cmpEq

                            ScaOp_Identifier QCOL: [dp].ProductKey

                            ScaOp_Identifier QCOL: [fs].ProductKey

                    ScaOp_Comp x_cmpLt

                        ScaOp_Identifier QCOL: [fs].SalesKey

                        ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=2)

                AncOp_PrjList 

            AncOp_PrjList 

        AncOp_PrjList 

*******************

所以，问题是：如何在第一个查询中获取LogOp_Join ？

我尝试创建一个包含 5000 万行的类似表，但仍然没有成功。导致这种情况发生的条件是什么？

sql-server