Question on index for performance

[Former Member]

Suppose I have a column DateCreated (datetime) in a table, for this column, actually each row has different value.

Question: if index on this column improve performance? Or no need to create index for this kind of column?

[former_member188958 · Accepted solution]

Depends on the queries being run.  The index can't help if this column isn't used to join tables or in the WHERE clause.   Nor will it help if there is a better index on the table for such queries.   But having it will slow the performance of inserts, updates, and deletes as ASE now has to also maintain the index entries.

[Former Member · Accepted solution]

Thanks. Bret. Because each row has different value, index will have same data has original data?

Or only index can apply some search algorithm like B-tree? No index always cause full table scan?

[former_member188958 · Accepted solution]

The leaf level of the index will have the same full key values as the original data.  Higher levels of the index may contain values that are truncated (called suffix-compression) so only as much of the value is stored as is needed to navigate the tree.

If there are no indexes then you will always get a table scan.