SQL Server 2008 R2 Create index

Question

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

Now this is my table structure

This the query I need index for maximum performance.

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

Now I am very bad with indexes. I plan like that when the query is executing it will find first that UrlChecksumCode rows then look pageurl columns. If you also explain me why to make such index I really appreciate that. Thank you.

Answer 1

one way, since your pageURL is long(nvarchar(1000) and an index can only be 900 bytes if you don't use included columns, I have created this index 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 URL nvarchar instead of varchar...can they be unicode? if not make it varchar and save 1/2 the size

Answer 2

Create Nonclustered Indexes

       create nonclustered index indexname on tablename(columnname)

Drop Index

       Drop Index index name on tablename

See all Indexes in a database

       select * from sys.indexes