Change included columns when modifying table

Is there a reason you can't modify included columns when modifying an index on a table?

Well, the primary reason would be due to the fact that adding additional included columns would touch all of the leaf pages in an index, and probably force a fair majority of pages to split multiple times, so in reality most of the time recreating the index from scratch would probably be faster and more efficient once completed. Additionally, a heavy heavy number of disk seeks would be incurred jumping from the position last updated in the index to the table data to read the data to be added and then jump back again.

Basically, I'm sure you'd find that rebuilding the index will almost always give you both faster results, and additionally a more efficient (clean) index when the operation completes.

HTH,

|||I full appreciate that the index needs to be rebuilt, however SSMS manages that for you when you change the columns, clustering, file groups etc. So why can't I change the included columns and SSMS just do a drop_existing.

Change Clustered Index to Non-Clustered Index

When I create a table in Enterprise Manager, the Primary
Key is created as a Clustered Index (As there is no other
Clustered Index exists in that table).
However, when I attempt to change the Clustered Index to a
non-Clustered Index, it says that "Cannot convert a
Clustered Index to an nonclustered index using the
DROP_EXISTING Option".
I would like to know
1) Is it possible to change the Clustered Index to Non-
clustered Index in Enterprise Manager OR we have to change
it in Query Analyzer ?
2) When we create a table in Enterprise Manager, can we
specify a column as a Clustered Index (Instead of creating
Clustered Index in the Primary Key)?
ThanksRoger
DROP TABLE TEST
CREATE TABLE TEST
(
COL INT NOT NULL PRIMARY KEY
)
--Run this sp to make sure you have clustered unique index
SP_HELPINDEX TEST
--other way to create clustered index
CREATE TABLE TEST (COL INT)
GO
CREATE UNIQUE CLUSTERED INDEX Idx1 ON TEST(COL)
--change clustered index to non_clustered
CREATE UNIQUE CLUSTERED INDEX Idx1 ON TEST(COL)
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'Idx1')
DROP INDEX TEST.Idx1
CREATE UNIQUE NONCLUSTERED INDEX Idx1 ON TEST(COL)
SP_HELPINDEX TEST
"Roger Lee" <rogerlee@.nospam.com> wrote in message
news:04e701c35a5b$1667a1c0$a101280a@.phx.gbl...
> When I create a table in Enterprise Manager, the Primary
> Key is created as a Clustered Index (As there is no other
> Clustered Index exists in that table).
> However, when I attempt to change the Clustered Index to a
> non-Clustered Index, it says that "Cannot convert a
> Clustered Index to an nonclustered index using the
> DROP_EXISTING Option".
> I would like to know
> 1) Is it possible to change the Clustered Index to Non-
> clustered Index in Enterprise Manager OR we have to change
> it in Query Analyzer ?
> 2) When we create a table in Enterprise Manager, can we
> specify a column as a Clustered Index (Instead of creating
> Clustered Index in the Primary Key)?
> Thanks|||Roger
You can change a clustered index to non-clustered in EM.
How did you try to do it? Use the properties window in the
design table pane. (it generates a drop index and create
index stement for you)
You can create a column or indeed multi-column clustered
index on any data you like. Again using EM, the design
table is the easiest way to do it.
Hope this helps.
John|||Dear John,
Does "Design Table Pane" mean the Database Diagram ?
I create a new database diagram with that table and I am
able to chagne the Clustered Index to Non-Clustered Index.
Thanks|||Roger
No not the database diagram.
In EM open up databases on your server. Then open the
database you want. Click on tables. In the pane on the
right, right click on the table you are interested in and
choose 'design table'.
One in the design table view you can open index properties
and there you can do a variety of thing with your indexes
including creating new ones, moving filegroups, make an
index clustered (as long as there is not already one) or
make it unclustered if it already is clustered.
When you exit the design table view it will ask if you
want to save the changes, say yes if you want the changes
you have made to take effect.
Regards
John

change clustered index

Hi,
I have a table with 1.5 million records. It has the primary key as the clustered index now, I would like to change the create_dt as clustered index. When I do reindex, will it block the table? How long will it take? Can I do it in EM from the Design able? ThanksYes you can do it from EM and yes it will block all access to the table for
the entire duration of the process. How long this takes depends mainly on
your hardware configuration.
--
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:A0F7FA2F-77F0-486B-99C2-31B2DD2E1409@.microsoft.com...
> Hi,
> I have a table with 1.5 million records. It has the primary key as the
clustered index now, I would like to change the create_dt as clustered
index. When I do reindex, will it block the table? How long will it take?
Can I do it in EM from the Design able? Thanks|||Hi,
This operation has to be done when there is no activity in this table.
Execute the below statements from Query Analyzer, Replace the table name ,
constraint name and column name with yours.
ALTER TABLE dbo.tablename
DROP CONSTRAINT Pkey_constraintname
go
ALTER TABLE dbo.tablename ADD CONSTRAINT
Pkey_constraintname PRIMARY KEY NONCLUSTERED
(
columnname
)
go
Create Clustered index IDX_name on tablename(newcolumn)
Thanks
Hari
MCDBA
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:A0F7FA2F-77F0-486B-99C2-31B2DD2E1409@.microsoft.com...
> Hi,
> I have a table with 1.5 million records. It has the primary key as the
clustered index now, I would like to change the create_dt as clustered
index. When I do reindex, will it block the table? How long will it take?
Can I do it in EM from the Design able? Thanks

change clustered index

Hi,
I have a table with 1.5 million records. It has the primary key as the clust
ered index now, I would like to change the create_dt as clustered index. Whe
n I do reindex, will it block the table? How long will it take? Can I do it
in EM from the Design able?
ThanksYes you can do it from EM and yes it will block all access to the table for
the entire duration of the process. How long this takes depends mainly on
your hardware configuration.
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:A0F7FA2F-77F0-486B-99C2-31B2DD2E1409@.microsoft.com...
> Hi,
> I have a table with 1.5 million records. It has the primary key as the
clustered index now, I would like to change the create_dt as clustered
index. When I do reindex, will it block the table? How long will it take?
Can I do it in EM from the Design able? Thanks|||Hi,
This operation has to be done when there is no activity in this table.
Execute the below statements from Query Analyzer, Replace the table name ,
constraint name and column name with yours.
ALTER TABLE dbo.tablename
DROP CONSTRAINT Pkey_constraintname
go
ALTER TABLE dbo.tablename ADD CONSTRAINT
Pkey_constraintname PRIMARY KEY NONCLUSTERED
(
columnname
)
go
Create Clustered index IDX_name on tablename(newcolumn)
Thanks
Hari
MCDBA
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:A0F7FA2F-77F0-486B-99C2-31B2DD2E1409@.microsoft.com...
> Hi,
> I have a table with 1.5 million records. It has the primary key as the
clustered index now, I would like to change the create_dt as clustered
index. When I do reindex, will it block the table? How long will it take?
Can I do it in EM from the Design able? Thanks

Chain Linkage Problem

Hi All,
I am facing this error continously:
Error: 8908, Severity: 22, State: 6
Table error: Database ID 7, object ID 477400920, index ID 0. Chain
linkage
mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
(1:736799)..
And when I run DBCC CHECKTABLE it shows consistency errors.
It get fixed using DBCC DBReIndex but re-occur after sometimes.
I have tried to find in newsgroup, some of them says it can happen due to
NOLOCK hint.
Is there any solution for this problem or please help me to identify the
root cause of the problem.
Thanks
Ritesh
Hi
KB article 308886 describes the issue, butthose errors occur in that query
only and do not affect data. In you case, you are showing data issues.
Have you run DBCC CheckDB and what version of SQL are you on?
Regards
Mike
"Ritesh" wrote:

> Hi All,
> I am facing this error continously:
> Error: 8908, Severity: 22, State: 6
> Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> linkage
> mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
> (1:736799)..
> And when I run DBCC CHECKTABLE it shows consistency errors.
> It get fixed using DBCC DBReIndex but re-occur after sometimes.
> I have tried to find in newsgroup, some of them says it can happen due to
> NOLOCK hint.
> Is there any solution for this problem or please help me to identify the
> root cause of the problem.
> Thanks
> Ritesh
>
>
|||Hi Mike,
Thanks for your response.
We are using Enterprise version of SQL 2000. Consistency errors get resolved
using DBCC DBReIndex but it is a re-occuring problem. Presently our server
is handling heavy traffic and according business logic needs we need to
access just inserted data, say Top 50 records desc by CreatedDateTime for
avoiding delay due to locks we use NOLock hints.
Will also like to know from you, though out of context, is there any way to
compute Fillfactor for indexes or just hit and trial is the only way. I have
a table which is more than 750 MB in size and in every 1 second atleast 10
records are getting inserted ( with updates also). Putting 90 or 85 can have
its effects but want to know exact FACTORS to be used, if any?
Will appreciate your help in this regard and think it may also help in
resolving the original issue (which may have this as its root cause)
Thanks & Regards,
Ritesh Khanna
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> KB article 308886 describes the issue, butthose errors occur in that query
> only and do not affect data. In you case, you are showing data issues.
> Have you run DBCC CheckDB and what version of SQL are you on?
> Regards
> Mike
> "Ritesh" wrote:

Chain Linkage Problem

Hi All,
I am facing this error continously:
Error: 8908, Severity: 22, State: 6
Table error: Database ID 7, object ID 477400920, index ID 0. Chain
linkage
mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev = (1:736799)..
And when I run DBCC CHECKTABLE it shows consistency errors.
It get fixed using DBCC DBReIndex but re-occur after sometimes.
I have tried to find in newsgroup, some of them says it can happen due to
NOLOCK hint.
Is there any solution for this problem or please help me to identify the
root cause of the problem.
Thanks
RiteshHi
KB article 308886 describes the issue, butthose errors occur in that query
only and do not affect data. In you case, you are showing data issues.
Have you run DBCC CheckDB and what version of SQL are you on?
Regards
Mike
"Ritesh" wrote:
> Hi All,
> I am facing this error continously:
> Error: 8908, Severity: 22, State: 6
> Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> linkage
> mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev => (1:736799)..
> And when I run DBCC CHECKTABLE it shows consistency errors.
> It get fixed using DBCC DBReIndex but re-occur after sometimes.
> I have tried to find in newsgroup, some of them says it can happen due to
> NOLOCK hint.
> Is there any solution for this problem or please help me to identify the
> root cause of the problem.
> Thanks
> Ritesh
>
>|||Hi Mike,
Thanks for your response.
We are using Enterprise version of SQL 2000. Consistency errors get resolved
using DBCC DBReIndex but it is a re-occuring problem. Presently our server
is handling heavy traffic and according business logic needs we need to
access just inserted data, say Top 50 records desc by CreatedDateTime for
avoiding delay due to locks we use NOLock hints.
Will also like to know from you, though out of context, is there any way to
compute Fillfactor for indexes or just hit and trial is the only way. I have
a table which is more than 750 MB in size and in every 1 second atleast 10
records are getting inserted ( with updates also). Putting 90 or 85 can have
its effects but want to know exact FACTORS to be used, if any?
Will appreciate your help in this regard and think it may also help in
resolving the original issue (which may have this as its root cause)
Thanks & Regards,
Ritesh Khanna
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> KB article 308886 describes the issue, butthose errors occur in that query
> only and do not affect data. In you case, you are showing data issues.
> Have you run DBCC CheckDB and what version of SQL are you on?
> Regards
> Mike
> "Ritesh" wrote:
> > Hi All,
> >
> > I am facing this error continously:
> >
> > Error: 8908, Severity: 22, State: 6
> >
> > Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> > linkage
> > mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev => > (1:736799)..
> >
> > And when I run DBCC CHECKTABLE it shows consistency errors.
> > It get fixed using DBCC DBReIndex but re-occur after sometimes.
> >
> > I have tried to find in newsgroup, some of them says it can happen due to
> > NOLOCK hint.
> >
> > Is there any solution for this problem or please help me to identify the
> > root cause of the problem.
> >
> > Thanks
> > Ritesh
> >
> >
> >

Chain Linkage Problem

Hi All,
I am facing this error continously:
Error: 8908, Severity: 22, State: 6
Table error: Database ID 7, object ID 477400920, index ID 0. Chain
linkage
mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
(1:736799)..
And when I run DBCC CHECKTABLE it shows consistency errors.
It get fixed using DBCC DBReIndex but re-occur after sometimes.
I have tried to find in newsgroup, some of them says it can happen due to
NOLOCK hint.
Is there any solution for this problem or please help me to identify the
root cause of the problem.
Thanks
RiteshHi
KB article 308886 describes the issue, butthose errors occur in that query
only and do not affect data. In you case, you are showing data issues.
Have you run DBCC CheckDB and what version of SQL are you on?
Regards
Mike
"Ritesh" wrote:

> Hi All,
> I am facing this error continously:
> Error: 8908, Severity: 22, State: 6
> Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> linkage
> mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
> (1:736799)..
> And when I run DBCC CHECKTABLE it shows consistency errors.
> It get fixed using DBCC DBReIndex but re-occur after sometimes.
> I have tried to find in newsgroup, some of them says it can happen due to
> NOLOCK hint.
> Is there any solution for this problem or please help me to identify the
> root cause of the problem.
> Thanks
> Ritesh
>
>|||Hi Mike,
Thanks for your response.
We are using Enterprise version of SQL 2000. Consistency errors get resolved
using DBCC DBReIndex but it is a re-occuring problem. Presently our server
is handling heavy traffic and according business logic needs we need to
access just inserted data, say Top 50 records desc by CreatedDateTime for
avoiding delay due to locks we use NOLock hints.
Will also like to know from you, though out of context, is there any way to
compute Fillfactor for indexes or just hit and trial is the only way. I have
a table which is more than 750 MB in size and in every 1 second atleast 10
records are getting inserted ( with updates also). Putting 90 or 85 can have
its effects but want to know exact FACTORS to be used, if any?
Will appreciate your help in this regard and think it may also help in
resolving the original issue (which may have this as its root cause)
Thanks & Regards,
Ritesh Khanna
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> KB article 308886 describes the issue, butthose errors occur in that query
> only and do not affect data. In you case, you are showing data issues.
> Have you run DBCC CheckDB and what version of SQL are you on?
> Regards
> Mike
> "Ritesh" wrote:
>

Catalog and characters with accent

I have a question about SQL Server 2000 Full Text Index.
I want to create a catalog in a field (varchar(255)), but Im with 2
problems:
1. The characters of this field can have accent. But when I do a search
I want to see the rows with and without the accent. For example:
SELECT NAME
FROM TABLE
WHERE CONTAINS (FIELD, '"PLASTICO*"')
With this command I want to see the row PLASTICO and the row PLSTICO.
Is it possible? Now, Im just receiving only the row PLASTICO. I need
that the catalog be accent insensitive. Can I do that?
2. My sencond problem is: I need to see also the rows that have the word
PLASTICO inside the complete word. For example: I want to see also the
rows with INTERPLASTICO, 2PLASTICO, XPTOPLASTICO. But whe I wrote the
following command I dont receive these words:
SELECT NAME
FROM TABLE
WHERE CONTAINS (FIELD, '"*PLASTICO*"')
Is it possible to do that? I wnat to see the rows that have the word
PLASTIC in the begin, middle or end of the words.
Thaks,
Paulo
*** Sent via Developersdex http://www.codecomments.com ***
SQL 2005 can solve both your problems. You can configure your catalog for
accent insensitive searches. You can also use the thesaurus option to expand
your search on plastico to search on interpastico, 2plastico, and
xptoplastico, as long as you enter all of these expansion terms into your
thesaurus file in advance.
In SQL 2000 you have to expand your search terms for accented or unaccented
versions as well as the alternate word forms.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paulo Andre Ortega Ribeiro" <paulo.andre.66@.terra.com.br> wrote in message
news:O%23k7qFyeFHA.2740@.TK2MSFTNGP10.phx.gbl...
> I have a question about SQL Server 2000 Full Text Index.
> I want to create a catalog in a field (varchar(255)), but Im with 2
> problems:
> 1. The characters of this field can have accent. But when I do a search
> I want to see the rows with and without the accent. For example:
> SELECT NAME
> FROM TABLE
> WHERE CONTAINS (FIELD, '"PLASTICO*"')
> With this command I want to see the row PLASTICO and the row PLSTICO.
> Is it possible? Now, Im just receiving only the row PLASTICO. I need
> that the catalog be accent insensitive. Can I do that?
> 2. My sencond problem is: I need to see also the rows that have the word
> PLASTICO inside the complete word. For example: I want to see also the
> rows with INTERPLASTICO, 2PLASTICO, XPTOPLASTICO. But whe I wrote the
> following command I dont receive these words:
> SELECT NAME
> FROM TABLE
> WHERE CONTAINS (FIELD, '"*PLASTICO*"')
> Is it possible to do that? I wnat to see the rows that have the word
> PLASTIC in the begin, middle or end of the words.
> Thaks,
> Paulo
>
>
> *** Sent via Developersdex http://www.codecomments.com ***