Wrox Programmer Forums
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 5th, 2004, 03:59 PM
SQ SQ is offline
Registered User
 
Join Date: Sep 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Indexes

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

 
Old October 5th, 2004, 10:24 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Indexes luisjeronimo BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 0 June 27th, 2008 09:34 AM
Indexes for searching fizzerchris SQL Language 0 May 22nd, 2007 07:09 PM
Indexes for searching fizzerchris SQL Server 2005 0 May 22nd, 2007 07:08 PM
Table Indexes prabodh_mishra SQL Language 1 August 25th, 2006 04:39 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.