Collation

Is it ok to change the database collation from ...Latin1_CP1_AS to
Latin1_General_BIN?
Just issueing the ALTER DABASE ... command is sufficient or I need to change
something else?
Will I be able to retrieve case sensitive data? For ex. if the field name has
the value of 'Bryan Williams' will I retrieve the record if I look for name
like 'bryan*'? or I will get the record only if the query says name like
'Bry%'
Thank you,
-MeIF you change to CASE SENSITIVE, then all queries will be CASE SENSITIVE by
default. Searching for 'bryan' will NOT find 'Bryan'.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Me" <Me@.discussions.microsoft.com> wrote in message
news:4638D07C-339D-4BE6-8264-20DAAED15F52@.microsoft.com...
> Is it ok to change the database collation from ...Latin1_CP1_AS to
> Latin1_General_BIN?
>
> Just issueing the ALTER DABASE ... command is sufficient or I need to
> change
> something else?
> Will I be able to retrieve case sensitive data? For ex. if the field name
> has
> the value of 'Bryan Williams' will I retrieve the record if I look for
> name
> like 'bryan*'? or I will get the record only if the query says name like
> 'Bry%'
> Thank you,
> -Me
>

Collation

Is it ok to change the database collation from ...Latin1_CP1_AS to
Latin1_General_BIN?
Just issueing the ALTER DABASE ... command is sufficient or I need to change
something else?
Will I be able to retrieve case sensitive data? For ex. if the field name has
the value of 'Bryan Williams' will I retrieve the record if I look for name
like 'bryan*'? or I will get the record only if the query says name like
'Bry%'
Thank you,
-Me
IF you change to CASE SENSITIVE, then all queries will be CASE SENSITIVE by
default. Searching for 'bryan' will NOT find 'Bryan'.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Me" <Me@.discussions.microsoft.com> wrote in message
news:4638D07C-339D-4BE6-8264-20DAAED15F52@.microsoft.com...
> Is it ok to change the database collation from ...Latin1_CP1_AS to
> Latin1_General_BIN?
>
> Just issueing the ALTER DABASE ... command is sufficient or I need to
> change
> something else?
> Will I be able to retrieve case sensitive data? For ex. if the field name
> has
> the value of 'Bryan Williams' will I retrieve the record if I look for
> name
> like 'bryan*'? or I will get the record only if the query says name like
> 'Bry%'
> Thank you,
> -Me
>

Collation

Is it ok to change the database collation from ...Latin1_CP1_AS to
Latin1_General_BIN?
Just issueing the ALTER DABASE ... command is sufficient or I need to change
something else?
Will I be able to retrieve case sensitive data? For ex. if the field name ha
s
the value of 'Bryan Williams' will I retrieve the record if I look for name
like 'bryan*'? or I will get the record only if the query says name like
'Bry%'
Thank you,
-MeIF you change to CASE SENSITIVE, then all queries will be CASE SENSITIVE by
default. Searching for 'bryan' will NOT find 'Bryan'.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Me" <Me@.discussions.microsoft.com> wrote in message
news:4638D07C-339D-4BE6-8264-20DAAED15F52@.microsoft.com...
> Is it ok to change the database collation from ...Latin1_CP1_AS to
> Latin1_General_BIN?
>
> Just issueing the ALTER DABASE ... command is sufficient or I need to
> change
> something else?
> Will I be able to retrieve case sensitive data? For ex. if the field name
> has
> the value of 'Bryan Williams' will I retrieve the record if I look for
> name
> like 'bryan*'? or I will get the record only if the query says name like
> 'Bry%'
> Thank you,
> -Me
>

Clustered Primary Key

Hello!
How can I create a clustered primary key?
This alter table statement get the following error:
ALTER TABLE test ADD CONSTRAINT
PK_Table_1 Primary Key CLUSTERED
(
[Field 1],
[Field 2]
)
The constraint specified is not valid.

Thank you
SaschaThe CLUSTERED keyword is not supported by SQL Compact Edition. If you drop it, the statement should run OK.

Clustered Index Change

Is there an easy way to alter an identity column which is the clustered index and primary key on a table so that it is no longer clustered? I am going to create a new clustered index but the current one is difficult to alter.

Thanks in advance.

Drop the existing primary key constraint and re-create it again.

Code Snippet

Create table IndexChange

(

Id int identity(1,1) constraint IndexChange_PK primary key clustered,

Name varchar(100)

)

Alter Table IndexChange Drop Constraint IndexChange_PK

Alter Table IndexChange Add Constraint IndexChange_PK Primary Key nonclustered (id)

|||Thanks Manivannan. I was hoping I could alter the index because it is a foreign key constraint on other tables. I can't just drop it because of the references. Is there a way to do it without removing the contraints?|||

There is not a way to 'convert' an index from custered to non-clustered WITHOUT dropping it and re-creating it.

You may have to create a script that

drops all of the FK constraints

drops all of the other indexes on the table

drops the PK constraint

re-creates the PK constraint non-clustered

re-creates the other indexes on the table

re-creates the FK constraints

Dropping and later re-creating the other indexes on the table should greatly increase the speed of the process.

|||

That explains why I couldn't find information on altering the index to remove the cluster.

I'll drop the FK constraints and the indexes and recreate them. No easy way out.

Thanks Arnie!

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?