Applying an Index

[innmedia]

Hi all,

Is there a difference between creating an index and applying it? I have been trying out various indexes on a table, on different fields, ascending, descending, and so on.

But every time I open the table it is in the same order. How do I get an index to affect the order of records?

Thanks!

 

[hmckillop]

If you create a clustered index it will physically order the data and as such will return it in order.
However, you should use ORDER BY on the fields you need to order by. This is where the indices can be used, if you include the field you wish to order by in the index it means it wont have to perform a sort operation on the returned data.


"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[SQLSister]

Going on with what hmckillop said, there is only one clustered index allowed per table. (Records can only physically be stored in one order.)
This is automatically created on the primary key, but can be created on a differnt field if you desire.

If you want to see the data displayed in a differnt order, then you need to write queries using the order by clause. It would be a very bad idea to drop the old clustered index and replace it with a new one every time you wanted to see the data in a different order.


Questions about posting. See faq183-874

 

[innmedia]

THanks All. THe Clustered Index did the trick!

 

[ESquared]

Just be aware that having a clustered index on nondistinct data can be inefficient. Also, if you are doing a lot of updates to a field, then it is not a good candidate for a clustered index. If you are inserting a lot of new records, then a field is also not a good candidate for being clustered if the new rows are not always greater than the rows already in the table. These last two points are due to the fact that the clustered index rows must be in physical order across data pages.

By 'nondistinct data' I mean, for example, that indexing a field which can have only the values M and F will not help you very much. The best index is one that can have a unique value per record.

Also be wary of indexing plaintext columns, especially very wide ones. Some of this can be alleviated through maintenance of a hash column, but that is an advanced technique.

 

[innmedia]

ESqared -

Thanks for the tips. The index is on a field with unique data. Also it is set to the table after all the updates are in. It's just to serve that the data will be sorted the way the user needs it.

 

[ESquared]

Even though a clustered index does cause records to appear in a certain order, you should not rely on it or be in the habit of relying on any particular order from an index. Instead, use an ORDER BY clause, as others have suggested.

You cannot guarantee for sure that the records will always be in the order you desire without ORDER BY. Things that can change are: the index is changed (by you or a successor), the database engine changes (patches, new versions of SQL server).

 

[innmedia]

Can you do Order By just on a table? No SQL statement, no view. How/where does it get done?

 

[ESquared]

How and why are your users seeing a table where you have no means of using a query or view? Sounds strange.

 

[innmedia]

There is raw data in one SQL table. Then, there is a Access/VBA program that changes, sums data, etc. and writes it to the new table.

Then the users use SPSS, a statistical tool, to work with the data. From SPSS they link or import the SQL table. They need the table sorted.

 

[ESquared]

You can use a view just like a table and no one outside SQL server will be the wiser... just put TOP 100 PERCENT and ORDER BY in it.

 

[innmedia]

OK I will give it a try thanks!