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

Re: question on clustered indexes in sql-server

From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: question on clustered indexes in sql-server
Date	2011-12-01 23:55 +0100
Organization	Erland Sommarskog
Message-ID	<Xns9FAEF36A48289Yazorman@127.0.0.1> (permalink)
References	<jb2ucg$tq7$1@dont-email.me> <Xns9FACF1B805663Yazorman@127.0.0.1> <jb5eld$cj7$1@dont-email.me> <Xns9FADEFAF7E378Yazorman@127.0.0.1> <jb7h5k$k4j$1@dont-email.me>

Show all headers | View raw

Lennart Jonsson (erik.lennart.jonsson@gmail.com) writes:
> 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.
 
So the good news is that there are no page splits.

The bad news is that scans along the clustered index can be jumping forth 
and back.

Am I right?

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

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