----------建立表----------
if exists(select * from sys.objects where object_id = object_id (N'[DBO].
[TERMINATOR]') and type in (N'U'))
DROP TABLE [DBO].[TERMINATOR]
GO
CREATE TABLE TERMINATOR
(ID INT IDENTITY (1,1) PRIMARY KEY ,NAME VARCHAR(30))
GO
-----------存储过程 分页存储----------
create procedure [dbo].[T]
@pageSize int =1,
@pageIndex int = 1,
@where varchar(8000) = ' 1=1 ',
@sortField varchar(100) = ' [id] desc '
as
set nocount on
declare @terrific varchar(8000)
set @terrific = 'select top '+convert(varchar(8),@pageSize)+'[id],[name],[time] from
[dbo].[TERMINATOR]
where [id] not in (select top '+ CONVERT(VARCHAR(8),@pageSize * (@pageIndex - 1) ) +
'[id] from [dbo].[TERMINATOR] WHERE '
+ @WHERE + ' ORDER BY ' + @SORTFIELD + ') and '+ @where + ' order by ' + @sortfield
----------print @TERMINATOR xp
exec (@terrific)
RETURN @@ERROR
-----------------------页面大小----------显示页面----------条件---------排序----------
exec T 3, 3, '1=1', 'id asc'
---------循环添加----------
declare @a int,@b int
set @a=1
while @a<1000000
begin
insert into TERMINATOR(name,TIME) values('vista',2009-1-1)
set @a=@a + 1
end
----------索引查询----------
select top 10 * from terminator
select top 100 name from TERMINATOR WITH (INDEX=TT) WHERE NAME LIKE '%'+'我们一起沉
默'+'%'
