sqlserver2005术资料sqlserver2005技术资料sqlserver2005技术资料sqlserver2005技术资料.doc

在SQL Server中通过SQL语句实现分页查询 1 无法连接到WMI?提供程序 3 SQLServer数据库日志清理 清除sqlserver2005日志 4 DBCC_CHECKDB_数据库或表修复 4 压缩数据库 11 约束（Constraint） 13 sql server 2005学习笔记之触发器简介 15 SQLServer 创建表实现外键约束 create table product ( id int primary key identity(1,1), name varchar(255), descr varchar(255), normalprice float, memberprice float, pdate datetime, categoryid int not null foreign key references category(id) ); /* alter table [tbname外键表] add constraint FK_外键约束名foreign key (外键列) references [tbname主键表](主键列)--所引用列必须是唯一键 on update cascade--可选级联更新 on delete cascade--可选级联删除 */ 在SQL Server中通过SQL语句实现分页查询 建立表： CREATE TABLE [TestTable] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL , [LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL , [Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO 插入数据：(2万条，用更多的数据测试会明显一些) SET IDENTITY_INSERT TestTable ON declare @i int set @i=1 while @i=20000 begin insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, FirstName_XXX,LastName_XXX,Country_XXX,Note_XXX) set @i=@i+1 end SET IDENTITY_INSERT TestTable OFF  分页方案一：(利用Not In和SELECT TOP分页) 语句形式： SELECT TOP 10 * FROM TestTable WHERE (ID NOT IN (SELECT TOP 20 id FROM TestTable ORDER BY id)) ORDER BY ID SELECT TOP 页大小 * FROM TestTable WHERE (ID NOT IN (SELECT TOP 页大小*页数 id FROM 表 ORDER BY id)) ORDER BY ID  分页方案二：(利用ID大于多少和SELECT TOP分页） 语句形式： SELECT TOP 10 * FROM TestTable WHERE (ID (SELECT MAX(id) FROM (SELECT TOP 20 id FROM TestTable ORDER BY id) AS T)) ORDER BY ID SELECT TOP 页大小 * FROM TestTable WHERE (ID (SELECT MAX(id) FROM (SELECT TOP 页大小*页数 id FROM 表 ORDER BY id) AS T)) ORDER BY ID  分页方案三：(利用SQL的游标存储过程分页) create procedure XiaoZhengGe @sqlstr nvarchar(4000), --查询字符串 @currentpage int, --第N页 @pagesize int --每页行数 as set