Akash's questions

在下面的代码中，按OUTPUT Updated.*原样执行时失败，但删除FROMinUPDATE并将其替换为静态值可以正常工作。OUTPUT与 一起使用时是否需要不同的语法UPDATE FROM？

 USE SCRATCH

 CREATE TABLE Tab1(a char(10))
 CREATE TABLE Tab2(a char(10))
 CREATE TABLE Tab3(a char(10))

 INSERT INTO Tab1(a) values ('a')
 INSERT INTO Tab1(a) values ('a')
 INSERT INTO Tab1(a) values ('a')
 INSERT INTO Tab1(a) values ('a')
 INSERT INTO Tab1(a) values ('a')

 INSERT INTO Tab2(a) values ('a')
 INSERT INTO Tab2(a) values ('a')
 INSERT INTO Tab2(a) values ('a')
 INSERT INTO Tab2(a) values ('a')
 INSERT INTO Tab2(a) values ('a')

 UPDATE T1
 SET T1.a = T2.a
 FROM Tab1 T1 JOIN Tab2 T2 ON T1.a = T2.a
 OUTPUT Updated.*

我得到的错误是

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'OUTPUT'.

在 SQL Server 2012 实例上托管的数据库上，我已启用ALLOW_SNAPSHOT_ISOLATION并验证状态为ON使用

SELECT snapshot_isolation_state_desc,name from sys.databases

TABLOCK但是，在 2 个单独的会话中，如果我在第一个和UPDATE第二个（反之亦然）中运行一个长时间运行的 select ，则无论哪个查询首先开始都会阻止第二个查询（根据sp_who2）

看着

select * from sys.dm_exec_requests，这两个查询的事务隔离级别均为已提交读 (2)

据我了解，启用快照隔离后，tempdb 的使用量应该会增加，但在这种情况下不应发生阻塞。我是否缺少一些配置步骤来实现此行为？

CREATE TABLE TestTab (ID INT IDENTITY(1,1), st nvarchar(100))

INSERT INTO TestTab (st) values ('a')
INSERT INTO TestTab (st) values ('b')
INSERT INTO TestTab (st) values ('c')
INSERT INTO TestTab (st) values ('d')
INSERT INTO TestTab (st) values ('e')

INSERT INTO TestTab (st) SELECT TOP 10000 st from testtab
GO 30

ALTER TABLE TestTab ADD newcol nvarchar(10) DEFAULT 'newcol'
UPDATE TestTab SET newcol = 'newcol'  --6 sec
ALTER TABLE TestTab ADD newcol1 nvarchar(10) DEFAULT 'newcol1' NOT NULL

DROP TABLE TestTab

当我执行这个测试脚本时，ALTERwithUPDATE需要 6 秒，这是可以理解的。

但是，即使在更大的表上， ALTERwith也会立即执行。DEFAULT NOT NULL有什么解释为什么这是瞬时的吗？在物理磁盘上，数据仍然需要写入所有行对吗？

我尝试查看SET STATISTICS IO ON查询计划，但是这些似乎不适用于 DDL 操作。

将多个报告 (.rdl) 连同各自的数据源部署到无法从 Visual Studio 连接到的计算机的最简单方法是什么？

我只能将文件放在给定位置，通过远程桌面连接到报表服务器并执行它们。我不允许打开防火墙端口，或在我的机器和报表服务器之间建立任何其他类型的连接。

一种方法是一次一个地手动上传文件，我希望避免这种情况。

表 DDL：

CREATE TABLE trigtest(a INTEGER IDENTITY(1,1) PRIMARY KEY, b INT)

我目前有

CREATE TRIGGER MaxDelete ON trigtest INSTEAD OF DELETE AS
IF @@rowcount < 1000
DELETE trigtest  WHERE a IN (SELECT a FROM deleted)

但是，它要求我以自定义方式为我应用它的每个表编写最后一行

而不是上面的，是否可以写这样的东西：

IF @@rowcount > 1000 (OR SELECT COUNT(*) FROM DELETED)
Kill statement
ELSE
Continue statement

如果我按以下格式设置 SSRS

服务器 1：运行 SQL Server，托管 SSRS 数据库

服务器 2：运行 SSRS，配置为在服务器 1 数据库上存储元数据

并且 SSRS 报告被配置为连接服务器 3

到服务器 3 的连接是从服务器 1 还是服务器 2 进行的？

选择“log to table”选项并运行作业后，当我回到此屏幕并选择 View 时，我可以在文本文件中看到 powershell 脚本的输出。

唯一的工作步骤是调用 powershell 脚本，该脚本将字符串作为输入并在执行toUpper

但是，这些日志存储在哪里？

--Setup, actual queries after the space
--create table fltrind (a integer,b integer)
truncate table fltrind
DROP INDEX fltrind.nf
DROP INDEX fltrind.f
DECLARE @i integer
SET @i=0
while @i<100
BEGIN
INSERT INTO fltrind(a) values(@i)
SET @i=@i+1
END
INSERT INTO fltrind(a,b) values(9000,0900)

insert into fltrind(a,b)
select top 100000 f1.a,f1.b from fltrind f1 , fltrind f2

create nonclustered index nf on fltrind(b) INCLUDE(a)
create nonclustered index f on fltrind(b) INCLUDE(a) where b is not null

UPDATE STATISTICS fltrind WITH FULLSCAN

select a from fltrind where b is not null

DROP INDEX fltrind.nf

UPDATE STATISTICS fltrind WITH FULLSCAN

select a from fltrind where b is not null

查看查询计划，只要存在非过滤索引，就不会使用过滤索引。知道为什么，以及如何让它使用过滤索引？

删除非过滤索引nf会使优化器使用过滤索引f。

事实上，增加行数以使超过 10% 的行符合条件会导致在删除非过滤索引时进行表扫描。

是否有任何边缘情况建议在存储过程开始时显式检查、删除和创建临时表，而不是仅仅创建它们？

同样，是否存在在存储过程结束时显式删除它们比让 SQL Server 清理它们更可取的情况？

我知道出于性能原因应该避免它，但我试图展示它作为演示出现的情况，以确保它不会出现。

然而，我最终得到一个缺少索引的警告，但优化器选择不创建临时索引。

我正在使用的查询是

SELECT 
    z.a
FROM dbo.t5 AS z WITH(INDEX(0))
WHERE 
    EXISTS 
    (
        SELECT y.a 
        FROM dbo.t4 AS y
        WHERE y.a = z.a
    )
OPTION (MAXDOP 1);

表模式是：

CREATE TABLE dbo.t4
(
    a   integer NULL,
    b   varchar(1000) NULL,
    p   varchar(100) NULL
);

CREATE TABLE dbo.t5
(
    a   integer NULL,
    b   varchar(1000) NULL
);

CREATE UNIQUE CLUSTERED INDEX c1 
ON dbo.t5 (a);

两个表都有 10,000 行，您可以使用以下方法进行模拟：

UPDATE STATISTICS dbo.t4 
WITH 
    ROWCOUNT = 10000, 
    PAGECOUNT = 1000;

UPDATE STATISTICS dbo.t5 
WITH 
    ROWCOUNT = 10000,
    PAGECOUNT = 1000;

查询计划为：

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.2218.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="5532.16" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.407384" StatementText="select a from t5  z WITH(INDEX(0))  where exists (select a from t4 where a=z.a )" StatementType="SELECT" QueryHash="0x1B882FCEA34AEAF4" QueryPlanHash="0x1B276DC04B718F7C" RetrievedFromCache="true">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" MemoryGrant="2912" CachedPlanSize="32" CompileTime="10" CompileCPU="10" CompileMemory="296">
            <MissingIndexes>
              <MissingIndexGroup Impact="82.4536">
                <MissingIndex Database="[planoper]" Schema="[dbo]" Table="[t4]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[a]" ColumnId="1" />
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
            </MissingIndexes>
            <MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="2912" RequiredMemory="1024" DesiredMemory="2912" RequestedMemory="2912" GrantWaitTime="0" GrantedMemory="2912" MaxUsedMemory="896" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104846" EstimatedPagesCached="11834" EstimatedAvailableDegreeOfParallelism="2" />
            <RelOp AvgRowSize="11" EstimateCPU="0.228447" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="5532.16" LogicalOp="Left Semi Join" NodeId="0" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.407384">
              <OutputList>
                <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a" />
              </OutputList>
              <MemoryFractions Input="1" Output="1" />
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="10000" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <Hash>
                <DefinedValues />
                <HashKeysBuild>
                  <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a" />
                </HashKeysBuild>
                <HashKeysProbe>
                  <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t4]" Column="a" />
                </HashKeysProbe>
                <ProbeResidual>
                  <ScalarOperator ScalarString="[planoper].[dbo].[t4].[a]=[planoper].[dbo].[t5].[a] as [z].[a]">
                    <Compare CompareOp="EQ">
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t4]" Column="a" />
                        </Identifier>
                      </ScalarOperator>
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a" />
                        </Identifier>
                      </ScalarOperator>
                    </Compare>
                  </ScalarOperator>
                </ProbeResidual>
                <RelOp AvgRowSize="11" EstimateCPU="0.0110785" EstimateIO="0.0565368" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="10000" LogicalOp="Clustered Index Scan" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0676153" TableCardinality="10000">
                  <OutputList>
                    <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="10000" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <IndexScan Ordered="false" ForcedIndex="true" ForceScan="false" NoExpandHint="false">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[planoper]" Schema="[dbo]" Table="[t5]" Index="[c1]" Alias="[z]" IndexKind="Clustered" />
                  </IndexScan>
                </RelOp>
                <RelOp AvgRowSize="11" EstimateCPU="0.011157" EstimateIO="0.100162" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="10000" LogicalOp="Table Scan" NodeId="2" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.111319" TableCardinality="10000">
                  <OutputList>
                    <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t4]" Column="a" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="10000" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t4]" Column="a" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[planoper]" Schema="[dbo]" Table="[t4]" IndexKind="Heap" />
                  </TableScan>
                </RelOp>
              </Hash>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

它甚至告诉我创建这个索引：

USE [planoper];
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[t4] ([a]);

参考这个 StackOverflow 问题：https ://stackoverflow.com/questions/13307485/sql-server-clearing-cache-has-no-effect-on-io-stats

我使用了跟踪标志 652，它当时正确地禁用了预读功能。

我回到它，它不再这样做了。

询问：

DBCC DROPCLEANBUFFERS
DBCC TRACEON(652,-1)
select a.JDID,a.JDDESC from t1 a with (Index = ccsi) JOIN t2 b on a.manager = b.JDID JOIN t3 c on a.department = c.jdid

统计：

Table 't1'. Scan count 4, logical reads 5884, physical reads 364, read-ahead reads 13679, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

执行计划：

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.2218.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="566392" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="3.98206" StatementText="select a.JDID,a.JDDESC from t1 a with (Index = ccsi) JOIN t2 b on a.manager = b.JDID JOIN t3 c on a.department = c.jdid" StatementType="SELECT" QueryHash="0xD7458E0CA3CDD337" QueryPlanHash="0xB4104FE9B0A0FBEE" RetrievedFromCache="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan CachedPlanSize="72" CompileTime="16" CompileCPU="16" CompileMemory="472">
            <ThreadStat Branches="3" />
            <MemoryGrantInfo SerialRequiredMemory="2048" SerialDesiredMemory="2952" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104846" EstimatedPagesCached="12447" EstimatedAvailableDegreeOfParallelism="2" />
            <RelOp AvgRowSize="65" EstimateCPU="1.75104" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="566392" LogicalOp="Gather Streams" NodeId="0" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="3.98206">
              <OutputList>
                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDID" />
                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDDESC" />
              </OutputList>
              <Parallelism>
                <RelOp AvgRowSize="65" EstimateCPU="0.215848" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="566392" LogicalOp="Inner Join" NodeId="1" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="2.23102">
                  <OutputList>
                    <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDID" />
                    <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDDESC" />
                  </OutputList>
                  <MemoryFractions Input="1" Output="1" />
                  <Hash>
                    <DefinedValues />
                    <HashKeysBuild>
                      <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" Column="JDID" />
                    </HashKeysBuild>
                    <HashKeysProbe>
                      <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="manager" />
                    </HashKeysProbe>
                    <ProbeResidual>
                      <ScalarOperator ScalarString="[bigdatatest].[dbo].[t1].[manager] as [a].[manager]=[bigdatatest].[dbo].[t2].[JDID] as [b].[JDID]">
                        <Compare CompareOp="EQ">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="manager" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" Column="JDID" />
                            </Identifier>
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                    </ProbeResidual>
                    <RelOp AvgRowSize="11" EstimateCPU="0.0286198" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Repartition Streams" NodeId="2" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0623762">
                      <OutputList>
                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" Column="JDID" />
                      </OutputList>
                      <Parallelism PartitioningType="Hash">
                        <PartitionColumns>
                          <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" Column="JDID" />
                        </PartitionColumns>
                        <RelOp AvgRowSize="11" EstimateCPU="0.00093625" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="100" LogicalOp="Batch Hash Table Build" NodeId="3" Parallel="true" PhysicalOp="Batch Hash Table Build" EstimatedTotalSubtreeCost="0.0337563">
                          <OutputList>
                            <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" Column="JDID" />
                          </OutputList>
                          <MemoryFractions Input="0.477876" Output="0.477876" />
                          <BatchHashTableBuild>
                            <RelOp AvgRowSize="11" EstimateCPU="0.0286874" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Distribute Streams" NodeId="4" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0328201">
                              <OutputList>
                                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" Column="JDID" />
                              </OutputList>
                              <Parallelism PartitioningType="Demand">
                                <RelOp AvgRowSize="11" EstimateCPU="0.000267" EstimateIO="0.00386574" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Table Scan" NodeId="5" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.00413274" TableCardinality="100">
                                  <OutputList>
                                    <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" Column="JDID" />
                                  </OutputList>
                                  <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" Column="JDID" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" IndexKind="Heap" />
                                  </TableScan>
                                </RelOp>
                              </Parallelism>
                            </RelOp>
                          </BatchHashTableBuild>
                        </RelOp>
                      </Parallelism>
                    </RelOp>
                    <RelOp AvgRowSize="69" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="750056" LogicalOp="Repartition Streams" NodeId="6" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1.95279">
                      <OutputList>
                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDID" />
                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDDESC" />
                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="manager" />
                      </OutputList>
                      <Parallelism PartitioningType="Hash">
                        <PartitionColumns>
                          <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="manager" />
                        </PartitionColumns>
                        <RelOp AvgRowSize="69" EstimateCPU="0.287091" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="750056" LogicalOp="Inner Join" NodeId="7" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.95279">
                          <OutputList>
                            <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDID" />
                            <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDDESC" />
                            <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="manager" />
                          </OutputList>
                          <MemoryFractions Input="0.522124" Output="0.522124" />
                          <Hash>
                            <DefinedValues />
                            <HashKeysBuild>
                              <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" Column="JDID" />
                            </HashKeysBuild>
                            <HashKeysProbe>
                              <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="department" />
                            </HashKeysProbe>
                            <ProbeResidual>
                              <ScalarOperator ScalarString="[bigdatatest].[dbo].[t1].[department] as [a].[department]=[bigdatatest].[dbo].[t3].[JDID] as [c].[JDID]">
                                <Compare CompareOp="EQ">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="department" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" Column="JDID" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                            </ProbeResidual>
                            <RelOp AvgRowSize="11" EstimateCPU="0.0286198" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Repartition Streams" NodeId="8" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0623762">
                              <OutputList>
                                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" Column="JDID" />
                              </OutputList>
                              <Parallelism PartitioningType="Hash">
                                <PartitionColumns>
                                  <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" Column="JDID" />
                                </PartitionColumns>
                                <RelOp AvgRowSize="11" EstimateCPU="0.00093625" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="100" LogicalOp="Batch Hash Table Build" NodeId="9" Parallel="true" PhysicalOp="Batch Hash Table Build" EstimatedTotalSubtreeCost="0.0337563">
                                  <OutputList>
                                    <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" Column="JDID" />
                                  </OutputList>
                                  <MemoryFractions Input="0.522124" Output="0.522124" />
                                  <BatchHashTableBuild>
                                    <RelOp AvgRowSize="11" EstimateCPU="0.0286874" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Distribute Streams" NodeId="10" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0328201">
                                      <OutputList>
                                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" Column="JDID" />
                                      </OutputList>
                                      <Parallelism PartitioningType="Demand">
                                        <RelOp AvgRowSize="11" EstimateCPU="0.000267" EstimateIO="0.00386574" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Table Scan" NodeId="11" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.00413274" TableCardinality="100">
                                          <OutputList>
                                            <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" Column="JDID" />
                                          </OutputList>
                                          <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
                                            <DefinedValues>
                                              <DefinedValue>
                                                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" Column="JDID" />
                                              </DefinedValue>
                                            </DefinedValues>
                                            <Object Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" IndexKind="Heap" />
                                          </TableScan>
                                        </RelOp>
                                      </Parallelism>
                                    </RelOp>
                                  </BatchHashTableBuild>
                                </RelOp>
                              </Parallelism>
                            </RelOp>
                            <RelOp AvgRowSize="73" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="999998" LogicalOp="Repartition Streams" NodeId="12" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1.60332">
                              <OutputList>
                                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDID" />
                                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDDESC" />
                                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="manager" />
                                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="department" />
                              </OutputList>
                              <Parallelism PartitioningType="Hash">
                                <PartitionColumns>
                                  <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="department" />
                                </PartitionColumns>
                                <RelOp AvgRowSize="73" EstimateCPU="0.0550077" EstimateIO="1.54831" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="999998" LogicalOp="Index Scan" NodeId="13" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="1.60332" TableCardinality="999998">
                                  <OutputList>
                                    <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDID" />
                                    <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDDESC" />
                                    <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="manager" />
                                    <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="department" />
                                  </OutputList>
                                  <IndexScan Ordered="false" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="ColumnStore">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDID" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDDESC" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="manager" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="department" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Index="[ccsi]" Alias="[a]" IndexKind="NonClustered" />
                                  </IndexScan>
                                </RelOp>
                              </Parallelism>
                            </RelOp>
                          </Hash>
                        </RelOp>
                      </Parallelism>
                    </RelOp>
                  </Hash>
                </RelOp>
              </Parallelism>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

采取以下代码：

Declare @a integer
set @a = 0
set @a = @a | cast (1 as BINARy)
set @a = @a | cast (100 as BINARy)
select cast (@a as integer)

现在，这段代码的输出不应该是 5 吗？

我有一个整数，将它设置为 0，所以在二进制中它全是 0

然后我OR用 1 ，所以它变成 0..001

然后我OR用二进制的 100，所以它应该变成 00..0101，十进制是 5

我实际得到的输出是 101

编辑：为了更清楚起见，我试图用 TSQL 完成以下代码在 C++ 中的作用

#include<stdio.h>
#include<iostream>
using namespace std;
int main()
{
int a;
a = 0;
a = a | 1;
cout<< a;
a = a | 0b100;
cout<<endl<<a;
}

如果我通过检查它们的相对“查询成本”作为高级性能指标来比较 2 个查询，缓冲区状态是否对执行计划中显示的值有任何影响？

假设我运行以下查询：

declare @i integer
set @i=1
while @i < 2000000
BEGIN
insert into t1 (ID,DESC,date) values (@i%200,concat(NEWID(),newid()),GETDATE())
set @i = @i +1
END

循环实际上是在客户端机器上运行，并且每次都通过网络调用插入，还是循环在服务器上运行并且只进行本地调用？

同样，通过使用 SSMS 连接到服务器或使用 RDP 连接到服务器并使用本地 SSMS 实例来运行此类查询更好吗？