clustered indexes in OLTP

Hi everyone,
I came across an opinion that clustered indexes shouldn't be used in busy
OLTP systems. What are pros and cons in that?
Thanks a lot in advance
AlexIt is very useful that a table has a clustered index. What column(s) are you
using for the key, that is another thing. For example, if your table store
orders and the clustered index key is column [orderdate], I do not see a
problem there, but if you are using a uniqueidentifier data type column then
every new insert will cause that the index and table to be sort because of
the ordering of this kind of data.
AMB
"Alex" wrote:

> Hi everyone,
> I came across an opinion that clustered indexes shouldn't be used in busy
> OLTP systems. What are pros and cons in that?
> Thanks a lot in advance
> Alex
>
>|||the opinion is a dangerous one.
Generally speaking, all tables should have a clustered index. this is
crucial to the health of SQL Server.
If you need, I can explain in more detail...
Greg Jackson
PDX, Oregon|||Yes Greg, please, just in a few words
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%239u%23J7RQFHA.3448@.TK2MSFTNGP10.phx.gbl...
> the opinion is a dangerous one.
> Generally speaking, all tables should have a clustered index. this is
> crucial to the health of SQL Server.
> If you need, I can explain in more detail...
>
> Greg Jackson
> PDX, Oregon
>|||Thank you Greg
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eIVchJSQFHA.604@.TK2MSFTNGP10.phx.gbl...
>a table without a clustered index is known as a Heap.
> In a nutshell, heaps can cause Fragmentation which results in Page Splits
> (Expensive on IO)
> IF the table in quesiton, has nonclustered indexes, they cannot be
> defragged without a cluster.
> in order to do index maintenance a Clustered index is absolutely
> necessary.
>
> Here is an article I wrote that discusses many of these specifics.
>
> Cheers
> Greg J
>|||Greg, but why can nc index not be defragged without a cluster?
Alex
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eIVchJSQFHA.604@.TK2MSFTNGP10.phx.gbl...
>a table without a clustered index is known as a Heap.
> In a nutshell, heaps can cause Fragmentation which results in Page Splits
> (Expensive on IO)
> IF the table in quesiton, has nonclustered indexes, they cannot be
> defragged without a cluster.
> in order to do index maintenance a Clustered index is absolutely
> necessary.
>
> Here is an article I wrote that discusses many of these specifics.
>
> Cheers
> Greg J
>|||a heap has no order to it.
with no "Order" how can you move data to contiguous disk space...?
that is the answer. Its impossible.
GAJ|||Well, my understanding is a heap itself (its data pages) can't be defraged
but nonclustered indexes on it (index pages) can be?
Alex
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OAqhz3SQFHA.3144@.tk2msftngp13.phx.gbl...
>a heap has no order to it.
> with no "Order" how can you move data to contiguous disk space...?
>
> that is the answer. Its impossible.
>
> GAJ
>|||no that is incorrect.
easy to prove.
run dbcc indexdefrag or dbcc dbreindex on a heap
before you run check frag levels with ShowContig
check again AFTER ShowContig
you'll see that frag levels (Scan density) have not improved.
GAJ|||http://www.sql-server-performance.c...red_indexes.asp
http://www.quest-pipelines.com/newsletter-v4/1103_B.htm
AMB
"Alex" wrote:

> Yes Greg, please, just in a few words
> "pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
> news:%239u%23J7RQFHA.3448@.TK2MSFTNGP10.phx.gbl...
>
>