Posted by Erland Sommarskog on 12/28/07 22:45

Neil (nospam@nospam.net) writes:
> "PRIMARY KEY constraints create clustered indexes automatically if no
> clustered index already exists on the table and a nonclustered index is
> not specified when you create the PRIMARY KEY constraint."
>
> I think I assumed that the clustered index would automatically be
> created on the pk index.

That's correct. If you say

CREATE TABLE alfons(a int NOT NULL,
b int NOT NULL,
c int NOT NULL,
CONSTRAINT pk_alfons PRIMARY KEY(a, b, c))

The primary will indeed be clustered. You need to include NONCLUSTERED
to have it nonclustered.

Possibly if you created the tables and keys through some table designer,
you may have gotten non-clustered PKs.

> For the five large tables, the before->after on Reserved was as follows:
>
> CustomerMerges: 226661->959
> ImageFilesProcessed: 72333->1727
> CustActivity: 38034->303
> CustomerEvents: 28018->367
> ImageFileErrors: 24391->2046
>
> So, some very dramatic changes there!

Indeed, but...

> I then ran the "sp_spaceused NULL, true" command again. But the results
> were pretty much the same as they had been previously. I then ran the
> optimization job and then reran the "sp_spaceused NULL, true" command,
> and the results were still pretty much the same.

One would have expected the numbers here to fall as well.

And there is another thing that is fishy as well. The "Pages Scanned"
in the old output was nowhere close to the numbers for Reserved. Now,
Reserved also includes non-clustered indexes, but I can't see how an
non-clustered index could be that badly fragmented when you run
defragmentation regularly.

So I am a little uncertain that you really gained that much. Maybe
something is flaky with SQL 7. But if you really gained that much space
by adding clustering indexes - then I would say that you really have a
case for shrinking. But that would be if sp_spaceused reported over
1GB of space unallocated.


> I was wondering if there's a way to have the query: SELECT * FROM
> sysindexes WHERE indid = 0 list the actual table names instead of the
> IDs of the tables without clustered indexes?

object_name(id). But the name of the "index" is the table name for
indid = 0.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]