Indexes

SQ · October 5th, 2004, 03:59 PM

Could someone help out with this one.
When more than one index is on a table (say a clustered and non-clustered index)is all the data in that table stored in both orders ready to be accessed. In other words if you add another index to a table does that double the amount of storage space that table will occupy. I have looked at books on line but can't quite understand it.
Thanks SQ

happygv · October 5th, 2004, 10:24 PM

Hi SQ,

Quote:

quote: from MS site... Clustered Indexes
Clustered indexes are implemented in Oracle as index-organized tables. A clustered index is an index that has been physically merged with a table. The table and index share the same storage area. The clustered index physically rearranges the rows of data in indexed order, forming the intermediate decision nodes. The leaf pages of the index contain the actual table data. This architecture permits only one clustered index per table. Microsoft SQL Server automatically creates a clustered index for the table whenever a PRIMARY KEY or UNIQUE constraint is placed on the table. Clustered indexes are useful for:
â¢ Primary keys.
â¢ Columns that are not updated.
â¢ Queries that return a range of values, using operators such as BETWEEN, >, >=, <, and <=.

All other indexes can be non-clustered. In nonclustered indexes, the index data and the table data are physically separate, and the rows in the table are not stored in the order of the index. Every time a non-clustered index used, it refers the index data.

It doesn't mean that it doubles the amount of storage space that the table will occupy. Take a look at this - Clustered and Non-Clustered Index Data Structures

tempdb and Index Creation

Estimating the Size of a Table

Hope that explains.
Cheers!

_________________________
- Vijay G
Strive for Perfection