Covering Index question

Question

Post reply

Covering Index question

Kwisatz78

SSChampion

Points: 12478
More actions
November 21, 2011 at 9:35 am

#260453

Hi all
In general terms I understand that when creating covering indexes you use in the index itself the columns from the search and joins, and columns in the select as includes.
However how do you handle it when the select part of the statement has a query a sub query e.g.
(selectTOP(1) mso.[DESCRIPTION]
fromDynamicsV5Realtime.dbo.MARSALESOFFICETABLE mso
wheremso.DATAAREAID = st.DATAAREAID
andmso.SALESOFFICE = st.MARSALESOFFICE)assalesOfficeDescr
Would this be normally handled by a separate index, or would this be ignored?
Thanks

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Please post the full query and actual execution plan along with the create index statement.

Too many ifs to answer that one generally.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

In the mean time. This is relevant to your question : http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/

Kwisatz78 SSChampion Points: 12478 More actions · Answer 3

Hi there, I will post this tomorrow if thats ok, the query is ginourmous which is why I was only intrigued by this aspect of it.

Thanks for the info in the mean time.

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 4

Kwisatz78 (11/21/2011)
Hi there, I will post this tomorrow if thats ok, the query is ginourmous which is why I was only intrigued by this aspect of it.
Thanks for the info in the mean time.

You cannot optimize one of the subqueries alone and then be sure that the server will use the same plan for this part when you run the whole query. It doesn't work this way.

The full query has to be provided otherwise the effort is pointless. Maybe you don't need to pull row by row in a subquery, but something like the following would be better in your case. Who knows.

JOIN

(select

mso.DATAAREAID,

mso.SALESOFFICE,

MIN(mso.[DESCRIPTION]) DESCRIPTION

from DynamicsV5Realtime.dbo.MARSALESOFFICETABLE mso

group by

mso.DATAAREAID,

mso.SALESOFFICE) as salesOfficeDescr

on salesOfficeDescr.DATAAREAID = st.DATAAREAID

and salesOfficeDescr.SALESOFFICE = st.MARSALESOFFICE ...

Alex Suprun

Kwisatz78 SSChampion Points: 12478 More actions · Answer 5

Ok guys and gals I here is the query in question along with the XML for the plan and the missing index statement. I understand if you think this may take up too much of your time.

This is the select taken from a stored proc.

I am a little confused with the missing index statement as it doesn't include all the columns that are returned in the query.

Furthermore if I run the proc with values as opposed to the select, then the missing index returned slightly different results as in the number of included columns.

DECLARE

@Company varchar(3),

@TransportGroup varchar(max),

@EmailKey varchar(10),

@SalesType varchar(200),

@Qwerty varchar(1)

set @Company = 'test'

set @TransportGroup = 'test'

set @EmailKey = 'test'

set @SalesType = '1'

set @Qwerty = 'A'

DECLARE@paramCompanyvarchar(3),

@paramTransportGroupvarchar(max),

@paramEmailKeyvarchar(10),

@paramSalesTypevarchar(200),

@paramQwertyvarchar(1)

SET@paramCompany = @Company

SET@paramTransportGroup = @TransportGroup

SET@paramEmailKey = @EmailKey

SET@paramSalesType = @SalesType

SET@paramQwerty = @Qwerty

SELECT

jt.JOURNEYID as 'Journey',

jo.DROPSEQ as 'DropSequence',

jo.SALESID as 'SalesOrder',

st.INVENTSITEID as 'InventSite',

jo.DESTINATION as 'DeliveryTown',

jo.POSTCODE as 'DeliveryPostcode',

jt.TRANSPORTGROUP as 'TransportGroup',

st.SALESNAME as 'SalesName',

mre3.ENUMVALUESTR as 'SalesType',

mos.[DESCRIPTION] as 'OrderStatus',

mre1.ENUMVALUESTR as 'DeliveryType',

CASEWHEN DATEADD(ss,CASE WHEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),GetDate())

> 0 THEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),GetDate())

ELSE 0END, DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTARRIVALTIME), jo.ESTARRIVALDATE)) -- NewEstArrivalDateTime

>DATEADD(ss, st.MARDELIVERYTIMETO, st.RECEIPTDATEREQUESTED)

THEN 1 ELSE 0 END as 'PotentialLatekey',

CASEWHEN DATEADD(ss,CASE WHEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),DateAdd(ss,3600,GetDate())) -- now plus 1 hour

> 0 THEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),DateAdd(ss,3600,GetDate())) -- now plus 1 hour

ELSE 0END, DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTARRIVALTIME), jo.ESTARRIVALDATE)) -- NewEstArrivalDateTime

>DATEADD(ss, st.MARDELIVERYTIMETO, st.RECEIPTDATEREQUESTED)

THEN 1 ELSE 0 END as 'PotentialLatekey-plusonehour',

jt.VEHICLETYPE + ' - ' + wt.[DESCRIPTION]as 'WagonType',

'Send' as 'EmailKey',

DATEADD(ss, st.MARDELIVERYTIMETO, st.RECEIPTDATEREQUESTED) as 'ReqDeliveryToDateTime',

DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE) as 'EstDespatchDateTime',

DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTARRIVALTIME), jo.ESTARRIVALDATE) as 'EstArrivalDateTime',

CASE WHEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),GetDate())

> 0 THEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),GetDate())

ELSE 0END as 'DespatchSecondsLate',

DATEADD(ss,CASE WHEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),GetDate())

> 0 THEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),GetDate())

ELSE 0END, DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTARRIVALTIME), jo.ESTARRIVALDATE)) as 'NewEstArrivalDateTime',

st.MARTRADINGOFFERID as 'TradingOffer',

emp.name as 'SalesResponsibility'

INTO #dataset

FROM

DynamicsV5Realtime.dbo.MARJOURNEYTABLE jt

INNER JOIN

DynamicsV5Realtime.dbo.MARJOURNEYORDERS jo

ON

jo.JOURNEYID = jt.JOURNEYID

INNER JOIN

DynamicsV5Realtime.dbo.SALESTABLE st

ON

st.SALESID = jo.SALESID

INNER JOIN

DynamicsV5Realtime.dbo.SALESLINE sl

ON

sl.SALESID = st.SALESID

and sl.DATAAREAID = st.DATAAREAID

LEFT OUTER JOIN

DynamicsV5Realtime.dbo.MARAPORDERSTATUSTABLE mos

ON

mos.ORDERSTATUS = st.MARAPORDERSTATUS

and mos.DATAAREAID = st.DATAAREAID

and mos.INVENTTRANSTYPE = 1

LEFT OUTER JOIN

DynamicsV5Realtime.dbo.MARREPORTENUMS mre1

ON

mre1.ENUMVALUEINT = st.MARADDRESSTYPE

and mre1.ENUMNAME = 'MARFOEAddressType'

LEFT OUTER JOIN

DynamicsV5Realtime.dbo.MARWAGONTABLE wt

ON

wt.WAGONTYPE = jt.VEHICLETYPE

and wt.DATAAREAID = 'RCH'

INNER JOIN

DynamicsV5Realtime.dbo.MARREPORTENUMS mre2

ON

mre2.ENUMVALUEINT = jt.[STATUS]

and mre2.ENUMNAME = 'MARJourneyStatus'

INNER JOIN

DynamicsV5Realtime.dbo.MARREPORTENUMS mre3

ON

mre3.ENUMVALUEINT = st.SALESTYPE

and mre3.ENUMNAME = 'SalesType'

Left OUTER Join

udf_R000_EmployeeList() emp

ON

st.SALESRESPONSIBLE = emp.emplid

WHERE

jt.[SYSTEM] = 'AX'

andst.MARAPORDERSTATUS <'SL060'

andCASEWHEN DATEADD(ss,CASE WHEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),DateAdd(ss,3600,GetDate())) -- now plus 1 hour

> 0 THEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),DateAdd(ss,3600,GetDate())) -- now plus 1 hour

ELSE 0END, DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTARRIVALTIME), jo.ESTARRIVALDATE)) -- NewEstArrivalDateTime

>DATEADD(ss, st.MARDELIVERYTIMETO, st.RECEIPTDATEREQUESTED)

THEN 1 ELSE 0 END = 1

and (jt.TRANSPORTGROUP IN (SELECT * FROM dbo.udf_MultiValueParameterHandlingString(@paramTransportGroup)) or @paramTransportGroup = 'All')

and (

CASE WHEN (DATEPART(HH,getdate())*60 + DATEPART(MI,getdate())) BETWEEN '475' and '1025'

AND DATEPART(DW, GETDATE())IN(2,3,4,5,6)

THEN 'Send' ELSE 'Z' END in (@paramEmailKey) or @paramEmailKey = 'All'

)

and (mre3.ENUMVALUEINT IN (SELECT * FROM dbo.udf_MultiValueParameterHandlingString(@paramSalesType)) or @paramSalesType = '99')

and st.RECEIPTDATEREQUESTED <= CAST(FLOOR(CAST(getdate() AS FLOAT))AS DATETIME)

GROUP BY

jt.JOURNEYID,

jo.SALESID,

st.INVENTSITEID,

jo.DESTINATION,

jo.POSTCODE,

jt.TRANSPORTGROUP,

st.SALESNAME,

mre3.ENUMVALUESTR,

mos.[DESCRIPTION],

DATEADD(ss, st.MARDELIVERYTIMETO, st.RECEIPTDATEREQUESTED),

DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),

DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTARRIVALTIME), jo.ESTARRIVALDATE),

mre1.ENUMVALUESTR,

jt.VEHICLETYPE,

wt.[DESCRIPTION],

jo.DROPSEQ,

st.MARTRADINGOFFERID,

emp.name

ORDER BY

jt.JOURNEYID,

jo.DROPSEQ

If @paramQwerty = 'S' SELECT DISTINCT #dataset.EmailKey FROM #dataset

If @paramQwerty = 'D' SELECT DISTINCT * FROM #dataset

DROP TABLE #dataset

Execution Plan

<?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.1" Build="10.0.2531.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

<BatchSequence>

<Batch>

<Statements>

<StmtSimple StatementCompId="11" StatementEstRows="23.1271" StatementId="4" StatementOptmLevel="FULL" StatementSubTreeCost="107.714" StatementText="SELECT jt.JOURNEYID as 'Journey', jo.DROPSEQ as 'DropSequence', jo.SALESID as 'SalesOrder', st.INVENTSITEID as 'InventSite', jo.DESTINATION as 'DeliveryTown', jo.POSTCODE as 'DeliveryPostcode', jt.TRANSPORTGROUP as 'TransportGroup', st.SALESNAME as 'SalesName', mre3.ENUMVALUESTR as 'SalesType', mos.[DESCRIPTION] as 'OrderStatus', mre1.ENUMVALUESTR as 'DeliveryType', CASEWHEN DATEADD(ss,CASE WHEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),GetDate()) > 0 THEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),GetDate()) ELSE 0END, DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTARRIVALTIME), jo.ESTARRIVALDATE)) -- NewEstArrivalDateTime >DATEADD(ss, st.MARDELIVERYTIMETO, st.RECEIPTDATEREQUESTED) THEN 1 ELSE 0 END as 'PotentialLatekey', CASEWHEN DATEADD(ss,CASE WHEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),DateAdd(ss,3600,GetDate())) -- now plus 1 hour > 0 THEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),DateAdd(ss,3600,GetDate())) -- now plus 1 hour ELSE 0END, DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTARRIVALTIME), jo.ESTARRIVALDATE)) -- NewEstArrivalDateTime >DATEADD(ss, st.MARDELIVERYTIMETO, st.RECEIPTDATEREQUESTED) THEN 1 ELSE 0 END as 'PotentialLatekey-plusonehour', jt.VEHICLETYPE + ' - ' + wt.[DESCRIPTION]as 'WagonType', 'Send' as 'EmailKey', DATEADD(ss, st.MARDELIVERYTIMETO, st.RECEIPTDATEREQUESTED) as 'ReqDeliveryToDateTime', DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE) as 'EstDespatchDateTime', DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTARRIVALTIME), jo.ESTARRIVALDATE) as 'EstArrivalDateTime', CASE WHEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),GetDate()) > 0 THEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),GetDate()) ELSE 0END as 'DespatchSecondsLate', DATEADD(ss,CASE WHEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),GetDate()) > 0 THEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),GetDate()) ELSE 0END, DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTARRIVALTIME), jo.ESTARRIVALDATE)) as 'NewEstArrivalDateTime', st.MARTRADINGOFFERID as 'TradingOffer', emp.name as 'SalesResponsibility' INTO #dataset FROM DynamicsV5Realtime.dbo.MARJOURNEYTABLE jt INNER JOIN DynamicsV5Realtime.dbo.MARJOURNEYORDERS jo ON jo.JOURNEYID = jt.JOURNEYID INNER JOIN DynamicsV5Realtime.dbo.SALESTABLE st ON st.SALESID = jo.SALESID INNER JOIN DynamicsV5Realtime.dbo.SALESLINE sl ON sl.SALESID = st.SALESID and sl.DATAAREAID = st.DATAAREAID LEFT OUTER JOIN DynamicsV5Realtime.dbo.MARAPORDERSTATUSTABLE mos ON mos.ORDERSTATUS = st.MARAPORDERSTATUS and mos.DATAAREAID = st.DATAAREAID and mos.INVENTTRANSTYPE = 1 LEFT OUTER JOIN DynamicsV5Realtime.dbo.MARREPORTENUMS mre1 ON mre1.ENUMVALUEINT = st.MARADDRESSTYPE and mre1.ENUMNAME = 'MARFOEAddressType' LEFT OUTER JOIN DynamicsV5Realtime.dbo.MARWAGONTABLE wt ON wt.WAGONTYPE = jt.VEHICLETYPE and wt.DATAAREAID = 'RCH' INNER JOIN DynamicsV5Realtime.dbo.MARREPORTENUMS mre2 ON mre2.ENUMVALUEINT = jt.[STATUS] and mre2.ENUMNAME = 'MARJourneyStatus' INNER JOIN DynamicsV5Realtime.dbo.MARREPORTENUMS mre3 ON mre3.ENUMVALUEINT = st.SALESTYPE and mre3.ENUMNAME = 'SalesType' Left OUTER Join udf_R000_EmployeeList() emp ON st.SALESRESPONSIBLE = emp.emplid WHERE jt.[SYSTEM] = 'AX' andst.MARAPORDERSTATUS <'SL060' andCASEWHEN DATEADD(ss,CASE WHEN DATEDIFF(ss,DATEADD(ss, dbo.udf_ConvertCharToSeconds(jo.ESTDESPATCHTIME), jo.ESTDESPATCHDATE),DateAdd(ss,3600,GetDate())) -- now plus 1 hour > 0 THEN DATEDIFF(" StatementType="SELECT INTO" QueryHash="0x1EB92BA71BF32599" QueryPlanHash="0x1156710B1B4518CA">

<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="0" MemoryGrant="9944" CachedPlanSize="208" CompileTime="5494" CompileCPU="5491" CompileMemory="9824">

<MissingIndexes>

<MissingIndexGroup Impact="80.4894">

<MissingIndex Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[SALESTABLE]">

<ColumnGroup Usage="INEQUALITY">

<Column Name="[RECEIPTDATEREQUESTED]" ColumnId="90" />

<Column Name="[MARAPORDERSTATUS]" ColumnId="127" />

</ColumnGroup>

<ColumnGroup Usage="INCLUDE">

<Column Name="[SALESID]" ColumnId="1" />

<Column Name="[SALESNAME]" ColumnId="2" />

<Column Name="[SALESTYPE]" ColumnId="36" />

<Column Name="[INVENTSITEID]" ColumnId="100" />

<Column Name="[SALESRESPONSIBLE]" ColumnId="123" />

<Column Name="[MARDELIVERYTIMETO]" ColumnId="188" />

<Column Name="[DATAAREAID]" ColumnId="204" />

<Column Name="[MARTRADINGOFFERID]" ColumnId="228" />

<Column Name="[MARADDRESSTYPE]" ColumnId="229" />

</ColumnGroup>

</MissingIndex>

</MissingIndexGroup>

</MissingIndexes>

<RelOp AvgRowSize="9" EstimateCPU="2.61271E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="23.1271" LogicalOp="Sequence" NodeId="0" Parallel="false" PhysicalOp="Sequence" EstimatedTotalSubtreeCost="107.714">

<OutputList />

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<Sequence>

<RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table-valued function" NodeId="1" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="1.157E-06">

<OutputList />

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="1" />

</RunTimeInformation>

<TableValuedFunction>

<DefinedValues />

<Object Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[emp]" />

</TableValuedFunction>

</RelOp>

<RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table-valued function" NodeId="2" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="1.157E-06">

<OutputList />

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="1" />

</RunTimeInformation>

<TableValuedFunction>

<DefinedValues />

<Object Database="[AxReports]" Schema="[dbo]" Table="[udf_MultiValueParameterHandlingString]" />

<ParameterList>

<ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(max),[@paramTransportGroup],0)">

<Identifier>

<ColumnReference Column="ConstExpr1039">

<ScalarOperator>

<Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="true">

<ScalarOperator>

<Identifier>

<ColumnReference Column="@paramTransportGroup" />

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</ColumnReference>

</Identifier>

</ScalarOperator>

</ParameterList>

</TableValuedFunction>

</RelOp>

<RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table-valued function" NodeId="4" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="1.157E-06">

<OutputList />

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="1" />

</RunTimeInformation>

<TableValuedFunction>

<DefinedValues />

<Object Database="[AxReports]" Schema="[dbo]" Table="[udf_MultiValueParameterHandlingString]" />

<ParameterList>

<ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(max),[@paramSalesType],0)">

<Identifier>

<ColumnReference Column="ConstExpr1040">

<ScalarOperator>

<Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="true">

<ScalarOperator>

<Identifier>

<ColumnReference Column="@paramSalesType" />

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</ColumnReference>

</Identifier>

</ScalarOperator>

</ParameterList>

</TableValuedFunction>

</RelOp>

<RelOp AvgRowSize="9" EstimateCPU="2.31271E-05" EstimateIO="0.0113121" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="23.1271" LogicalOp="Insert" NodeId="6" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="107.714">

<OutputList />

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<Update DMLRequestSort="false">

<Object Table="[#dataset]" />

<SetPredicate>

<ScalarExpressionList>

<ScalarOperator>

<MultipleAssign>

<Assign>

<ColumnReference Table="[#dataset]" Column="Journey" />

<ScalarOperator>

<Identifier>

<ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[MARJOURNEYTABLE]" Alias="[jt]" Column="JOURNEYID" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="DropSequence" />

<ScalarOperator>

<Identifier>

<ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[MARJOURNEYORDERS]" Alias="[jo]" Column="DROPSEQ" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="SalesOrder" />

<ScalarOperator>

<Identifier>

<ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[MARJOURNEYORDERS]" Alias="[jo]" Column="SALESID" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="InventSite" />

<ScalarOperator>

<Identifier>

<ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[SALESTABLE]" Alias="[st]" Column="INVENTSITEID" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="DeliveryTown" />

<ScalarOperator>

<Identifier>

<ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[MARJOURNEYORDERS]" Alias="[jo]" Column="DESTINATION" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="DeliveryPostcode" />

<ScalarOperator>

<Identifier>

<ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[MARJOURNEYORDERS]" Alias="[jo]" Column="POSTCODE" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="TransportGroup" />

<ScalarOperator>

<Identifier>

<ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[MARJOURNEYTABLE]" Alias="[jt]" Column="TRANSPORTGROUP" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="SalesName" />

<ScalarOperator>

<Identifier>

<ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[SALESTABLE]" Alias="[st]" Column="SALESNAME" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="SalesType" />

<ScalarOperator>

<Identifier>

<ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[MARREPORTENUMS]" Alias="[mre3]" Column="ENUMVALUESTR" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="OrderStatus" />

<ScalarOperator>

<Identifier>

<ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[MARAPORDERSTATUSTABLE]" Alias="[mos]" Column="DESCRIPTION" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="DeliveryType" />

<ScalarOperator>

<Identifier>

<ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[MARREPORTENUMS]" Alias="[mre1]" Column="ENUMVALUESTR" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="PotentialLatekey" />

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1031" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="PotentialLatekey-plusonehour" />

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1032" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="WagonType" />

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1033" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="EmailKey" />

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1034" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="ReqDeliveryToDateTime" />

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1028" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="EstDespatchDateTime" />

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1029" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="EstArrivalDateTime" />

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1030" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="DespatchSecondsLate" />

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1035" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="NewEstArrivalDateTime" />

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1036" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="TradingOffer" />

<ScalarOperator>

<Identifier>

<ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[SALESTABLE]" Alias="[st]" Column="MARTRADINGOFFERID" />

</Identifier>

</ScalarOperator>

</Assign>

<Assign>

<ColumnReference Table="[#dataset]" Column="SalesResponsibility" />

<ScalarOperator>

<Identifier>

<ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[emp]" Column="name" />

</Identifier>

</ScalarOperator>

</Assign>

</MultipleAssign>

</ScalarOperator>

</ScalarExpressionList>

</ScalarOperator>

</SetPredicate>

<RelOp AvgRowSize="423" EstimateCPU="2.31271E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="23.1271" LogicalOp="Compute Scalar" NodeId="7" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="107.703">

<OutputList>