实现百万数据量毫秒级查询的存储结构

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--分页存储过程 CREATE PROCEDURE [dbo].[sp_Paging7] ( @Tables nvarchar(1000),                --表名/视图名@pageindex int = 1,                    --当前页码@PageSize int = 10,                    --每页记录数@Filter1 nvarchar(1000) = NULL,         --where过滤条件(不带where)@Filter2 nvarchar(1000) = NULL,         --where过滤条件(不带where)@Filter3 nvarchar(1000) = NULL,         --where过滤条件(不带where)@time nvarchar(100) =N'time',          --时间@Sort nvarchar(200) = NULL,            --排序字段(不带order by)@Group nvarchar(1000) = NULL ,         --Group语句(不带Group By)@Fields nvarchar(1000) = N'*'         --输出字段--@TotalCount int OUTPUT                 --总记录数) AS   DECLARE @SortTable nvarchar(100) DECLARE @SortName nvarchar(100) DECLARE @strSortColumn nvarchar(200) DECLARE @operator char(2) DECLARE @type nvarchar(100) DECLARE @prec int --设定排序语句IF @Sort IS NULL OR @Sort = ''        SET @Sort = @time IF CHARINDEX('DESC',@Sort)>0   BEGIN             SET @strSortColumn = REPLACE(@Sort, 'DESC', '')             SET @operator = '<='     END ELSE     BEGIN                    SET @strSortColumn = REPLACE(@Sort, 'ASC', '')                    SET @operator = '>='     END IF CHARINDEX('.', @strSortColumn) > 0     BEGIN             SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))    SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))     END ELSE     BEGIN             SET @SortTable = @Tables             SET @SortName = @strSortColumn  END --设置排序字段类型和精度SELECT @type=t.name, @prec=c.prec FROM sysobjects o        JOIN syscolumns c on o.id=c.id        JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName        IF CHARINDEX('char', @type) > 0       SET @type = @type + '(' + CAST(@prec AS varchar) + ')'   DECLARE @strPageSize nvarchar(50) DECLARE @strStartRow nvarchar(50) DECLARE @strFilter nvarchar(1000) DECLARE @strSimpleFilter nvarchar(1000) DECLARE @strGroup nvarchar(1000)   IF @pageindex <1        SET @pageindex = 1  SET @strPageSize = CAST(@PageSize AS nvarchar(50)) --设置开始分页记录数SET @strStartRow = CAST(((@pageindex - 1)*@PageSize + 1) AS nvarchar(50))  --筛选以及分组语句IF @Filter3 IS NOT NULL AND @Filter3 != ''     BEGIN             SET @strFilter = ' WHERE ' + @Filter1 + ' ' + ' AND ' + @Filter2 + ' AND ' + @Filter3    SET @strSimpleFilter = ' AND ' + @Filter1 + ' ' + ' AND ' + @Filter2 + ' AND ' + @Filter3END ELSE     BEGIN              SET @strFilter = ' WHERE ' + @Filter1 + ' ' + ' AND ' + @Filter2      SET @strSimpleFilter = ' AND ' + @Filter1 + ' ' + ' AND ' + @Filter2   END IF @Group IS NOT NULL AND @Group != ''     SET @strGroup = ' GROUP BY ' --计算总记录数--DECLARE @TotalCountSql nvarchar(1000)--SET @TotalCountSql=N'SELECT @TotalCount=COUNT(*)' +N' FROM ' + @Tables + @strFilter--EXEC sp_executesql @TotalCountSql,N'@TotalCount int OUTPUT',@TotalCount OUTPUT--执行查询语句   EXEC('DECLARE @SortColumn ' + @type + 'SET ROWCOUNT ' + @strStartRow + 'SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + 'SET ROWCOUNT ' + @strPageSize + 'SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '')

在网上查了资料，根据项目进行了修改，实现快速查询。