提高速度.sql

本代码简单明了

use pubs
create table Test2
(
    bookid int,
    bookName varchar(20),
    bookPrice int,
    bookProductName varchar(20)
)


----
set nocount on  --提高执行时间30%--50%
declare @bid int
declare @bName varchar(20)
declare @bPrice int
declare @bpName varchar(20)
set @bid = 1

while @bid < 10000
begin
    set @bid = @bid + 1
    set @bName = 'ACCP' + convert(varchar(10),@bid)
    set @bPrice = @bid
    set @bpName = 'APTECH' + convert(varchar(10),@bid)
    insert into Test2 values(@bid,@bName,@bPrice,@bpName)
end

--提高查询速度
--创建索引


create CLUSTERED index TestID
on test2(bookid)

create NONCLUSTERED index TestbpName
on test2(bookProductName)

select * from test2 where bookid = 8000

select * from test2 where bookProductName like 'APTECH8000'