>In my case I have some 4000 tables. So theres no point on creating, now a clustered index on each of them.
why not? At a minimum you could replace the index on the primary key. This would greatly reduce the work that SQL Server must do to maintain the non-clustered indexes as data is manipulated. I don't see why the number of tables is important aside from the fact that it may take you a while to make the changes.
>All my tables have a unique index on a stamp column that I generate acording to an algorithm of my own. This key is 30 characters long.
It is true that the clustered key will become part of the key for all non-clustered indexes.
Is this column your primary key? It would be an really big PK.
> As I read, creating a clustered index on this column is not as eficient as it could be because they are all discrete values.
Would it help to know that all clustered keys must be unique? If you specify a clustered index on a column that is not unique, SQL Server will automatically add a "uniquifier." Clustered keys must be unique because they are used as the Row Identifier (RID) in non-clustered indexes. This takes the place of the File#:Page#:Offset that is used when there is not a clustered index.
>Furthermore, creating this clustered index, would make SQL Server use this keys in all other indexes (to point to the real data). Being such a long key, this is not good.
In general, we perfer that index keys be as small as possible. The smaller the keysize, the more keys that we can get per page, the less work (ie page IO) that SQL Server has to do.
The Clustered index does not have to go on the PK. I like to put it on the FK of detail-tables (as in Master-Detail combinations) since most rows will be retrieved by the PK of the master (it being the FK in the detail).
If you are constantly ordering data by the same column, you can put the clustered index on it and sql server might not have to perform an explicit sort since it knows that by pulling the data by the clustered index, it will already be sorted.
-Mike
