design Index problem

[Enorme Vigenti]

Hi all,
I have a very large table with many columns: dateTime type, nvarchar
type and integer field type.
A program exec many type of query with where clause.
Data field is always in where clause, but some other field is present
too. Sometimes integer field, sometimes nvarchar field.
Now I must create index for the query!
For choose index field what i can do?
I must create only index on a datetime field or every combination in
every type of query?
In the second case I must create very much index! But this is very
dispendious for update/insert/delete operation on the table!!!
Some ideas?
thnx

[Erland Sommarskog]

Re: design Index problem

Enorme Vigenti (LSimon5@libero .it) writes:

I have a very large table with many columns: dateTime type, nvarchar
type and integer field type.
A program exec many type of query with where clause.
Data field is always in where clause, but some other field is present
too. Sometimes integer field, sometimes nvarchar field.
Now I must create index for the query!
For choose index field what i can do?
I must create only index on a datetime field or every combination in
every type of query?
In the second case I must create very much index! But this is very
dispendious for update/insert/delete operation on the table!!!

How selective is the datetime column? If all queries are for a single
day, maybe an index on that column is sufficient, preferrably a clustered
index.

But if queries can be for longer periods of time, that may address too many
rows, and in such case you will need to add more indexes. How these indexes
should be designed depends on the queries. If a query can be on account
number and a date interval, it's probably better to have the account number
first in that index.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Jack Vamvas]

Re: design Index problem

If you could post an example Query ?

--

Jack Vamvas
_______________ _______________ _____
Search IT jobs from multiple sources- http://www.ITjobfeed.com




"Enorme Vigenti" <LSimon5@libero .itwrote in message
news:0kZSi.1510 09$U01.1108206@ twister1.libero .it...

Hi all,
I have a very large table with many columns: dateTime type, nvarchar type
and integer field type.
A program exec many type of query with where clause.
Data field is always in where clause, but some other field is present too.
Sometimes integer field, sometimes nvarchar field.
Now I must create index for the query!
For choose index field what i can do?
I must create only index on a datetime field or every combination in every
type of query?
In the second case I must create very much index! But this is very
dispendious for update/insert/delete operation on the table!!!
Some ideas?
thnx