导入表结构：
select * into ProductCategory from AdventureWorksDW2014.dbo.DimProductCategory
select * into Product from AdventureWorksDW2014.dbo.DimProduct
开启磁盘io：
set statistics io on
select EnglishProductName,StandardCost,Color,Size,Weight from Product
where size>'M'--0.189 io:251
set statistics io off
非聚簇索引：
创建的语句：
create nonclustered index nc_product_size on product(size)
再次执行上面的查询代码（提高了三倍）：
set statistics io on
select EnglishProductName,StandardCost,Color,Size,Weight from Product
where size>'M' --0.054 io:19
set statistics io off
建立覆盖索引：
create nonclustered index nc_product_size1 on product(size) include(EnglishProductName,
StandardCost,Color,Weight)
再次执行上述语句：
set statistics io on
select EnglishProductName,StandardCost,Color,Size,Weight from Product
where size>'M' --0.003 io:2
set statistics io off
数据库会自动选择索引：
没有创建索引的情况：
set statistics io on
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Size
from product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey
where c.ProductCategoryKey=1 --0.1928
set statistics io off
创建索引：
create nonclustered index nc_productcategory_key on ProductCategory(ProductcategoryKey) include
(EnglishProductCategoryName)
在次查询：
set statistics io on
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Size
from product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey
where c.ProductCategoryKey=1 --0.1928 io:c 2 p 251
set statistics io off
IO情况：
由此可见 Product表影响比较严重 251
建立一个非聚簇索引：（做一个物理排序）
create nonclustered index nc_product_categorykey on product(productsubcategorykey) include
(englishproductname,color,size)
执行语句：
set statistics io on
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Size
from product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey
where c.ProductCategoryKey=1 --4.29 od 1497 oh 783 c 155
set statistics io off
导入三张表：
select * into Customer from AdventureWorks2014.Sales.Customer
select * into OrderHeader from AdventureWorks2014.Sales.SalesOrderHeader
select * into OrderDetail from AdventureWorks2014.Sales.SalesOrderDetail
实现一些业务：
set statistics io on
select c.CustomerID,SUM(od.LineTotal) from OrderDetail as od inner join
orderheader as oh on od.SalesOrderID=oh.SalesOrderID inner join customer as c
on oh.CustomerID =c.CustomerID group by(c.CustomerID) --4.29
set statistics io off
优化的第一步：
1.查看sql语句写法是否有问题（进行改造）
set statistics io on
select oh.CustomerID,sum(od.LineTotal) from OrderDetail as od inner join
OrderHeader as oh on od.SalesOrderID=oh.SalesOrderID group by(oh.CustomerID) --3.77 od 1497 oh 783
set statistics io off
创建索引：
create nonclustered index nc_OrderDetail_SalesOrderID on OrderDetail(SalesOrderID) include
(linetotal)
创建另外一个索引：针对group by 的列
create nonclustered index nc_OrderHeader_CustomerID on OrderHeader(CustomerID)
在次执行上述语句：
set statistics io on
select oh.CustomerID,sum(od.LineTotal) from OrderDetail as od inner join
OrderHeader as oh on od.SalesOrderID=oh.SalesOrderID group by(oh.CustomerID) --3.10 od 533 oh 783
set statistics io off
采用索引视图的方式：
create view v_Order_Total
as
select oh.CustomerID,sum(od.LineTotal) as 总额 from OrderDetail as od inner join
OrderHeader as oh on od.SalesOrderID=oh.SalesOrderID group by(oh.CustomerID)
效果差不多：
set statistics io on
select * from v_Order_Total --3.10 od 533 oh 783
set statistics io off
修改：
alter view v_Order_Total
as
select oh.CustomerID as 客户ID, sum(od.LineTotal) as 总额 from OrderDetail as od inner join
OrderHeader as oh on od.SalesOrderID=oh.SalesOrderID group by(oh.CustomerID)
对唯一列做聚集索引：
create clustered index c_vordertotal_customerid on v_order_total(客户ID)
直接运行报错：
解决方案：
在次执行：
alter view v_Order_Total
with schemabinding
as
select oh.CustomerID as 客户ID, sum(od.LineTotal) as 总额 from OrderDetail as od inner join
OrderHeader as oh on od.SalesOrderID=oh.SalesOrderID group by(oh.CustomerID)
报错：
解决方法：
alter view v_Order_Total
with schemabinding
as
select oh.CustomerID as 客户ID, sum(od.LineTotal) as 总额 from dbo.OrderDetail as od inner join
dbo.OrderHeader as oh on od.SalesOrderID=oh.SalesOrderID group by(oh.CustomerID)
在次创建：
create clustered index c_vordertotal_customerid on v_order_total(客户ID)
报错：
办法：
create unique clustered index c_vordertotal_customerid on v_order_total(客户ID)
报错：
方法：
alter view v_Order_Total
with schemabinding
as
select oh.CustomerID as 客户ID, sum(od.LineTotal) as 总额,COUNT_BIG(*) as 计数 from dbo.OrderDetail as od inner join
dbo.OrderHeader as oh on od.SalesOrderID=oh.SalesOrderID group by(oh.CustomerID)
执行创建索引：
create unique clustered index c_vordertotal_customerid on v_order_total(客户ID)
成功
set statistics io on
select * from v_Order_Total --0.09 io:92
set statistics io off
执行计划：
会自动进行值的更新，不用关心
对语句的访问会用到刚才的架构：
set statistics io on
select oh.CustomerID,sum(od.LineTotal) from OrderDetail as od inner join
OrderHeader as oh on od.SalesOrderID=oh.SalesOrderID group by(oh.CustomerID) --0.09 io:92
set statistics io off
