Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail From: Lennart Jonsson Newsgroups: comp.databases.ms-sqlserver Subject: Re: question on clustered indexes in sql-server Date: Wed, 30 Nov 2011 15:30:05 +0100 Organization: A noiseless patient Spider Lines: 37 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 7bit Injection-Date: Wed, 30 Nov 2011 14:30:05 +0000 (UTC) Injection-Info: mx04.eternal-september.org; posting-host="ea/GVBKKbWf16J2shRyUPw"; logging-data="12903"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX19O2mZkrEKqpMhzKoNa5Lh7" User-Agent: Mozilla/5.0 (X11; Linux i686; rv:8.0) Gecko/20111105 Thunderbird/8.0 In-Reply-To: Cancel-Lock: sha1:+nn6zgfz01ZNR73+u40/uL+35VM= Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:851 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 [...]