ms sql server indexes .....

i have succesfully created indexes with Tsql . I created a unique non clustered index on a column on which i expect unique entry's and plan to issue select statements to retrive only one entry at a time . I had expected to see the entrys in this coumn arranged in ascendinding or descending order according to the index but this doesnt happen. Enterprise manager shows that an index exits but i am not sure if the index is having any effect on query speed.

Is there nothing to worry about or any way to verify the effect of the index ?

See? Are you looking at the B tree? Row are only sorted on disk according to the clustered index.

By default the values in the nonclustered indexes are sorted ASC but that means you will still need to put an ORDER BY indexed_col ASC in order to extract your rows in a meaningful fashion (and use the index to avoid a disk/memory sort).

Issuing a SELECT * FROM table may come back in sorted order by the clustered index but there are no guarantees -- make sure you use an ORDER BY.

Well first thank you.

I dont seem to understand your "order by" idea ( as in the manner of implenting it ). I dont know whether this is implemented in the Tsql statement to create the indexes or tru enterprise manager , or .....

Also , i would really like to know if in your opinion , creating such an index will have any effect on the the particular type of query i said i will be issuing especially as the Index Tuning Wizard reported that it could not recommend any index for my tables

Thanks again

What is your SQL and your CREATE TABLE and CREATE INDEX statements?

well the TSQL format is :


CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ]

< index_option > :: =
{ PAD_INDEX |
FILLFACTOR = fillfactor |
IGNORE_DUP_KEY |
DROP_EXISTING |
STATISTICS_NORECOMPUTE |
SORT_IN_TEMPDB
}


to actually create my indexes i used :

CREATE UNIQUE NONCLUSTERED INDEX employeeID_ind
ON emp_pay (employeeID)

this as u might have seen ignores everything after the "WITH" clause. I then used enterprise manager to specify an ascending order option .


I hope you understand the junk i posted.

There are three situations in which this index will (probably) be used:
1: When running queries like
SELECT ... FROM emp_pay WHERE employee_id = 123
2: When running queries like:
SELECT employee_id FROM emp_pay
3: When running queries like
SELECT .. FROM emp_pay ORDER BY employee_id ASC (it may read it backwards if you specify DESC).

Enterprise manager shows you what is in the database, not how it is stored.

A non-clustered index is a separate data structure than the rows in your table. It creates a listing of all the values in the columns you specified and orders these.

So, your emp_pay table will look something like:
123, '$123,000',
333, '$20,000',
200, '$33,000'

But the index will look like:
123,
200,
333

A clustered index is the SAME as your table (more or less). It will physically move rows around in order to match the ordering in your clustered index. This is what you will see in Enterprise manager.