Groups > comp.databases.ms-sqlserver > #853

Re: question on clustered indexes in sql-server

From	Lennart Jonsson <erik.lennart.jonsson@gmail.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: question on clustered indexes in sql-server
Date	2011-12-01 10:25 +0100
Organization	A noiseless patient Spider
Message-ID	<jb7h5k$k4j$1@dont-email.me> (permalink)
References	<jb2ucg$tq7$1@dont-email.me> <Xns9FACF1B805663Yazorman@127.0.0.1> <jb5eld$cj7$1@dont-email.me> <Xns9FADEFAF7E378Yazorman@127.0.0.1>

Show all headers | View raw

On 2011-11-30 23:33, Erland Sommarskog wrote:
[...]
>> In db2 leaf pages contains a pointer to the data page just like any
>> other index. 
> 
> So then in DB2, what is the difference between a clustered index and a
> non-clustered index?
> 

If the index is clustered the data pages are ordered according to the
clustering index. When a row is inserted, db2 finds the page where the
row should reside. If the row does'nt fit there, db2 looks in a
neighbourhould of the page. If that does not succedd etheir, the row is
put at the end. In these cases a pointer to the chosen page is stored in
the page where the row should have been. If there are many "overflow"
pages in a table, additional I/O is required when reading pages, and a
reorg of the table should be performed.

For a table without a clustering index db2 store the rows in no
particular order.

/Lennart

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

question on clustered indexes in sql-server Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2011-11-29 16:39 +0100
  Re: question on clustered indexes in sql-server "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-11-29 12:03 -0500
    Re: question on clustered indexes in sql-server Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2011-11-29 18:39 +0100
  Re: question on clustered indexes in sql-server Erland Sommarskog <esquel@sommarskog.se> - 2011-11-29 23:45 +0100
    Re: question on clustered indexes in sql-server Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2011-11-30 15:30 +0100
      Re: question on clustered indexes in sql-server Erland Sommarskog <esquel@sommarskog.se> - 2011-11-30 23:33 +0100
        Re: question on clustered indexes in sql-server Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2011-12-01 10:25 +0100
          Re: question on clustered indexes in sql-server Erland Sommarskog <esquel@sommarskog.se> - 2011-12-01 23:55 +0100
            Re: question on clustered indexes in sql-server Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2011-12-02 08:32 +0100

csiph-web