what would be a good choice of clustered index (and other indexes) for this situation involving a indexed view?

Question

To help me improve the performance of some queries I have created the following indexed view:

CREATE VIEW DBO.vwOrders
WITH SCHEMABINDING
AS 

/*-----------------------------------------------------------------------*\
   view for boccs2 reports
   29-sep-2015
\*-----------------------------------------------------------------------*/
SELECT 

strBxOrderNo,
sintMarketId,
sdtmOrdCreated,
strCurrencyCode,
sintOrderStatusID

FROM dbo.tblBOrder o 
WHERE 1=1
AND ( NOT o.sintOrderStatusId IN ( 9, 10, 11, 12, 13, 14 )
)
GO

I have created the clustered index for this view:

    set deadlock_priority high
    CREATE UNIQUE CLUSTERED INDEX PK_VWoRDERS_ 
ON DBO.vwOrders (sintMarketId,strBxOrderNo,sdtmOrdCreated)
    GO

the way I will mostly use the view ( amongst other things, including joint with other tables):

set transaction isolation level read uncommitted
set nocount on
set deadlock_priority high


--===============================================
-- set up the parameters
--===============================================

declare @market smallint 
declare @dateFrom smalldatetime
declare @dateto smalldatetime


select @dateFrom = '01-Sep-2015'
      ,@dateto   = '28-Sep-2015'
      ,@market   = 1


-- for testing the view
select * from DBO.vworders O
            where 1=1
            AND o.sdtmOrdCreated BETWEEN @dateFrom AND @dateto
            AND o.sIntMarketId = @market

this is the primary key on the base table - dbo.tblBOrder: strBxOrderNo varchar(20) not null

Based ONLY on these clauses above, how could I define my clustered index, and other indexes and why?

Moderators: I am aware this is not a strictly objective answer, but I consider the contents of the question valuable

here is an example of how I am currently using this view:

USE Bocss2

set transaction isolation level read uncommitted
set nocount on
set deadlock_priority high


--=================================================================
-- set up the parameters
--=================================================================

declare @market smallint 
declare @dateFrom smalldatetime
declare @dateto smalldatetime


select @dateFrom = '01-Sep-2015'
      ,@dateto   = '28-Sep-2015'
      ,@market   = 1


-- for testing the view
--select * from DBO.vworders O
--          where 1=1
--          AND o.sdtmOrdCreated BETWEEN @dateFrom AND @dateto
--          AND o.sIntMarketId = @market



--=================================================================
-- drop the tables if they already exist
--=================================================================

BEGIN TRY
  DROP TABLE #AllOrdersPaymentType 
END TRY
BEGIN CATCH
END CATCH

BEGIN TRY
  DROP TABLE #usGiftCards 
END TRY
BEGIN CATCH
END CATCH

--=================================================================
-- create the temp tables
--=================================================================
CREATE  TABLE #AllOrdersPaymentType   (

                                            strBxOrderNo VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED,
                                            sintMarketId smallint not null,
                                            strPaymentTypeDescr VARCHAR(50) NOT NULL,
                                            sdtmOrdCreated smalldatetime NOT NULL,
                                            strCurrencyCode varchar(3) not null,
                                            sintOrderStatusID smallint not null


                                       )

CREATE TABLE #usGiftCards (
                                           strItemNo varchar(8) NOT NULL PRIMARY KEY CLUSTERED,
                                           strTier3 varchar(20) not null,

                          )


--=================================================================
-- load the table #usGiftCards
--=================================================================
insert into #usGiftCards
SELECT uGC.strTier3
      ,uGC.strItemNo
FROM dbo.tblBGiftVoucherItem uGC
WHERE sintMarketID = 2
AND strType = 'CARD'
AND strTier1 LIKE 'GG%'

--=================================================================
-- load the table #usGiftCards
-- Add our payment type information
--=================================================================
            ;WITH R2 AS(

            select
            op.strBxOrderNo,

            RO=ROW_NUMBER() OVER (PARTITION BY op.strBxOrderNo ORDER BY (SELECT NULL)),
            intNunmPayMethods= COUNT (*) OVER (PARTITION BY op.strBxOrderNo),
            MINsintPaymentTypeID = MIN (op.sintPaymentTypeID ) OVER (PARTITION BY op.strBxOrderNo)


            ,OP.sintPaymentTypeID 
            ,optd.strPaymentTypeDescr
            ,o.sintMarketId

            ,sdtmOrdCreated
            ,o.strCurrencyCode
            ,o.sintOrderStatusID

            FROM
                dbo.tblBOrderPayment op --with (index(IX_tblBOrderPayment))
            inner join DBO.vworders O WITH (NOEXPAND)  on op.strBxOrderNo = o.strBxOrderNo

            inner join dbo.tblBOrderPaymentTypeDescr optd 
                    on op.sintPaymentTypeID = optd.sintPaymentTypeID 
                   and optd.sintLanguageID = 1

            where 1=1
            AND o.sdtmOrdCreated BETWEEN @dateFrom AND @dateto
            AND o.sIntMarketId = @market
            AND OP.decPaymentAmount > 0 --Exclude the zero payment record...(sometimes created by the system to track the CC details) 

            )
            ,R3 AS(

            SELECT 
             strBxOrderNo
             ,sintPaymentTypeID
            ,intNunmPayMethods
            ,strPaymentTypeDescr 
            ,sintMarketId
            ,sintOrderStatusID
            ,sdtmOrdCreated
            ,strCurrencyCode


            ,sintPaymentTypeID2 = MIN( CASE 
                                            WHEN intNunmPayMethods = 1 THEN sintPaymentTypeID 
                                            WHEN intNunmPayMethods > 1 AND sintPaymentTypeID IN (3,4,5) THEN 99
                                        ELSE sintPaymentTypeID
                                      end
                                     ) OVER (PARTITION BY strBxOrderNo)

            FROM R2
            where r2.ro = 1
           )

            INSERT 
            INTO #AllOrdersPaymentType
            SELECT 
             strBxOrderNo
             ,sintMarketId

            --,sintPaymentTypeID
            --,intNunmPayMethods
            --,strPaymentTypeDescr 
            --,sintPaymentTypeID2

            ,strPaymentTypeDescr= case intNunmPayMethods when 1 then strPaymentTypeDescr 
                                        else (
                                                case 
                                                    WHEN sintPaymentTypeID = 1 then 'Mixed (CC)'
                                                    when sintPaymentTypeID = 6 then 'Mixed (OI)'
                                                    when sintPaymentTypeID = 8 then 'Mixed (PayPal)'
                                                    when sintPaymentTypeID = 9 then 'Mixed (IDEAL)'
                                                    else 'Mixed (Other)' 
                                                end) 
                                 end
            ,sdtmOrdCreated
            ,strCurrencyCode
            ,sintOrderStatusID

             FROM R3

--select * from #AllOrdersPaymentType 
--=================================================================
-- Get redeemed vouchers from tblBOrderPaymentGiftVoucher
--=================================================================
SELECT m.strMarketDescrShort AS "Market",
CONVERT(VARCHAR(11), pt.sdtmOrdCreated, 103) AS "Order Date" ,
pt.strBxOrderNo AS "Bocss Ord No" ,
oSD.strOrderStatus AS "Order Status" ,
pt.strCurrencyCode AS "Currency" ,
strPaymentTypeDescr AS "Payment Type",
gVT.strVoucherId AS "Voucher ID" ,
gV.strIssuedBxOrderNo AS "Issued Ord No" ,
0 AS "GV Issued" ,
CASE WHEN gV.strIssuedBxOrderNo NOT LIKE 'P%' THEN gVV.lngValue ELSE 0 END AS "GV Redeemed" ,
CASE WHEN gV.strIssuedBxOrderNo LIKE 'P%' THEN gVV.lngValue ELSE 0 END AS "Promo GV Redeemed"
FROM  #AllOrdersPaymentType pt 
JOIN dbo.tblBMarket m ON pt.sintMarketID = m.sintMarketID
JOIN dbo.tblBOrderStatusDescr oSD ON pt.sintOrderStatusID = oSD.sintOrderStatusID
JOIN dbo.tblBOrderPayment oP ON pt.strBxOrderNo = oP.strBxOrderNo
LEFT JOIN dbo.tblBOrderPaymentGiftVoucher oPGV ON oP.lngPaymentID = oPGV.lngPaymentID
LEFT JOIN dbo.tblBGiftVoucher gV ON oPGV.strVoucherNumber = gV.strVoucherNumber
LEFT JOIN dbo.tblBGiftVoucherTranslation gVT ON gV.strVoucherNumber = gVT.strVoucherNumber
LEFT JOIN dbo.tblBGiftVoucherValue gVV ON gVT.strVoucherNumber = gVV.strVoucherNumber
WHERE 1=1
AND oSD.sintLanguageID = 1
AND oP.sintPaymentTypeID = 3

UNION 

--=================================================================
-- Get redeemed vouchers from tblBGiftVoucher
--=================================================================
SELECT m.strMarketDescrShort AS "Market",
CONVERT(VARCHAR(11), pt.sdtmOrdCreated, 103) AS "Order Date" ,
pt.strBxOrderNo AS "Bocss Ord No" ,
oSD.strOrderStatus AS "Order Status" ,
pt.strCurrencyCode AS "Currency" ,
strPaymentTypeDescr AS "Payment Type",
gVT.strVoucherId AS "Voucher ID" ,
gV.strIssuedBxOrderNo AS "Issued Ord No" ,
0 AS "GV Issued" ,
CASE WHEN gV.strIssuedBxOrderNo NOT LIKE 'P%' THEN gVV.lngValue ELSE 0 END AS "Non-Promo GV Redeemed" ,
CASE WHEN gV.strIssuedBxOrderNo LIKE 'P%' THEN gVV.lngValue ELSE 0 END AS "Promo GV Redeemed"
FROM dbo.tblBGiftVoucher gV
INNER JOIN #AllOrdersPaymentType pt  ON gV.strReedemedBxOrderNo = pt.strBxOrderNo
INNER JOIN dbo.tblBMarket m ON pt.sintMarketID = m.sintMarketID
INNER JOIN dbo.tblBOrderStatusDescr oSD ON pt.sintOrderStatusID = oSD.sintOrderStatusID
INNER JOIN dbo.tblBGiftVoucherTranslation gVT ON gV.strVoucherNumber = gVT.strVoucherNumber
INNER JOIN dbo.tblBGiftVoucherValue gVV ON gVT.strVoucherNumber = gVV.strVoucherNumber

UNION 
--=================================================================
-- Get issued vouchers from tblBGiftVoucher
--=================================================================
SELECT m.strMarketDescrShort AS "Market",
CONVERT(VARCHAR(11), pt.sdtmOrdCreated, 103) AS "Order Date" ,
pt.strBxOrderNo AS "Bocss Ord No" ,
oSD.strOrderStatus AS "Order Status" ,
pt.strCurrencyCode AS "Currency" ,
strPaymentTypeDescr AS "Payment Type",
gVT.strVoucherId AS "Voucher ID" ,
gV.strIssuedBxOrderNo AS "Issued Ord No" ,
gVV.lngValue AS "GV Issued" ,
0 AS "GV Redeemed" ,
0 AS "Promo GV Redeemed" 
FROM dbo.tblBGiftVoucher gV
INNER JOIN #AllOrdersPaymentType pt ON gV.strIssuedBxOrderNo = pt.strBxOrderNo
INNER JOIN dbo.tblBMarket m ON pt.sintMarketID = m.sintMarketID
INNER JOIN dbo.tblBOrderStatusDescr oSD ON pt.sintOrderStatusID = oSD.sintOrderStatusID
INNER JOIN dbo.tblBGiftVoucherTranslation gVT ON gV.strVoucherNumber = gVT.strVoucherNumber
INNER JOIN dbo.tblBGiftVoucherValue gVV ON gVT.strVoucherNumber = gVV.strVoucherNumber
WHERE 1=1
AND oSD.sintLanguageID = 1




UNION 

--=================================================================
-- Get issued vouchers for US by looking at order items
-- Does not return voucher numbers, returns seq number to make rows unique
--=================================================================
SELECT m.strMarketDescrShort AS "Market",
CONVERT(VARCHAR(11), pt.sdtmOrdCreated, 103) AS "Order Date" ,
pt.strBxOrderNo AS "Bocss Ord No" ,
oSD.strOrderStatus AS "Order Status" ,
pt.strCurrencyCode AS "Currency" ,
strPaymentTypeDescr AS "Payment Type",
CONVERT(VARCHAR, oI.sintOrderSeqNo) AS "Voucher ID" , -- Add sequence number to distinguish between multiple GV of the same value
pt.strBxOrderNo AS "Issued Ord No",
uGC.strTier3 AS "GV Issued" ,
0 AS "Non-Promo GV Redeemed" ,
0 AS "Promo GV Redeemed"
FROM #AllOrdersPaymentType pt
INNER JOIN dbo.tblBOrderItem oI ON pt.strBxOrderNo = oI.strBxOrderNo
INNER JOIN dbo.tblBMarket m ON pt.sintMarketID = m.sintMarketID
INNER JOIN dbo.tblBOrderStatusDescr oSD ON pt.sintOrderStatusID = oSD.sintOrderStatusID
INNER JOIN dbo.tblProdName pN ON oI.strItemNo = pN.strItemNo
INNER JOIN #usGiftCards uGC ON pN.strItemNo = uGC.strItemNo
WHERE oSD.sintLanguageID = 1
AND oI.sintOrderItemStatusId NOT IN (5, 9, 10)
ORDER BY pt.strBxOrderNo

Example of the output:

Execution plan when generating the temp table:

Answer 1

Based solely on this query:

select * 
from DBO.vworders O
where 1=1
and o.sdtmOrdCreated BETWEEN @dateFrom AND @dateto
and o.sIntMarketId = @market

The clustered index on the view should be:

-- Same columns, just in a query-friendly order
CREATE UNIQUE CLUSTERED INDEX PK_VWoRDERS_ 
ON DBO.vwOrders (sintMarketId,sdtmOrdCreated,strBxOrderNo);

Though to be certain of using the view (and to benefit from automatically creating statistics on the view), you should use the NOEXPAND hint, even in Enterprise Edition:

select * 
from DBO.vworders O WITH (NOEXPAND)
where 1=1
and o.sdtmOrdCreated BETWEEN @dateFrom AND @dateto
and o.sIntMarketId = @market