fasten the search on large table

[cuterita7]

Hi

(SQL Server 2005)I have a huge table "Articles" that contains over than one million records and I need to fasten the search.
the field of this table are:
ArticleID int
Title nvarchar(100)
Author nvarchar(100)
Filename varchar(50)
KeywordID int
ArticleDate datetime

I usually search this table by its Filename and/or KeywordID and/or ArticleDate
so i thought i should add indexes on those fields but i don't know what type of indexes should I add and how.

Could anyone give me an example?

thanks

[deambasca]

You can do this with SQL Server Studio Management, or you can execute this code:

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Articles]') AND name = N'index')
CREATE NONCLUSTERED INDEX [index] ON [dbo].[Articles] 
(
	[Filename] ASC,
	[KeywordID] ASC,
	[ArticleDate] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

If you set these columns as indexes, the search will be much faster, if you search by these fields.

[GaryMazzone]

The search will be faster with indexes yes. But if you are going to use wildcard searchs with the wildcard before the name such as anything with the word House in the filename (Where Filename Like '%House' you will not use the indexes and resort to table scanss

[dee-u]

If you are not using Stored Procedures yet then using one would help in speeding up your queries.