如何创建该表的索引? [英] How to create index of this table?

问题描述

USE [HJGINFODB]
GO
/****** 
object:Table [dbo].[TB_FB_EX]  
scritp:09/19/2010 21:16:39 
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TB_FB_EX](
    [ID] [bigint] NULL,
    [CITY_ID] [int] NOT NULL,
    [QU_ID] [int] NOT NULL,
    [TYPE_ID] [int] NOT NULL,
    [TYPE_PID] [int] NOT NULL,
    [STITLE] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [JG] [int] NOT NULL,
    [INSTIME] [datetime] NULL CONSTRAINT [DF_TB_FB_EX_INSTIME]  DEFAULT (getdate())
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF





USE [HJGINFODB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[f_div_TB_FB_EX]
@CITY_ID int,---------city of ID,default 0
@QU_ID int,-----------area ID,default 0
@TYPE_PID int,--------main type ID,default 0
@TYPE_ID int,---------small type ID,default 0

@PageIndex Int,-------Page Index 
@PageSize Int,--------Page size
@ReturnRecordCount int out---row count
as
/*

declare @ReturnRecordCount int
exec [f_div_TB_FB_EX] 264,0,1,0,1,10,@ReturnRecordCount

declare @ReturnRecordCount int
exec [f_div_TB_FB_EX] 264,571003,2,0,0,10,@ReturnRecordCount

declare @ReturnRecordCount int
exec [f_div_TB_FB_EX] 264,0,0,30,0,10,@ReturnRecordCount

declare @ReturnRecordCount int
exec [f_div_TB_FB_EX] 264,571008,0,30,0,10,@ReturnRecordCount

*/
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

SET @PageLowerBound = @PageSize * @PageIndex+1
SET @PageUpperBound = @PageLowerBound + @PageSize-1

--http://localhost/hangzhou/gongzuo/  一级
if @CITY_ID<>0 and @TYPE_PID<>0 and @QU_ID=0
    begin
    select @ReturnRecordCount = count(*)  from TB_FB_EX  with (nolock)
    where CITY_ID = @CITY_ID  and TYPE_PID=@TYPE_PID

    SELECT *
     FROM     (select ID,CITY_ID,QU_ID,
    TYPE_ID,TYPE_PID,STITLE,JG,INSTIME ,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
    FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID and TYPE_PID=@TYPE_PID) AS NormRen
    where  SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
    end
--http://localhost/hangzhou/jianzhiduangong/  二级
else if @CITY_ID<>0 and @TYPE_ID<>0  and @QU_ID=0
    begin
    select @ReturnRecordCount = count(*)  from TB_FB_EX  with (nolock)
    where CITY_ID = @CITY_ID  and TYPE_ID=@TYPE_ID

    SELECT *
     FROM     (select ID,CITY_ID,QU_ID,
    TYPE_ID,TYPE_PID,STITLE,JG,INSTIME,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
    FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID and TYPE_ID=@TYPE_ID) AS NormRen
    where  SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
    end
else if @CITY_ID<>0 and @TYPE_PID<>0  and @QU_ID<>0
--http://localhost/hangzhou/gongzuo/571004/
begin
    select @ReturnRecordCount = count(*)  from TB_FB_EX  with (nolock)
    where CITY_ID = @CITY_ID  and QU_ID=@QU_ID and TYPE_PID=@TYPE_PID

    SELECT *
     FROM     (select ID,CITY_ID,QU_ID,
    TYPE_ID,TYPE_PID,STITLE,JG,INSTIME,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
    FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID and QU_ID=@QU_ID and TYPE_PID=@TYPE_PID ) AS NormRen
    where  SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
end

else if @CITY_ID<>0 and @TYPE_ID<>0  and @QU_ID<>0
--http://localhost/hangzhou/dianyuan/571006/
begin
    select @ReturnRecordCount = count(*)  from TB_FB_EX with (nolock)
    where CITY_ID = @CITY_ID  and TYPE_ID=@TYPE_ID and QU_ID=@QU_ID

    SELECT *
     FROM     (select ID,CITY_ID,QU_ID,
    TYPE_ID,TYPE_PID,STITLE,JG,INSTIME,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
    FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID and TYPE_ID=@TYPE_ID and QU_ID=@QU_ID) AS NormRen
    where  SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
end
else if @CITY_ID<>0 and @TYPE_ID=0  and @QU_ID=0 and @TYPE_PID=0
begin
    select @ReturnRecordCount = count(*)  from TB_FB_EX with (nolock)
    where CITY_ID = @CITY_ID

    SELECT *
     FROM     (select ID,CITY_ID,QU_ID,
    TYPE_ID,TYPE_PID,STITLE,JG,INSTIME,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
    FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID) AS NormRen
    where  SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
end

推荐答案

我要为几列建立索引，您想在该列建立索引在where子句的链接或条件中使用.

话虽这么说，您不需要太多的索引.乍一看，我认为至少需要对City_ID和Type_PID进行索引.请注意，我并没有对您的程序进行快速概述.

I would index several columns, you want to index a column when it is used in a link or criteria in the where clause.

That being said you do not want too many indexes. At a glance I imagine that at least City_ID and Type_PID need to be indexed. Note I did not decipher your program just a quick overview.

这篇关于如何创建该表的索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！