Best Practice - Index vs Filter

[jsquizz]

It's been a long time since I've done report development, but Im trying to come up with some standards. Also, I am not a DBA by any stretch of the imagination

Wondering about AG fields - Here are my below assumptions I'd assume are correct.

1) Is there any reason why I would not always use an index as field type?
2) Referring to question 1 - Would I only use an index on fields that are searched commonly, AKA - Account, SSN, Name, ETC.
3) Dates- Should they be filters?
4) "Dropdown Boxes" - should they be filters?

Wondering how everyone does it and if i can get some tips. In the mean time, I'll be hitting up mr google. Thanks

[Justin Derrick]

1)  Storage space.  Indexes take up a lot of space.
2)  Only the MOST UNIQUE ("high cardinality") fields like customer number or policy number.
3)  Usually.
4)  Probably.

Indexes in the CMOD database work the same was as the index in the back of a book.  Indexing the words 'the' 'and' & 'a' are pointless, because it's not particularly meaningful or useful - you only put words in the index that help you find what you're looking for.

I've had a few questions about this lately.  I'll write something up.

-JD.

[jsquizz]

Space isn't so much a concern I dont think but who knows.

thank you for this!

[Justin Derrick]

Space is always a concern - if not now, in the not-too-distant future.  :)

-JD.

[sisusteve]

You really need to think about query performance. 
If there are no indexes, then the queries have to scan every row in the database table, which can take a really long time on high-volume data.
Having indexes on fields such as cust_num, subscriber_number, etc. and making them required will improve performance dramatically.