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.co...ed_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?

Clustered Index

I want a clustered index on a field in a table that is not currently apart o
f
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?[/vbcol
]
Yes
[vbcol=seagreen]
> Or, should I add the mentioned field to my primary key. If adding the fie
ld
> 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.c...red_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 fie
ld
> 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 fie
ld
> to the primary key is a bad idea – why?

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?

clustered and notClustered index

hi,
i am right:
if i create SP dropping clustered index and recreating them will SQL
automatically recreate nonClustered index for that table?
i use create ...with drop_existing only 4 clustered index
when nonClustered indexes are recreated? just after the clustered was
created (in this case should i put some delay before starting the second
one) o after SP ends?You should recreate the index instead of dropping it an creating it because
when you delete the clustered index all non clustered indexes are deleted an
d
created
And when you create the clustered index again all non clustered indexes are
deleted and created
lookup rebuilding an index in BOL
http://sqlservercode.blogspot.com/
"benamis" wrote:

> hi,
> i am right:
> if i create SP dropping clustered index and recreating them will SQL
> automatically recreate nonClustered index for that table?
> i use create ...with drop_existing only 4 clustered index
>
> when nonClustered indexes are recreated? just after the clustered was
> created (in this case should i put some delay before starting the second
> one) o after SP ends?
>