[dba-SQLServer] SQL Server 2005 - long count time

Michael,

Thanks for the reply.  Let me expand a little here.

This table is the 99 million record data set that I imported the other day.
The data came with some sort of PK field but it was nothing I created and
had no meaning to me.  It still exists.  

After the import, I created a PKID field, integer, autoincrement etc.
Somehow I created a primary key on that new integer field which used a
clustered index.  I did not intend to.  I can in fact, if I so desire (and
can cause it to happen) delete this PKID field entirely and start over.  I
created it, it is not integral to my data in any way, it is just a method of
setting up a unique record identifier (PK) based on an autoincrement
integer.  

One of the problems that dogs me still is that I cannot do particular things
in SQL Server from the "wizards", things like run a view of any complexity.
They simply time out (after about 1 minute or so).  I can copy and paste the
identical SQL from the view into a "new query" in the same database and it
will run just fine, even if the query takes 30 minutes to run.  It will not
time out.  I have yet to figure that one out.  The impact of that issue
however is that if I try to do things like delete a field in a large table
(for example), it takes so long that the operation times out, again after
about 1 minute.

So, I try to delete this clustered index - timeout.  Try to delete the PK
symbol from the field - timeout.  Try to create a new index - works fine.

So, I have a new non-clustered index on the PKID field, as well as the
clustered index which I cannot delete.  It seems redundant to have both, and
I certainly do not need a clustered index AFAIK.  I really do not understand
the clustered thing (sad, I know), I have read several explanations and I
just haven't yet wrapped my mind around it.

Anyway, other than the fact that I do not need the clustered index (none of
my other such huge tables have one, all are non-clustered) and it is taking
up space, it seems to not cause me any harm.  I was just looking to get rid
of it because AFAIK I do not need it (and it takes up room, or so I
thought).

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Michael
Maddison
Sent: Tuesday, June 12, 2007 10:51 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] SQL Server 2005 - long count time

John,

I don't know why the heap index was quicker but I would seriously hesitate
to remove the clustered index.  
Maybe the rsult of the 1st query was cached when you ran the 2nd query?

As the physical data is stored in clustered index order (a clustered index
is basically the data) removing it turns the table into a heap, literally.
This can have serious consequences for performance particularly on large
tables.  It can also cause fragmentation problems which again can have
performance issues.  AFAIK clustered indexes are preferred for all tables
with more then a dozen or so rows, of course knowing which column/s to index
is a grey area.
I recommend you research slow count queries before making such a radical
change.

cheers

Michael M