Sql-Server
對於這種涉及索引視圖的情況，什麼是聚集索引（和其他索引）的好選擇？
為了幫助我提高某些查詢的性能，我創建了以下索引視圖：
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我為此視圖創建了聚集索引：
set deadlock_priority high CREATE UNIQUE CLUSTERED INDEX PK_VWoRDERS_ ON DBO.vwOrders (sintMarketId,strBxOrderNo,sdtmOrdCreated) GO我將主要使用視圖的方式（除其他外，包括與其他表聯合）：
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這是基表上的主鍵 - dbo.tblBOrder: strBxOrderNo varchar(20) not null
僅基於上面的這些子句，我如何定義我的聚集索引和其他索引，為什麼？
主持人：我知道這不是一個嚴格客觀的答案，但我認為問題的內容很有價值
這是我目前如何使用此視圖的範例：
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輸出範例：
生成臨時表時的執行計劃：
僅基於此查詢：
select * from DBO.vworders O where 1=1 and o.sdtmOrdCreated BETWEEN @dateFrom AND @dateto and o.sIntMarketId = @market視圖上的聚集索引應該是：
-- Same columns, just in a query-friendly order CREATE UNIQUE CLUSTERED INDEX PK_VWoRDERS_ ON DBO.vwOrders (sintMarketId,sdtmOrdCreated,strBxOrderNo);儘管要確定使用視圖（並從自動在視圖上創建統計資訊中受益），但您應該使用
NOEXPAND提示，即使在 Enterprise Edition 中：select * from DBO.vworders O WITH (NOEXPAND) where 1=1 and o.sdtmOrdCreated BETWEEN @dateFrom AND @dateto and o.sIntMarketId = @market