Skip to content

Instantly share code, notes, and snippets.

@kappa7194

kappa7194/1 - schema.sql

Created May 14, 2013 10:17
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save kappa7194/5574997 to your computer and use it in GitHub Desktop.
Save kappa7194/5574997 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
1 - schema.sql
CREATE TABLE [dbo].[MyTable]
(
[MyColumn] DATE NOT NULL PRIMARY KEY
)
GO
INSERT INTO [dbo].[MyTable] ([MyColumn]) VALUES ('2013-01-01')
INSERT INTO [dbo].[MyTable] ([MyColumn]) VALUES ('2013-01-02')
INSERT INTO [dbo].[MyTable] ([MyColumn]) VALUES ('2013-01-03')
INSERT INTO [dbo].[MyTable] ([MyColumn]) VALUES ('2013-01-04')
INSERT INTO [dbo].[MyTable] ([MyColumn]) VALUES ('2013-01-05')
GO
Raw
2 - query.sql
SET SHOWPLAN_XML ON
GO
SELECT [MyColumn] FROM [dbo].[MyTable] WHERE [MyColumn] > CONVERT(DATETIME, '2013-01-03T12:00:00.000', 126)
GO
Raw
3 - plan.xml
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.1790.0">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="SELECT [MyColumn] FROM [dbo].[MyTable] WHERE [MyColumn] &gt; CONVERT(DATETIME, '2013-01-03T12:00:00.000', 126)" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0032842" StatementEstRows="2" StatementOptmLevel="TRIVIAL" QueryHash="0x4A2A79BE969B346C" QueryPlanHash="0x8DD885E0A84892F9" ParameterizedText="(@1 varchar(8000))SELECT [MyColumn] FROM [dbo].[MyTable] WHERE [MyColumn]&gt;CONVERT([datetime],@1,(126))">
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="24" CompileTime="2" CompileCPU="2" CompileMemory="96">
<RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="2" EstimateIO="0.003125" EstimateCPU="0.0001592" AvgRowSize="10" EstimatedTotalSubtreeCost="0.0032842" TableCardinality="5" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[Test]" Schema="[dbo]" Table="[MyTable]" Column="MyColumn" />
</OutputList>
<NestedLoops Optimized="0">
<OuterReferences>
<ColumnReference Column="Expr1005" />
<ColumnReference Column="Expr1006" />
<ColumnReference Column="Expr1004" />
</OuterReferences>
<RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="0" AvgRowSize="17" EstimatedTotalSubtreeCost="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Column="Expr1005" />
<ColumnReference Column="Expr1006" />
<ColumnReference Column="Expr1004" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ValueVector>
<ColumnReference Column="Expr1005" />
<ColumnReference Column="Expr1006" />
<ColumnReference Column="Expr1004" />
</ValueVector>
<ScalarOperator ScalarString="GetRangeWithMismatchedTypes(CONVERT(datetime,[@1],126),NULL,(6))">
<Intrinsic FunctionName="GetRangeWithMismatchedTypes">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1003">
<ScalarOperator>
<Convert DataType="datetime" Style="126" Implicit="0">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@1" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(6)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp NodeId="2" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="1" EstimateIO="0" EstimateCPU="0" AvgRowSize="0" EstimatedTotalSubtreeCost="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList />
<ConstantScan />
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp NodeId="6" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="2" EstimateIO="0.003125" EstimateCPU="0.0001592" AvgRowSize="10" EstimatedTotalSubtreeCost="0.0032842" TableCardinality="5" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[Test]" Schema="[dbo]" Table="[MyTable]" Column="MyColumn" />
</OutputList>
<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Test]" Schema="[dbo]" Table="[MyTable]" Column="MyColumn" />
</DefinedValue>
</DefinedValues>
<Object Database="[Test]" Schema="[dbo]" Table="[MyTable]" Index="[PK__MyTable__FC2639007F60ED59]" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<StartRange ScanType="GT">
<RangeColumns>
<ColumnReference Database="[Test]" Schema="[dbo]" Table="[MyTable]" Column="MyColumn" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Expr1005]">
<Identifier>
<ColumnReference Column="Expr1005" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</StartRange>
<EndRange ScanType="LT">
<RangeColumns>
<ColumnReference Database="[Test]" Schema="[dbo]" Table="[MyTable]" Column="MyColumn" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Expr1006]">
<Identifier>
<ColumnReference Column="Expr1006" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<ParameterList>
<ColumnReference Column="@1" ParameterCompiledValue="'2013-01-03T12:00:00.000'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment