我用两个表共建了一个索引视图，并在视图上建了索引，效率上却怎么也引用不到索引。请各位大侠帮助。谢谢。
索引视图和索引是：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
alter view [dbo].[V_SeaProductInfo](ID,C_Picture,C_Name,C_Item_No,C_GrossWeight,C_Weight,C_BoxSize,N_UserStatus,N_Order) WITH SCHEMABINDING
as
select a.ID,a.C_Picture,a.C_Name,a.C_Item_No,a.C_GrossWeight,a.C_Weight,a.C_BoxSize,b.N_Status,b.N_Status*10000000+a.ID N_Order
from [dbo].T_ProductInfo a ,[dbo].T_Corporation b
where a.UserID=b.ID and a.N_Status=2 and a.N_IsVipProduct=0
GO
CREATE UNIQUE CLUSTERED INDEX IX_SeaProductInfo_Order ON [dbo].V_SeaProductInfo(N_Order desc)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
然后查询其效率：
SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT TOP 15 * from V_SeaProductInfo order by N_Order desc
GO
SET STATISTICS PROFILE OFF
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
引用不了索引：
SELECT TOP 15 * from V_SeaProductInfo order by N_Order desc 46.79288
|--Top(TOP EXPRESSION:((15))) 46.79288
|--Parallelism(Gather Streams, ORDER BY:([Expr1004] DESC)) 46.79288
|--Sort(TOP 15, ORDER BY:([Expr1004] DESC)) 46.76412
|--Compute Scalar(DEFINE:([Expr1004]=[Expr1005]+[ToybabaDB].[dbo].[T_ProductInfo].[ID] as [a].[ID])) 29.70327
|--Hash Match(Inner Join, HASH:([b].[ID])=([a].[UserID])) 29.6833
|--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[ToybabaDB].[dbo].[T_Corporation].[N_Status] as [b].[N_Status],0)*(10000000))) 2.109637
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([b].[ID])) 2.108403
| |--Clustered Index Scan(OBJECT:([ToybabaDB].[dbo].[T_Corporation].[PK_T_CORPORATION] AS [b])) 2.042695
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([a].[UserID])) 26.17119
|--Clustered Index Scan(OBJECT:([ToybabaDB].[dbo].[T_ProductInfo].[PK_T_PRODUCTINFO] AS [a]), WHERE:([ToybabaDB].[dbo].[T_ProductInfo].[N_Status] as [a].[N_Status]=(2) AND [ToybabaDB].[dbo].[T_ProductInfo].[N_IsVipProduct] as [a].[N_IsVipProduct]=(0))) 21.51876
------解决方案--------------------
试试这个,应能用上视图索引,
- SQL code
select a.ID,a.C_Picture,a.C_Name,a.C_Item_No,a.C_GrossWeight,a.C_Weight,a.C_BoxSize,b.N_Status,b.N_Status*10000000+a.ID N_Orderfrom [dbo].T_ProductInfo a,[dbo].T_Corporation b where a.UserID=b.ID and a.N_Status=2 and a.N_IsVipProduct=0order by b.N_Status*10000000+a.ID desc
------解决方案--------------------
Clustered Index Scan 重点优化这两个地方
a.N_Status=2 and a.N_IsVipProduct=0
涉及到这连个字段的索引 如果离散度不够 请去掉加在他们上面的索引
------解决方案--------------------
1、要确认是否是企业版或更高版本。
否则，创建是可以的，如果不是显式的写明调用视图的索引（有时写明也不一定调用，可以使用查询提示noexpand），查询优化器会忽略视图索引。（MS的营销策略啊）
2、查询优化器是成本计算的结果，可能不走索引视图来的更快。（这个得看具体情况了，LZ可以先强制走视图索引，再对比一下看看）
3、测试了一下，这边是走视图索引的。LZ参考
- SQL code
SELECT COUNT(1) FROM dbo.[packmain]--3069244CREATE VIEW V_scv(serialid,model,quantity)WITH SCHEMABINDING ASSELECT '10000' +CONVERT(VARCHAR,SerialID),model,quantityFROM dbo.[packmain]CREATE UNIQUE CLUSTERED INDEX IX_SCV ON dbo.V_scv(serialid desc)SET STATISTICS PROFILE ON SET STATISTICS IO ON SET STATISTICS TIME ON SELECT TOP 25 * FROM dbo.V_scv ORDER BY serialid ASC/*SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。(25 行受影响)表 'V_scv'。扫描计数 1，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。(3 行受影响) SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。*/ /*SELECT TOP 25 * FROM dbo.V_scv ORDER BY serialid asc |--Top(TOP EXPRESSION:((25))) |--Clustered Index Scan(OBJECT:([Test_HL].[dbo].[V_scv].[IX_SCV]), ORDERED BACKWARD)*/