p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

sql_language thread: Need Help for Indexes and Data Pages


Message #1 by =?iso-8859-1?q?BHAVESH=20JAIN?= <vir64@y...> on Mon, 11 Dec 2000 06:07:52 -0800 (PST)
Well I don't know whether I have all you answers
however you are welcome to the following.

I don't know ehether you have every ripped a database
appart to see how it was structured and what changes
take place when records are added deleted etc..

With Clustered indexes the index pointers are stored
in there sorted order Thus sorting takes place. Not
the as entered.

However the RDMS strategy will obviously exploit a
Clustered index if it exists with other Non Clustered
indexes. Its faster to serach through a Index than the
whole data file.


Non clustered indexes without a clustered index must
act upon the primary (un sorted data) and the system
has to maintain them. This will always be slower.

Of course the two strategies are different so it
follows the system will rebuild its indexes if you
start with non clustering and move to clustering.

Since clustering (if thought through and planned)
makes for efficient faster RDBMS why would you wnt to
drop it.

If you for whatever reason want to drop the clustered
index then create a new table without a clustered
index and insert the old data into the new.


Now the deep question of data pages. You are into your
choice of Operating system. hardware etc.

The last thing they want to do is jump to and fro
between hard disk sectors and records etc. So the
strategy is to hold as much of the data in memory
(that can be quickly dumped to disk in one pass) so
the data in memory is mapped to disk sectors. RDBMS
take upon themselves the storing of information within
disk sectors. It padds these out if necessary but it
doesn't want to cross sector boundaries. No fragmented
data spread over numerous disk sectors.

I assum you are familiar with T-SQL
you will now about 
sysindexes
sp_indexes
sp_column_privileges
etc.

Roland

--- BHAVESH JAIN <vir64@y...> wrote:
> Hello Programmers
> 
>    I need clarification on few statements.
> 
> 1. SQL Server automatically rebuilds any
> non-clustered
> indexes when an existing clustered index is dropped,
> or when a clustered index is created.
> 
> Question a). Why non-clustered gets recreated.
>          b). What data pages are getting recreated.
> 
> 
> 2. DROP_EXISTING option in create index statement
> allows me change non-clustered to clustered index
> type
> but it does not allow me to change clustered to 
> non-clustered.
> 
> 
> 3. The DROP_EXISTING option accelerates the process
> of
> clustered index building by eliminating the sorting
> process.
> 
> Questiong a). It always perform sorting whenever
> clustered index gets created.
> 
> 4. Which system tables stores information rules
> binded
> to any column.
> 
> 
> Thank you for earliest reply.
> 
> 
> 
> Regards
> Bhavesh Jain
> Sr. Software Engineer
> 
> 
> 
> 

---
You are currently subscribed to sql_language as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-sql_language-$subst('Recip.MemberIDChar')@p2p.wrox.com

  Return to Index