SQL Server 2008 R2 Create Index

Question

Now I am looking for your help to create an index on them.

Now this is my table structure

I need this query for maximum performance.

select PageId from tblPages where UrlChecksumCode = @UrlChecksumCode and PageUrl = @PageUrl 

Now I am very bad with indexes. I plan that when the request is executed, it will first be found that the UrlChecksumCode rows look like pageurl columns. If you also explain to me why to make such an index, I really appreciate it. Thanks.

Answer 1

one way, since your pageURL is long (nvarchar (1000), and the index can only be 900 bytes, if you are not using included columns, I created this with included columns

 create index ix_IndexName on tblPages (UrlChecksumCode) INCLUDE(PageUrl) 

or

 create index ix_IndexName on tblPages (UrlChecksumCode) INCLUDE(PageUrl, PageID) 

See also SQL Server, covering indexes

why is the nvarchar url instead of varchar ... can they be unicode? if you don't make it varchar and save 1/2 size

Answer 2

Create nonclustered indexes

  create nonclustered index indexname on tablename(columnname) 

Bounce index

  Drop Index index name on tablename 

View all indexes in the database

  select * from sys.indexes 

Answer 3

You probably need an index over (UrlChecksumCode and PageUrl) , because that is what it selects.

Although some data patterns may have better performance if you just index ( UrlChecksumCode ), because PageUrl will require a larger text index

Answer 4

I think that even we can use clusterd index, as for a quick search, for example create clusteredindex someName on tableName (columnName1, columnName2, ......) but only one clustered index that we can create for the tab