--Demo 1:
use northwind
go
set statistics IO on
go
select count(*) from northwind.dbo.employees
go
set statistics IO off
go

use northwind
go
exec sp_spaceused employees
go

--Demo 2:
set statistics time on
go
select count(*) from northwind.dbo.employees
go
set statistics time off
go

--Demo 3:
set showplan_text on
go
select count(*) from northwind.dbo.employees
go
set showplan_text off
go

--Demo 4:
set nocount on
go
select count(*) from northwind.dbo.employees
go
set nocount off
go

--Demo 5查询单条sql语句的执行时间:
declare @start_time datetime
select @start_time=getdate()
select * from northwind.dbo.employees
select '查询语句的执行时间(毫秒)'=datediff(ms,@start_time,getdate())

--Demo 6查询成批的sql语句的执行时间:
create table #save_time(start_time datetime not null)
insert #save_time values(getdate())
go
select * from employees
go
select * from orders
go
select '查询语句的执行时间(毫秒)'=datediff(ms,start_time,getdate())
from #save_time
drop table #save_time
go

--Demo 7返回语句的执行计划内容:
set showplan_all on
go
select * from pubs.dbo.authors
go
set showplan_all off
go

--Demo 8从执行计划判断是否需要优化SQL:
/**//**//**//*SEEK操作*/
set showplan_all on
go
select * from pubs.dbo.sales where stor_id>='7131'
go
set showplan_all off
go
/**//**//**//*SCAN操作*/
set showplan_all on
go
select * from pubs.dbo.sales where ord_date is not null
go
set showplan_all off
go

--Demo 9连接查询VS子查询:
/**//**//**//*子查询*/
set statistics io on
go
select au_fname,au_lname from pubs.dbo.authors where au_id in
(select au_id from pubs.dbo.titleauthor)
set statistics io off
go

/**//**//**//*连接查询*/
set statistics io on
go
select distinct au_fname,au_lname from pubs.dbo.authors as a inner join
pubs.dbo.titleauthor as t on a.au_id=t.au_id
go
set statistics io off
go

--Demo 10智能优化:
select p1.productname from northwind.dbo.products as p1 inner join
northwind.dbo.products as p2 on (p1.unitprice=p2.unitprice)
where p2.productname like 'Alice%'