refreshing index after updates to indexed column in database?

I have just begun to experiment with the full text searching capabilities in
SQL2000. Very happy so far! Nice work. I like the INFLECTIONAL results.
When the user makes a change or addition to a row's full-text-indexed column
(e.g. adds the phrase "now available in brushed aluminum" to the Description
column) what needs to be done so that the full-text indexes reflect the
change/addition and the row will be found in subsequent searches? Is there
a daemon that does reindexing periodically that needs to be configured, or
can updates be made to appear in real time, or does the index have to be
rebuilt in toto manually?
Thanks!
TR
Change tracking does near real time updates. To enable change tracking use the following proc
sp_fulltext_table 'TableName','start_change_tracking'
sp_fulltext_table 'TableName','start_background_updateindex'
You do not need a timestamp column on your table.
If you want to do an incremental index and your table has a time stamp column you have to manually kick off the indexing process. If your table does not have a timestamp column a full population will be run, and again you have to manually kick it off.
use
sp_fulltext_table 'TableName','start_incremental' -- for an incremental population
or
sp_fulltext_table 'TableName','start_full' -- for a full population
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
-- TR wrote: --
I have just begun to experiment with the full text searching capabilities in
SQL2000. Very happy so far! Nice work. I like the INFLECTIONAL results.
When the user makes a change or addition to a row's full-text-indexed column
(e.g. adds the phrase "now available in brushed aluminum" to the Description
column) what needs to be done so that the full-text indexes reflect the
change/addition and the row will be found in subsequent searches? Is there
a daemon that does reindexing periodically that needs to be configured, or
can updates be made to appear in real time, or does the index have to be
rebuilt in toto manually?
Thanks!
TR
|||Thanks!

Redundant Indexes?

Hi
I would get rid or A and B. If there is no clustered index on that table,
make C clustered too, assuming it has good selectivity.
Without the data types, it is not possible to tell and be 100% sure (your
attachment gets blocked by Outlook Express)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> Hello,
>
> Ive got a table called 'prd_movcomision' (Ive attached a script with its
> structure) which has several indexes as you can see. There are several
> indexes which are contained in others. For example:
> CREATE INDEX [A] ON [dbo].[prd_movcomision]([pers_rutcia]
, [poli_codigo],
> [itpo_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [B] ON [dbo].[prd_movcomision]([poli_codigo],[/vbco
l]
[itpo_codigo])[vbcol=seagreen]
> ON [PRIMARY]
> GO
> CREATE INDEX [C] ON [dbo].[prd_movcomision]([prop_codigo
]) ON [PRIMARY]
> GO
>
> Given the previous indexes, [B] is contained within [A] and &#
91;C] is
> contained within [B]. The question is if this is really neccessary or
once
> I have an index A the other indexes (B and C) are redundant. Im having
too
> many problems with the size of the database, it is growing too fast.
>
> Any advice on this would be very much appreciated.
>
> Jorge Luzarraga
>
>Hello Mike,
Are you talking about getting rid of the one which contains the others? Any
help is very much appreciated.
this is the script:
****************************************
******************************
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id, N'IsUse
rTable')
= 1)
drop table [dbo].[prd_movcomision]
GO
CREATE TABLE [dbo].[prd_movcomision] (
[movc_codigo] [numeric](18, 0) NOT NULL ,
[movc_fecmov] [datetime] NOT NULL ,
[pers_rutcia] [numeric](9, 0) NULL ,
[infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[doco_numero] [numeric](18, 0) NULL ,
[poli_codigo] [numeric](18, 0) NULL ,
[itpo_codigo] [numeric](18, 0) NULL ,
[esta_codigo] [numeric](4, 0) NULL ,
[mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[prop_codigo] [numeric](18, 0) NULL ,
[itpr_codigo] [numeric](18, 0) NULL ,
[cone_codigo] [numeric](18, 0) NULL ,
[prop_fecdocum] [datetime] NULL ,
[prop_fecemision] [datetime] NULL ,
[prop_fecinicio] [datetime] NULL ,
[itpr_fecinicio] [datetime] NULL ,
[poli_fecemision] [datetime] NULL ,
[poli_fecinicio] [datetime] NULL ,
[itpo_fecinicio] [datetime] NULL ,
[movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[movc_periodo] [numeric](6, 0) NULL ,
[movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL
,
[movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[movc_fijo] [numeric](18, 4) NULL ,
[movc_variable] [numeric](7, 3) NULL ,
[movc_prima] [numeric](18, 4) NULL ,
[movc_montomo] [numeric](18, 4) NOT NULL ,
[movc_montomc] [numeric](18, 4) NULL ,
[movc_fecvalor] [datetime] NULL ,
[movc_feccierre] [datetime] NULL ,
[movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cier_correlativo] [numeric](18, 0) NULL ,
[cuot_codigo] [numeric](18, 0) NULL ,
[cuot_numero] [numeric](4, 0) NULL ,
[movc_fecmeta] [datetime] NULL ,
[movc_primaneta] [t_dom_monto] NULL ,
[movc_fecpagocomis] [t_dom_fecha] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
(
[movc_codigo]
) ON [PRIMARY]
GO
CREATE INDEX [AK_prd_movcomision] ON
[dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia], &#
91;movc_concepto],
[cone_codigo], [poli_codigo]) ON [PRIMARY]
GO
CREATE INDEX [Ai_prd_movcomision2] ON
[dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [i
tpo_codigo]) ON
[PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision] ON
[dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON
1;PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision1] ON
[dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision2] ON
[dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
[dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
GO
ALTER TABLE [dbo].[prd_movcomision] ADD
CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
(
[cone_codigo]
) REFERENCES [dbo].[prd_comnegocio] (
[cone_codigo]
),
CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
(
[cuot_codigo],
[cuot_numero]
) REFERENCES [dbo].[prd_cuota] (
[cuot_codigo],
[cuot_numero]
),
CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
(
[pers_rutcia],
[infi_codigo],
[doco_numero]
) REFERENCES [dbo].[prd_doccomision] (
[pers_rutcia],
[infi_codigo],
[doco_numero]
),
CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
(
[esta_codigo]
) REFERENCES [dbo].[adm_estado] (
[esta_codigo]
),
CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
(
[movc_periodo],
[cier_tipo],
[cier_correlativo]
) REFERENCES [dbo].[prd_estadocierre] (
[cier_periodo],
[cier_tipo],
[cier_correlativo]
),
CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
(
[prop_codigo],
[itpr_codigo]
) REFERENCES [dbo].[vta_itmpropuesta] (
[prop_codigo],
[itpr_codigo]
),
CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
(
[mone_codigo]
) REFERENCES [dbo].[adm_moneda] (
[mone_codigo]
)
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est
_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est
cier_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_itm
pr_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_mnd
_movcom]
GO
****************************************
*************************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
> Hi
> I would get rid or A and B. If there is no clustered index on that table,
> make C clustered too, assuming it has good selectivity.
> Without the data types, it is not possible to tell and be 100% sure (your
> attachment gets blocked by Outlook Express)
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
> news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> [itpo_codigo])
> once
> too
>|||Hi
All those columns are nullable, so making a compound index (multiple
columns) is not a good idea. From my experience, the query processor will
think of them as 2nd choice.
Having 1 index on poli_codigo and one on poli_codigo will cover your
queries, and will improve Insert/Update performance as compound index add a
lot to index maintenance.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:u0fD$1c6EHA.208@.TK2MSFTNGP12.phx.gbl...
> Hello Mike,
> Are you talking about getting rid of the one which contains the others?
Any
> help is very much appreciated.
> this is the script:
> ****************************************
******************************
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id,
N'IsUserTable')
> = 1)
> drop table [dbo].[prd_movcomision]
> GO
> CREATE TABLE [dbo].[prd_movcomision] (
> [movc_codigo] [numeric](18, 0) NOT NULL ,
> [movc_fecmov] [datetime] NOT NULL ,
> [pers_rutcia] [numeric](9, 0) NULL ,
> [infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [doco_numero] [numeric](18, 0) NULL ,
> [poli_codigo] [numeric](18, 0) NULL ,
> [itpo_codigo] [numeric](18, 0) NULL ,
> [esta_codigo] [numeric](4, 0) NULL ,
> [mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
> [prop_codigo] [numeric](18, 0) NULL ,
> [itpr_codigo] [numeric](18, 0) NULL ,
> [cone_codigo] [numeric](18, 0) NULL ,
> [prop_fecdocum] [datetime] NULL ,
> [prop_fecemision] [datetime] NULL ,
> [prop_fecinicio] [datetime] NULL ,
> [itpr_fecinicio] [datetime] NULL ,
> [poli_fecemision] [datetime] NULL ,
> [poli_fecinicio] [datetime] NULL ,
> [itpo_fecinicio] [datetime] NULL ,
> [movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [movc_periodo] [numeric](6, 0) NULL ,
> [movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS[/vb
col]
NULL[vbcol=seagreen]
> ,
> [movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
> [movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [movc_fijo] [numeric](18, 4) NULL ,
> [movc_variable] [numeric](7, 3) NULL ,
> [movc_prima] [numeric](18, 4) NULL ,
> [movc_montomo] [numeric](18, 4) NOT NULL ,
> [movc_montomc] [numeric](18, 4) NULL ,
> [movc_fecvalor] [datetime] NULL ,
> [movc_feccierre] [datetime] NULL ,
> [movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1
_CI_AS
> NULL ,
> [movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [cier_correlativo] [numeric](18, 0) NULL ,
> [cuot_codigo] [numeric](18, 0) NULL ,
> [cuot_numero] [numeric](4, 0) NULL ,
> [movc_fecmeta] [datetime] NULL ,
> [movc_primaneta] [t_dom_monto] NULL ,
> [movc_fecpagocomis] [t_dom_fecha] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
> CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
> (
> [movc_codigo]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [AK_prd_movcomision] ON
> [dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia],
[movc_concepto],
> [cone_codigo], [poli_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [Ai_prd_movcomision2] ON
> [dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [
;itpo_codigo]) ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision] ON
> [dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON &
#91;PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision1] ON
> [dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision2] ON
> [dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
> [dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
> GO
> ALTER TABLE [dbo].[prd_movcomision] ADD
> CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
> (
> [cone_codigo]
> ) REFERENCES [dbo].[prd_comnegocio] (
> [cone_codigo]
> ),
> CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
> (
> [cuot_codigo],
> [cuot_numero]
> ) REFERENCES [dbo].[prd_cuota] (
> [cuot_codigo],
> [cuot_numero]
> ),
> CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
> (
> [pers_rutcia],
> [infi_codigo],
> [doco_numero]
> ) REFERENCES [dbo].[prd_doccomision] (
> [pers_rutcia],
> [infi_codigo],
> [doco_numero]
> ),
> CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
> (
> [esta_codigo]
> ) REFERENCES [dbo].[adm_estado] (
> [esta_codigo]
> ),
> CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
> (
> [movc_periodo],
> [cier_tipo],
> [cier_correlativo]
> ) REFERENCES [dbo].[prd_estadocierre] (
> [cier_periodo],
> [cier_tipo],
> [cier_correlativo]
> ),
> CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
> (
> [prop_codigo],
> [itpr_codigo]
> ) REFERENCES [dbo].[vta_itmpropuesta] (
> [prop_codigo],
> [itpr_codigo]
> ),
> CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
> (
> [mone_codigo]
> ) REFERENCES [dbo].[adm_moneda] (
> [mone_codigo]
> )
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_e
st_movcom]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint
& #91;fk_r_estcier_movcom]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_i
tmpr_movcom]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_m
nd_movcom]
> GO
> ****************************************
*************************
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
> news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
table,[vbcol=seagreen]
(your[vbcol=seagreen]
[PRIMARY][vbcol=seagreen]
>|||It looks like Mike has this handled, but I'd like to interject here.
First, histogram statistics are only stored for the first attribute in any
index, composite or not. That means the QA selectivity evaluation is based
on the leading attribute...alone, regardless if it is singlet or composite.
Second, since SQL Server version 7.0, the Query Optimizer can use index
intersection to make better search conditions...viz., you are not limited to
using a single index in a query expression. Either an index is highly
selective, or it is not. Having multiple single-attribute indexes is more
desirble than fewer composite indexes. And, never should you require
overlapping indexes; SQL Server will only choose one based on selectivity,
the others would only be a waste of space.
That being said, after a set of well chosen, single-attribute indexes have
been created, and every table--and I mean EVERY table--should have a
clustered index defined, then a few--VERY FEW--customized, query targeted,
mission-critical, composite indexes can be built, but only after proving the
multiple single-attribute indexes are not producing sufficient response.
Everything else will only produce storage and processing overhead whenever
you do CRUD or reorg operations.
Sincerely,
Anthony Thomas
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:u0fD$1c6EHA.208@.TK2MSFTNGP12.phx.gbl...
Hello Mike,
Are you talking about getting rid of the one which contains the others? Any
help is very much appreciated.
this is the script:
****************************************
******************************
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id, N'IsUse
rTable')
= 1)
drop table [dbo].[prd_movcomision]
GO
CREATE TABLE [dbo].[prd_movcomision] (
[movc_codigo] [numeric](18, 0) NOT NULL ,
[movc_fecmov] [datetime] NOT NULL ,
[pers_rutcia] [numeric](9, 0) NULL ,
[infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[doco_numero] [numeric](18, 0) NULL ,
[poli_codigo] [numeric](18, 0) NULL ,
[itpo_codigo] [numeric](18, 0) NULL ,
[esta_codigo] [numeric](4, 0) NULL ,
[mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[prop_codigo] [numeric](18, 0) NULL ,
[itpr_codigo] [numeric](18, 0) NULL ,
[cone_codigo] [numeric](18, 0) NULL ,
[prop_fecdocum] [datetime] NULL ,
[prop_fecemision] [datetime] NULL ,
[prop_fecinicio] [datetime] NULL ,
[itpr_fecinicio] [datetime] NULL ,
[poli_fecemision] [datetime] NULL ,
[poli_fecinicio] [datetime] NULL ,
[itpo_fecinicio] [datetime] NULL ,
[movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[movc_periodo] [numeric](6, 0) NULL ,
[movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL
,
[movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[movc_fijo] [numeric](18, 4) NULL ,
[movc_variable] [numeric](7, 3) NULL ,
[movc_prima] [numeric](18, 4) NULL ,
[movc_montomo] [numeric](18, 4) NOT NULL ,
[movc_montomc] [numeric](18, 4) NULL ,
[movc_fecvalor] [datetime] NULL ,
[movc_feccierre] [datetime] NULL ,
[movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cier_correlativo] [numeric](18, 0) NULL ,
[cuot_codigo] [numeric](18, 0) NULL ,
[cuot_numero] [numeric](4, 0) NULL ,
[movc_fecmeta] [datetime] NULL ,
[movc_primaneta] [t_dom_monto] NULL ,
[movc_fecpagocomis] [t_dom_fecha] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
(
[movc_codigo]
) ON [PRIMARY]
GO
CREATE INDEX [AK_prd_movcomision] ON
[dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia], &#
91;movc_concepto],
[cone_codigo], [poli_codigo]) ON [PRIMARY]
GO
CREATE INDEX [Ai_prd_movcomision2] ON
[dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [i
tpo_codigo]) ON
[PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision] ON
[dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON
1;PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision1] ON
[dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision2] ON
[dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
[dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
GO
ALTER TABLE [dbo].[prd_movcomision] ADD
CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
(
[cone_codigo]
) REFERENCES [dbo].[prd_comnegocio] (
[cone_codigo]
),
CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
(
[cuot_codigo],
[cuot_numero]
) REFERENCES [dbo].[prd_cuota] (
[cuot_codigo],
[cuot_numero]
),
CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
(
[pers_rutcia],
[infi_codigo],
[doco_numero]
) REFERENCES [dbo].[prd_doccomision] (
[pers_rutcia],
[infi_codigo],
[doco_numero]
),
CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
(
[esta_codigo]
) REFERENCES [dbo].[adm_estado] (
[esta_codigo]
),
CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
(
[movc_periodo],
[cier_tipo],
[cier_correlativo]
) REFERENCES [dbo].[prd_estadocierre] (
[cier_periodo],
[cier_tipo],
[cier_correlativo]
),
CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
(
[prop_codigo],
[itpr_codigo]
) REFERENCES [dbo].[vta_itmpropuesta] (
[prop_codigo],
[itpr_codigo]
),
CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
(
[mone_codigo]
) REFERENCES [dbo].[adm_moneda] (
[mone_codigo]
)
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est
_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est
cier_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_itm
pr_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_mnd
_movcom]
GO
****************************************
*************************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
> Hi
> I would get rid or A and B. If there is no clustered index on that table,
> make C clustered too, assuming it has good selectivity.
> Without the data types, it is not possible to tell and be 100% sure (your
> attachment gets blocked by Outlook Express)
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
> news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> [itpo_codigo])
> once
> too
>|||Jorge Luzarraga Castro (jluzarraga@.fidens.cl) writes:
> Ive got a table called 'prd_movcomision' (Ive attached a script with its
> structure) which has several indexes as you can see. There are several
> indexes which are contained in others. For example:
> CREATE INDEX [A] ON [dbo].[prd_movcomision]([pers_rutcia]
, [poli_codigo],
> [itpo_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [B] ON [dbo].[prd_movcomision]([poli_codigo
],
> [itpo_codigo])
> ON [PRIMARY]
> GO
> CREATE INDEX [C] ON [dbo].[prd_movcomision]([prop_codigo
]) ON [PRIMARY]
> GO
>
> Given the previous indexes, [B] is contained within [A] and &#
91;C] is
> contained within [B]. The question is if this is really neccessary or
> once I have an index A the other indexes (B and C) are redundant.
There are no redudant indexes in your table. There may be unused indexes,
that I can't tell, because that depends on your queries.
While the columns index B is included in index A, a query like:
SELECT * FROM tbl WHERE poli_coidgo = @.val1 AND itpo_codigo = @.val2
cannot seek index A to find any rows. It can scan the index, though.
The difference here is that "seek" means that SQL Server finds the
value by walking the fast way through the index tree. "scan" means
that SQL Server searches all leaf pages of the index, which is considerably
slower. In reality, SQL Server will probably scan the entire table instead,
which is even slower. (As a scan. Beside the scanning the index, SQL Server
will also have to access data pages, and with many hits, that can be more
data-pages access than a table scan.)
The same goes for index C, assuming that you mean to have itpo_codigo hear.
So the important issue here, is whether you actually have any queries for
which these indexes are useful.

> Im having too many problems with the size of the database, it is
> growing too fast.
I don't think dropping indexes is a very good help to address that. In
such case it's better to examine whether there is data that is inserted
multiple times, data which is not removed as it should be etc.
Dropping an index can conserve some space - but it can also kill
performance, if there is a query which needs the index.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Redundant indexes

When exactly is an index considered redundant? I've read some general
guidelines about composite indexes, but I'm trying to find some
definitive answers or documentation. For example (fixed width font
will work best):
Table 1 existing indexes:
Name Type Columns
PK1 clustered, unique, PK A
IX1 nonclustered B
IX2 nonclustered ACDE
IX3 nonclustered ACBF
Proposed new index:
IX4 nonclustered AE
Is the proposed new index IX4 redundant? The two columns are already
included in IX2, but they're not adjacent in IX2. Does that matter?
Are any of the existing indexes unneeded?
Table 2 existing indexes:
Name Type Columns
PK1 nonclustered, unique, PK A
IX1 clustered B
IX2 nonclustered C
IX3 nonclustered D
IX4 nonclustered DBEFGHIJKLMNOPQR
Proposed new indexes:
IX5 nonclustered CD
IX6 nonclustered BD
For table 2, are the proposed new indexes redundant? The columns are
already indexed individually, so would those be used? There's also an
existing covering index IX4 that includes two columns from the proposed
index IX6, but in a different order. Could IX4 be used in place of IX6?stavros wrote:
> When exactly is an index considered redundant? I've read some general
> guidelines about composite indexes, but I'm trying to find some
> definitive answers or documentation.
An index I is definitive redundant if its columns C1..Cn are the leading
columns of another index Y on the same table that has the same storage
characteristics (i.e. both non clustered). If clustering is different
then it depends on the access patterns to the table which is to be
retained etc.

> For example (fixed width font
> will work best):
> Table 1 existing indexes:
> Name Type Columns
> PK1 clustered, unique, PK A
> IX1 nonclustered B
> IX2 nonclustered ACDE
> IX3 nonclustered ACBF
> Proposed new index:
> IX4 nonclustered AE
> Is the proposed new index IX4 redundant?
Probably not.

> The two columns are already
> included in IX2, but they're not adjacent in IX2. Does that matter?
Yes. I believe newer Oracle versions can do a skip scan when scanning a
range of the index but I doubt that SQL Server can do it. You'd have to
check with the docs for the details.

> Are any of the existing indexes unneeded?
> Table 2 existing indexes:
> Name Type Columns
> PK1 nonclustered, unique, PK A
> IX1 clustered B
> IX2 nonclustered C
> IX3 nonclustered D
> IX4 nonclustered DBEFGHIJKLMNOPQR
Theoretically IX3 is redundant - although it might be used because it
needs less space then IX4.

> Proposed new indexes:
> IX5 nonclustered CD
> IX6 nonclustered BD
> For table 2, are the proposed new indexes redundant? The columns are
> already indexed individually, so would those be used? There's also an
> existing covering index IX4 that includes two columns from the proposed
> index IX6, but in a different order. Could IX4 be used in place of IX6?
Depends. B has higher selectivity IX6 might have an advantage over IX4
- especially since IX4 is so large.
As you see even redundant indexes can make sense. It all depends...
robert|||On 30 Oct 2006 13:01:49 -0800, "stavros" <stavros@.mailinator.com>
wrote:

> Table 2 existing indexes:
> Name Type Columns
> PK1 nonclustered, unique, PK A
> IX1 clustered B
> IX2 nonclustered C
> IX3 nonclustered D
> IX4 nonclustered DBEFGHIJKLMNOPQR
> Proposed new indexes:
> IX5 nonclustered CD
> IX6 nonclustered BD
Neither of the new indexes are redundant, but each of them makes an
existing index redundant.
Roy|||Roy Harvey wrote:
> On 30 Oct 2006 13:01:49 -0800, "stavros" <stavros@.mailinator.com>
> wrote:
>
> Neither of the new indexes are redundant, but each of them makes an
> existing index redundant.
> Roy
But since the columns referenced in the new indexes are already indexed
individually, doesn't that make the new indexes redundant?
http://www.sql-server-performance.c...ite_indexes.asp says that
"SQL Server has the ability to join two or more individual indexes and
intersect them, just as if you were using a composite index", and also
implies that single-column indexes are generally preferred.|||So who are you going to believe? ;-)
"SQL Server has the ability to join two or more individual indexes and
intersect them, just as if you were using a composite index"
... "just as if" is not true.
SQL Server can join two or more individual indexes together, but there is
more internal work involved to do so, much more than if you already have a
composite index on the columns.
"single-column indexes are generally preferred"
Preferred by whom? Maybe just by the writers of this article. Multi column
indexes have much greater chance to be used as covering indexes, which avoid
table bookmark lookups and are generally preferred.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"stavros" <stavros@.mailinator.com> wrote in message
news:1162249345.592630.63930@.m7g2000cwm.googlegroups.com...
> Roy Harvey wrote:
> But since the columns referenced in the new indexes are already indexed
> individually, doesn't that make the new indexes redundant?
> http://www.sql-server-performance.c...ite_indexes.asp says that
> "SQL Server has the ability to join two or more individual indexes and
> intersect them, just as if you were using a composite index", and also
> implies that single-column indexes are generally preferred.
>|||Robert Klemme wrote:
> An index I is definitive redundant if its columns C1..Cn are the leading
> columns of another index Y on the same table that has the same storage
> characteristics (i.e. both non clustered). If clustering is different
> then it depends on the access patterns to the table which is to be
> retained etc.
>
Thanks for the clear answer, that helps.|||Another incorrect statement from the same article:
"if you create a composite index, such as "City, State", then a query
such as "WHERE City = 'Springfield'" will use the index, but the query
"WHERE STATE = 'MO'" will not use the index."
;)|||Agreed. Should be:
"but the query "WHERE STATE = 'MO'" will not use the index to search for the
rows that qualifies, it
can be used to scan for the rows."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1162259023.159887.170330@.b28g2000cwb.googlegroups.com...
> Another incorrect statement from the same article:
> "if you create a composite index, such as "City, State", then a query
> such as "WHERE City = 'Springfield'" will use the index, but the query
> "WHERE STATE = 'MO'" will not use the index."
> ;)
>|||On 31.10.2006 00:13, stavros wrote:
> Robert Klemme wrote:
> Thanks for the clear answer, that helps.
You're welcome. But keep in mind that redundant != superfluous as I
tried to point out for the case of the huge index.
Regards
robert|||stavros wrote:
> Roy Harvey wrote:
> But since the columns referenced in the new indexes are already indexed
> individually, doesn't that make the new indexes redundant? [snip]
That is why "redundant" is not all that useful when you are trying to
maximize query performance. I mean, all but the clustered index are by
definition redundant, because their data is already in the table, and
can be retrieved with a table scan or clustered index scan.
Index IX5 will make IX2 redundant. But if column C is very narrow (let's
say a smallint) and D very wide (let's say char(150)), then finding rows
in index IX5 probably requires more reads then finding them in IX2. It
is up to you to decide which performance is satisfactory, and balance
that against the cost that each additional index adds to
inserts/updates/deletes.
Gert-Jan

Redundant indexes

When exactly is an index considered redundant? I've read some general
guidelines about composite indexes, but I'm trying to find some
definitive answers or documentation. For example (fixed width font
will work best):
Table 1 existing indexes:
Name Type Columns
PK1 clustered, unique, PK A
IX1 nonclustered B
IX2 nonclustered ACDE
IX3 nonclustered ACBF
Proposed new index:
IX4 nonclustered AE
Is the proposed new index IX4 redundant? The two columns are already
included in IX2, but they're not adjacent in IX2. Does that matter?
Are any of the existing indexes unneeded?
Table 2 existing indexes:
Name Type Columns
PK1 nonclustered, unique, PK A
IX1 clustered B
IX2 nonclustered C
IX3 nonclustered D
IX4 nonclustered DBEFGHIJKLMNOPQR
Proposed new indexes:
IX5 nonclustered CD
IX6 nonclustered BD
For table 2, are the proposed new indexes redundant? The columns are
already indexed individually, so would those be used? There's also an
existing covering index IX4 that includes two columns from the proposed
index IX6, but in a different order. Could IX4 be used in place of IX6?stavros wrote:
> When exactly is an index considered redundant? I've read some general
> guidelines about composite indexes, but I'm trying to find some
> definitive answers or documentation.
An index I is definitive redundant if its columns C1..Cn are the leading
columns of another index Y on the same table that has the same storage
characteristics (i.e. both non clustered). If clustering is different
then it depends on the access patterns to the table which is to be
retained etc.
> For example (fixed width font
> will work best):
> Table 1 existing indexes:
> Name Type Columns
> PK1 clustered, unique, PK A
> IX1 nonclustered B
> IX2 nonclustered ACDE
> IX3 nonclustered ACBF
> Proposed new index:
> IX4 nonclustered AE
> Is the proposed new index IX4 redundant?
Probably not.
> The two columns are already
> included in IX2, but they're not adjacent in IX2. Does that matter?
Yes. I believe newer Oracle versions can do a skip scan when scanning a
range of the index but I doubt that SQL Server can do it. You'd have to
check with the docs for the details.
> Are any of the existing indexes unneeded?
> Table 2 existing indexes:
> Name Type Columns
> PK1 nonclustered, unique, PK A
> IX1 clustered B
> IX2 nonclustered C
> IX3 nonclustered D
> IX4 nonclustered DBEFGHIJKLMNOPQR
Theoretically IX3 is redundant - although it might be used because it
needs less space then IX4.
> Proposed new indexes:
> IX5 nonclustered CD
> IX6 nonclustered BD
> For table 2, are the proposed new indexes redundant? The columns are
> already indexed individually, so would those be used? There's also an
> existing covering index IX4 that includes two columns from the proposed
> index IX6, but in a different order. Could IX4 be used in place of IX6?
Depends. B has higher selectivity IX6 might have an advantage over IX4
- especially since IX4 is so large.
As you see even redundant indexes can make sense. It all depends...
robert|||On 30 Oct 2006 13:01:49 -0800, "stavros" <stavros@.mailinator.com>
wrote:
> Table 2 existing indexes:
> Name Type Columns
> PK1 nonclustered, unique, PK A
> IX1 clustered B
> IX2 nonclustered C
> IX3 nonclustered D
> IX4 nonclustered DBEFGHIJKLMNOPQR
> Proposed new indexes:
> IX5 nonclustered CD
> IX6 nonclustered BD
Neither of the new indexes are redundant, but each of them makes an
existing index redundant.
Roy|||Roy Harvey wrote:
> On 30 Oct 2006 13:01:49 -0800, "stavros" <stavros@.mailinator.com>
> wrote:
> > Table 2 existing indexes:
> > Name Type Columns
> > PK1 nonclustered, unique, PK A
> > IX1 clustered B
> > IX2 nonclustered C
> > IX3 nonclustered D
> > IX4 nonclustered DBEFGHIJKLMNOPQR
> >
> > Proposed new indexes:
> > IX5 nonclustered CD
> > IX6 nonclustered BD
> Neither of the new indexes are redundant, but each of them makes an
> existing index redundant.
> Roy
But since the columns referenced in the new indexes are already indexed
individually, doesn't that make the new indexes redundant?
http://www.sql-server-performance.com/composite_indexes.asp says that
"SQL Server has the ability to join two or more individual indexes and
intersect them, just as if you were using a composite index", and also
implies that single-column indexes are generally preferred.|||Robert Klemme wrote:
> An index I is definitive redundant if its columns C1..Cn are the leading
> columns of another index Y on the same table that has the same storage
> characteristics (i.e. both non clustered). If clustering is different
> then it depends on the access patterns to the table which is to be
> retained etc.
>
Thanks for the clear answer, that helps.|||So who are you going to believe? ;-)
"SQL Server has the ability to join two or more individual indexes and
intersect them, just as if you were using a composite index"
... "just as if" is not true.
SQL Server can join two or more individual indexes together, but there is
more internal work involved to do so, much more than if you already have a
composite index on the columns.
"single-column indexes are generally preferred"
Preferred by whom? Maybe just by the writers of this article. Multi column
indexes have much greater chance to be used as covering indexes, which avoid
table bookmark lookups and are generally preferred.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"stavros" <stavros@.mailinator.com> wrote in message
news:1162249345.592630.63930@.m7g2000cwm.googlegroups.com...
> Roy Harvey wrote:
>> On 30 Oct 2006 13:01:49 -0800, "stavros" <stavros@.mailinator.com>
>> wrote:
>> > Table 2 existing indexes:
>> > Name Type Columns
>> > PK1 nonclustered, unique, PK A
>> > IX1 clustered B
>> > IX2 nonclustered C
>> > IX3 nonclustered D
>> > IX4 nonclustered DBEFGHIJKLMNOPQR
>> >
>> > Proposed new indexes:
>> > IX5 nonclustered CD
>> > IX6 nonclustered BD
>> Neither of the new indexes are redundant, but each of them makes an
>> existing index redundant.
>> Roy
> But since the columns referenced in the new indexes are already indexed
> individually, doesn't that make the new indexes redundant?
> http://www.sql-server-performance.com/composite_indexes.asp says that
> "SQL Server has the ability to join two or more individual indexes and
> intersect them, just as if you were using a composite index", and also
> implies that single-column indexes are generally preferred.
>|||Another incorrect statement from the same article:
"if you create a composite index, such as "City, State", then a query
such as "WHERE City = 'Springfield'" will use the index, but the query
"WHERE STATE = 'MO'" will not use the index."
;)|||Agreed. Should be:
"but the query "WHERE STATE = 'MO'" will not use the index to search for the rows that qualifies, it
can be used to scan for the rows."
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1162259023.159887.170330@.b28g2000cwb.googlegroups.com...
> Another incorrect statement from the same article:
> "if you create a composite index, such as "City, State", then a query
> such as "WHERE City = 'Springfield'" will use the index, but the query
> "WHERE STATE = 'MO'" will not use the index."
> ;)
>|||On 31.10.2006 00:13, stavros wrote:
> Robert Klemme wrote:
>> An index I is definitive redundant if its columns C1..Cn are the leading
>> columns of another index Y on the same table that has the same storage
>> characteristics (i.e. both non clustered). If clustering is different
>> then it depends on the access patterns to the table which is to be
>> retained etc.
> Thanks for the clear answer, that helps.
You're welcome. But keep in mind that redundant != superfluous as I
tried to point out for the case of the huge index.
Regards
robert|||stavros wrote:
> Roy Harvey wrote:
> > On 30 Oct 2006 13:01:49 -0800, "stavros" <stavros@.mailinator.com>
> > wrote:
> >
> > > Table 2 existing indexes:
> > > Name Type Columns
> > > PK1 nonclustered, unique, PK A
> > > IX1 clustered B
> > > IX2 nonclustered C
> > > IX3 nonclustered D
> > > IX4 nonclustered DBEFGHIJKLMNOPQR
> > >
> > > Proposed new indexes:
> > > IX5 nonclustered CD
> > > IX6 nonclustered BD
> >
> > Neither of the new indexes are redundant, but each of them makes an
> > existing index redundant.
> >
> > Roy
> But since the columns referenced in the new indexes are already indexed
> individually, doesn't that make the new indexes redundant? [snip]
That is why "redundant" is not all that useful when you are trying to
maximize query performance. I mean, all but the clustered index are by
definition redundant, because their data is already in the table, and
can be retrieved with a table scan or clustered index scan.
Index IX5 will make IX2 redundant. But if column C is very narrow (let's
say a smallint) and D very wide (let's say char(150)), then finding rows
in index IX5 probably requires more reads then finding them in IX2. It
is up to you to decide which performance is satisfactory, and balance
that against the cost that each additional index adds to
inserts/updates/deletes.
Gert-Jan

Reducing column size

I want to reduce the length of a column in one of my database tables
from varchar(2048) to varchar(900), basically so I can create an index
on the column to gain much improved performance.
To do this, I am executing the following statement:
ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
Executing this in query analyzer results in the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
If I make this change via Enterprise Manager, it appears as a warning
and I can click OK to ignore and make the change. However, in query
analyzer this is treated as an error and the statement is not
performed.
What i want is to basically ignore this error and make the change
regardless. Is this possible?It basically tell you the data length in that column is more then varchar(900).
You can reduce the length of a column of the length less the length of the
data in that column.
review the data in that column which more the 900 char
Cheers
"jasonatkins2001@.hotmail.com" wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
>|||Notice that you have data that is longer then 900 bytes and by forcing
the modification you will lose data.
The reason that the EM does it and the alter statement in the QA
doesn't work is that the EM doesn't really runs alter table statement.
Instead the EM creates a new table and then runs insert select from the
source table to the new table. Then it drops the old table, renames
the new table, and creates the table constraints and the references
from other tables to the new table.
Also notice that even if you'll have an index on a column that has 900
bytes, the index won't be very effective.
Adi
jasonatkins2001@.hotmail.com wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?