Clustered Index

I want a clustered index on a field in a table that is not currently apart of
the primary key. Should I alter the table and recreate the primary key as
non clustered and then create a new clustered index on the mentioned field?
Or, should I add the mentioned field to my primary key. If adding the field
to the primary key is a bad idea â' why?> I want a clustered index on a field in a table that is not currently apart
of
> the primary key. Should I alter the table and recreate the primary key as
> non clustered and then create a new clustered index on the mentioned field?
Yes
> Or, should I add the mentioned field to my primary key. If adding the field
> to the primary key is a bad idea â' why?
The key of a clustered index is referenced from all non-clustered index. Try
to keep it as narrow as you can.
Tips on Optimizing SQL Server Clustered Indexes
http://www.sql-server-performance.com/clustered_indexes.asp
AMB
"Wes" wrote:
> I want a clustered index on a field in a table that is not currently apart of
> the primary key. Should I alter the table and recreate the primary key as
> non clustered and then create a new clustered index on the mentioned field?
> Or, should I add the mentioned field to my primary key. If adding the field
> to the primary key is a bad idea â' why?|||Wes
Every table MUST have a PRIMARY KEY. But who says that it should be a
CLUSTERED INDEX as well.
You and only you after some testing decide where a clustered index to be
created
Note: Try always drop NC indexes first because they contain CI indexe's
key
CREATE TABLE Test
(
col INT NOT NULL PRIMARY KEY,
col1 INT NOT NULL
)
INSERT Test VALUES (1,20)
INSERT Test VALUES (2,30)
sp_helpindex Test
--PK__Test__58D1301D clustered, unique, primary key located on PRIMARY
ALTER TABLE Test DROP CONSTRAINT PK__Test__58D1301D
GO
CREATE UNIQUE CLUSTERED INDEX col1_ind
ON Test (col)
WITH FILLFACTOR = 90
GO
CREATE NONCLUSTERED INDEX col_ind
ON Test (col1)
WITH FILLFACTOR = 90
sp_helpindex Test
DRO TABLE Test
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:CF08E1B8-1A7B-4498-A8E7-FDD887B0A674@.microsoft.com...
>I want a clustered index on a field in a table that is not currently apart
>of
> the primary key. Should I alter the table and recreate the primary key as
> non clustered and then create a new clustered index on the mentioned
> field?
> Or, should I add the mentioned field to my primary key. If adding the
> field
> to the primary key is a bad idea ? why?|||As a general rule, it's best to use constraints for integrity and then add
indexes as needed for performance. SQL Server automatically creates indexes
for primary key and unique constraints. For these constraints types that
have multiple columns, you can adjust the column order when needed so that
the index is more useful to your queries. This won't change the constraint
behavior.
If you introduce an additional column to your PK, it changes the meaning of
the constraint. It's probably best to create a separate index for the
column. Which index should be clustered depends on your usage patterns and
storage considerations.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:CF08E1B8-1A7B-4498-A8E7-FDD887B0A674@.microsoft.com...
>I want a clustered index on a field in a table that is not currently apart
>of
> the primary key. Should I alter the table and recreate the primary key as
> non clustered and then create a new clustered index on the mentioned
> field?
> Or, should I add the mentioned field to my primary key. If adding the
> field
> to the primary key is a bad idea - why?|||Thanks - everyone.
"Wes" wrote:
> I want a clustered index on a field in a table that is not currently apart of
> the primary key. Should I alter the table and recreate the primary key as
> non clustered and then create a new clustered index on the mentioned field?
> Or, should I add the mentioned field to my primary key. If adding the field
> to the primary key is a bad idea â' why?