Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: question on clustered indexes in sql-server

Show all headers | View raw

On 2011-11-29 23:45, Erland Sommarskog wrote:
[...]
> 
> I don't know about DB2, but I know that in Oracle, heaps are the norm,
> and index-organised tables is something you use rarely. In SQL Server,
> it is the other way around. The clustered index is the normal thing,
> and heaps is something you only use sometimes. Except in SQL Azure, 
> where heaps are not even supported. All mindsets in SQL Server is
> geared on clustered indexes, and you better know what you are doing if
> use a heap.
> 

Hi Erland, reading the links provided by Bob made me realize that that
are some essential differences between db2 and sql-server (when it comes
to clustering indexes anyhow). If I got it right, the leaf pages in a
clustered index in sql-server is the data pages. In db2 leaf pages
contains a pointer to the data page just like any other index.

In db2 the main focus is how queries may benefit from the clustering
index (reduce i/o and sort), and clustering indexes is therefor not
added until one is knows what the typical queries are. This may of
course be known at design time, but is often not discovered until later.

Where the clustering strategy used in my example would have been
absolutely braindead in db2, it makes a whole lot more sense in
sql-server (even if there seems to be some controversy of what strategy
to choose).

Looking at other constructions in the datamodel, I'll bet a dollar or
two on your auto pilot hyphothesis.


Cheers
/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