我的测试sql语句如下:
set statistics time on;
set statistics io on;
use Northwind;
go
drop index dbo.MyOrders.oid;
create clustered index oid on dbo.MyOrders(OrderID);
drop index dbo.MyOrderDetails.pkoid;
create clustered index pkoid on dbo.MyOrderDetails(OrderID,ProductID);
select o.OrderID,o.OrderDate,od.ProductID,od.Quantity
from dbo.MyOrders as o
join dbo.MyOrderDetails as od
on o.OrderID=od.OrderID;
set statistics time on;
set statistics io on;
use Northwind;
go
drop index dbo.MyOrders.oid;
create clustered index oid on dbo.MyOrders(OrderID);
drop index dbo.MyOrderDetails.pkoid;
create clustered index pkoid on dbo.MyOrderDetails(OrderID,ProductID);
select o.OrderID,o.OrderDate,od.ProductID,od.Quantity
from dbo.MyOrders as o
join dbo.MyOrderDetails as od
on o.OrderID=od.OrderID;
表是复制过来的,除了索引是我自己添加的以外,其他的都是直接用select into 复制来的
那该如何根据hash join的信息来创建索引呢?
select o.OrderID,o.OrderDate,od.ProductID,od.Quantity
from dbo.MyOrders as o
inner join dbo.MyOrderDetails as od on o.OrderID=od.OrderID;
order by o.OrderID
关键字 'order' 附近有语法错误。
create clustered index oid on dbo.MyOrders(OrderID);
drop index dbo.MyOrderDetails.pkoid;
create clustered index pkoid on dbo.MyOrderDetails(OrderID);
select o.OrderID,o.OrderDate,od.ProductID,od.Quantity
from dbo.MyOrders as o
inner join dbo.MyOrderDetails as od on o.OrderID=od.OrderID
order by o.OrderID
我是SqlServer2012的select * from [Purchasing].[PurchaseOrderHeader]
聚集索引在PurchaseOrderIDselect * from [Purchasing].[PurchaseOrderDetail]
聚集索引在PurchaseOrderDetailID