ahhh ... see this is why i like mr.flump

it is possible to seek on this index . okey .

i have created 2 million records by his/her 1 insert statement.
when ever i even query "select * from tbldocment" . the cluster index scan the whole 2 million . given any query it is doing the same .
with where clause it is a lil bit more .............


so this index is perfectly fine ? ... or the reason for this index is compromised .

Clustered index: It’s a type of index where the data itself is arranged at the indexed column i.e. we have the actual data at the leaf nodes. For example, in a science definition pocket book, every word is arranged alphabetically and the definition of the word is given along with it. ...............

Advantage:

* Seeking operations are much faster than normal indexing.

Disadvantage:

* It will rearrange the whole data at every operation of UPDATE, INSERT or DELETE on key column / cluster index column, in order to keep ordering of the data in that column.


SQL Server Indexes ? Pros and Cons (Part 2) | SQL Lion

create a table and insert few values in it . ( columns id , name ,date )
insert values in it .
id
-----
1
11a
1aa
2a
2b
2x
11h
32fg
345ad

etc

and then select from it with order by clause asc/ desc

this is what is happening in the index scan ( not seek ) . as every condition of where clause is effecting by the pre-ordered scan conditions (naturally) and then again the ORDER BY clause.



provided -- i am just a lil small dba working in a lil tiny comapny having not so much more then 5k employees and just lil few bit of data from 5 countries .