Index on Table Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Index on Table Column

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2011-06-22 : 20:23:41

Hello,

I am creating a table as follows:

create table dbo.Jobs
(
Id int identity not null
constraint Jobs_Id_PK primary key clustered (Id),
Date datetime not null,
Type nvarchar(40) not null,
Comment nvarchar(4000) null
) filestream_on [STORAGE]

I am searching Jobs by Date and Type.

Should I create indexes on them? How?

Thank You,

Miguel

Sachin.Nand

2937 Posts

Posted - 2011-06-23 : 01:41:53

What are the columns which you would like to return in the resultset ?

PBUH

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-23 : 10:43:16

http://msdn.microsoft.com/en-us/library/ms188783.aspx

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-06-24 : 12:42:44

I guess so. What are the most frequent queries you will run?

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2011-06-24 : 19:20:47

Sorry for the delay.

The most common queries are:
1 - Filter records by date range;
2 - Filter records by type;
3 - Filter records by date range and type

And can I add the indexes to an existing database?

Will the existing records be indexed?

Thank You,
Miguel

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-06-25 : 07:18:11

quote:
Originally posted by shapper

Sorry for the delay.

The most common queries are:
1 - Filter records by date range;
2 - Filter records by type;
3 - Filter records by date range and type

And can I add the indexes to an existing database?

Will the existing records be indexed?

Thank You,
Miguel

If it is expected to have 100s of 1000s rows in the table, then I would add index on date column and another (composite) index on type and date, in that order.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources