logic sequence :

row1:2008-01-01 12:03:24

row2:2008-01-02 12:03:24

row3:2008-01-01 12:03:24

Will SQL SERVER save the data like that(physical):

row1:2008-01-01 12:03:24

row3:2008-01-01 12:03:24

row2:2008-01-02 12:03:24

when you create clustered  on a column which has duplicate rows SQL Server has to "uniquify" the rows but it will take addional space and time...

Check the following...

http://www.sqlskills.com/blogs/kimberly/2007/02/02/TheClusteredIndexDebateContinues.aspx

How SQL Server stores the data physically is meaningless.  It might be contigous or not. However in any case the page chain will be maintained and the page chain will look like your example. A smalldatetime attribute alone probably makes up only for a poor clustered index, imho

SQL Server can use index on DateTimeField or on any other filed based on UniqueNumberWithIndex. If any of those is determined (based on the values and the statistics) very selective, then that will be used. If both are less selective, then SQL Server can join the indexesin run-time and used the combination of those two indexes (this is called index intersection).Checking the query plan will tell you which is always a best one to check, and also depending on the query you have to provide index hint . If you are adding a column to your select , may be better to add that column as second column in your datetimeindex

My meaning is that: a table has many fields including one calling d_date (datetime) , and i want to select d_date for clusted index.

Is that right? why?

Thank

My meaning is that: a table has many fields including one calling d_date (datetime) , and i want to select d_date for clusted index.

Is that right? why?

Thanks everybody!

Sorry, but without indepth knowledge of your system, tables, queries, etc... it is impossible to judge to the clustered index. My guess would be that there are better candidates for it, but this is just my guts.

 you can , but why not to be non clustered index.

you have only one cluster index for the table, and it is best to be used for PK

You are getting confused with the index concepts, I suggest to http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx and http://www.sql-server-performance.com/tips/optimizing_indexes_general_p1.aspx link fyi.

Thanks satya again. I think i understand what is a "datetime column" cluster index now.