创建索引

CREATE TABLE [dbo].[Article](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[MsId] [int] NOT NULL,
[Title] [nvarchar](96) NOT NULL,
[TitleBak] [nvarchar](96) NOT NULL,
[Summary] [nvarchar](512) NOT NULL,
[SummaryImageUrl] [nvarchar](256) NOT NULL,
[Tag] [nvarchar](50) NOT NULL,
[ArticleChannel_Id] [int] NOT NULL,
[ArticleCategory_Id] [int] NOT NULL,
[IsApproved] [bit] NOT NULL,
[Creator_Id] [int] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL,
[ModifiedDateTime] [datetime] NOT NULL,
[ViewCount] [int] NOT NULL,
[ReplyCount] [int] NOT NULL,
[DiggCount] [int] NOT NULL,
[FavoriteCount] [int] NOT NULL,
[LastReplyUser_Id] [int] NOT NULL,
[LastReplyDateTime] [datetime] NOT NULL,
[RightType] [int] NOT NULL,
[IsDisplayContent] [bit] NOT NULL,
[IsSensitive] [bit] NOT NULL,
[Source] [int] NOT NULL,
CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

 

DECLARE @number INT
SET @number = 200000

WHILE @number > 0
BEGIN
INSERT dbo.Article
(
MsId,
Title,
TitleBak,
Summary,
SummaryImageUrl,

Tag,
ArticleChannel_Id,
ArticleCategory_Id,
IsApproved,
Creator_Id,

CreatedDateTime,
ModifiedDateTime,
ViewCount,
ReplyCount,
DiggCount,

FavoriteCount,
LastReplyUser_Id,
LastReplyDateTime,
RightType,
IsDisplayContent,

IsSensitive,
Source
)
VALUES
(
@number,
'Title'+cast(@number AS VARCHAR(20)),
'TitleBak'+cast(@number AS VARCHAR(20)),
'Summary'+cast(@number AS VARCHAR(20)),
'SummaryImageUrl'+cast(@number AS VARCHAR(20)),

'Tag'+cast(@number AS VARCHAR(20)),
1,
2,
0,
@number,

GETDATE(),
GETDATE(),
100,
29,
123,

12,
@number,
GETDATE(),
1,
0,

0,
2

)
SET @number=@number-1
END

select * from Article where MsId=200 and ArticleCategory_Id=2

CREATE INDEX Ix_article_creatorid ON Article(Creator_Id) --创建索引
drop index Article.Ix_article_creatorid --删除索引

--查找所有索引
SELECT 'dbcc showcontig (' + CONVERT(VARCHAR(20), i.id) + ',' + -- table id
CONVERT(VARCHAR(20), i.indid) + ')--' + --index id
OBJECT_NAME(i.id) + '.' + -- table name
i.name--index name
FROM sysobjects o
INNER JOIN sysindexes i ON ( o.id = i.id )
WHERE o.type = 'U'
AND i.indid < 2
AND i.id = OBJECT_ID(o.name)
ORDER BY OBJECT_NAME(i.id), i.indid