Clustered Indexes

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Jon A (JonA_at_discussions.microsoft.com)
Date: 02/19/05 

Date: Sat, 19 Feb 2005 09:15:02 -0800

I have a client who has several tables all of which have no Clustered Index
created on them. For the sake of discussion, let's look at one of the larger
tables 1 million plus rows. The only index is a priamry key constraint
creating a unique non clustered index with a fillfactor of 90. The key fields
are
memberid int 4,
serialnum char(17),
stocknum char(20)
This table every night runs through about 3000 update / inserts. None of the
updates include the key fields. Due to processing rules DTS is not used and
all operations are logged. One program running on the server (single
threaded) does all the updates.
I was under the impression that this index should be a clustered index.
I have read a couple of posts which would suggest I am wrong.
Does anyone have any input? Would it be correct to leave this table with no
clustered index. Note when they work on data it is by memberid as thing are
now there is no physical sort to the data files. If the clstered index was
just put on memberid SQL Server would append a row id so it's unique. How
much overhead is invloved in this?
I have never seen this approach before. Is leaving the table as a heap and
having only a non clustered index which would store a row locator as opposed
to the clustered key more efficient? 

-- 
Thanks,
Jon A

Relevant Pages

  • Re: Clustered Indexes
    ... That is due to the fact that MemberID is the ... Andrew J. Kelly SQL MVP ... >> reasonable fill factor to account for some inserts and minor updates. ... >> forgetting about all that a clustered index can actually increase ...
    (microsoft.public.sqlserver.server)
  • Re: Clustered Indexes
    ... Either way, updates, deletes will likely cause splits to occur. ... I highly recommend that you place "A" clustered index on the table (and all ... You do NOT have to cluster on the Pkey. ... >> If MemberID is the column used most often to fetch and update the rows ...
    (microsoft.public.sqlserver.server)
  • clustered index table updates question - please help
    ... orderqueue (id int identity primary key, customer_id int, queuedate ... clustered index on queuedate ... select * from orderqueue where customer_id is null order by queuedate ... the updates to the cluster index page. ...
    (microsoft.public.sqlserver.connect)
  • Re: Timeouts
    ... > setting on my clustered index and the issue has always been dodged. ... > table is heavy on the inserts and updates, ... queries, just as eliminating page splits helps insert/udpates. ... David Gugick ...
    (microsoft.public.sqlserver.server)
  • Re: Clustered Index Question
    ... It depends on which columns you modify. ... If you modify a column which is part of the clustered index, ... >>is a no brainer per say for SQL Server. ... >>> Updates be any slower since you cant Update an Identity ...
    (microsoft.public.sqlserver.server)