Regarding indexes (I think)

I have an issue where i can do the following
Select count(*) from table and it returns 181,000
Select count(*) from table where ncolumn is not null and it returns 174,000
Select count(*) from table where ncolumn is null and it returns 153,000
Now, those numbers should at up to the count of the top one,
I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
dbreindex with the same problem.
Anyone have any suggestions?Try,
DBCC UPDATEUSAGE ('db_name', 'table_name')
go
select count(*), count(case when ncolumn is not null then 1 end),
count(ncolumn is null then 1 end)
from table
go
AMB
"Gary" wrote:

> I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns 174,00
0
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>
>|||Correction,
DBCC UPDATEUSAGE ('db_name', 'table_name')
go
select count(*), count(case when ncolumn is not null then 1 end),
count(case when ncolumn is null then 1 end)
from table
go
AMB
"Alejandro Mesa" wrote:
> Try,
> DBCC UPDATEUSAGE ('db_name', 'table_name')
> go
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> go
>
> AMB
> "Gary" wrote:
>|||Are there inserts into the table between each select?
"Gary" <clgary@.yahoo.com> wrote in message
news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
> I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>|||Thanks for the help, it sort of worked
Select count(*) from table and it returns 181,000
Select count(*) from table where ncolumn is not null and it returns 174,000
Select count(*) from table where ncolumn is null and it returns 153,000
Same results there, however,
select count(*), count(case when ncolumn is not null then 1 end),
count(ncolumn is null then 1 end)
from table
returns
181,000 159,000 22,000
which is correct,.but I'm still not sure whats going on there.
Another poster I saw asked if there were inserts happening during the
selects, and its not.
So, I'm still baffled. Something else to note, even if i run them
individually, instead of all 3 as a batch in QA, it still returns the wrong
count.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:61BE7216-D2F7-435D-B1E4-656E4D683C5E@.microsoft.com...
> Try,
> DBCC UPDATEUSAGE ('db_name', 'table_name')
> go
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> go
>
> AMB
> "Gary" wrote:
>
174,000|||If you are running parallel queries:
http://support.microsoft.com/kb/814509/en-us
http://support.microsoft.com/default.aspx/kb/838166 (for getting the 878
build).
S. L.
"Gary" <clgary@.yahoo.com> wrote in message
news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
>I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
> 174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>|||Perhaps uncommitted inserts or updates are being held open by other
processes and your current transaction isolation level allows your query to
include "dirty reads". Sometimes a large number of updates (100,000s) can
take 1/2 hour or more to rollback if a batch transaction fails. Try running
the following, it will lock any records as it reads and will not include
"dirty reads".
Run sp_lock to see what other processes may have records locked. Also, if
the queries seems to take longer than usual to run using this locking
method, then you can use sp_who2 to determine if your SPID is blocked by
another process.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Select count(*) from table
Select count(*) from table where ncolumn is not null
Select count(*) from table where ncolumn is null
"Gary" <clgary@.yahoo.com> wrote in message
news:uDpR8ISGFHA.4004@.tk2msftngp13.phx.gbl...
> Thanks for the help, it sort of worked
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Same results there, however,
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> returns
> 181,000 159,000 22,000
> which is correct,.but I'm still not sure whats going on there.
> Another poster I saw asked if there were inserts happening during the
> selects, and its not.
> So, I'm still baffled. Something else to note, even if i run them
> individually, instead of all 3 as a batch in QA, it still returns the
wrong
> count.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:61BE7216-D2F7-435D-B1E4-656E4D683C5E@.microsoft.com...
> 174,000
153,000
>|||Thanks,
That seems to do the trick
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OOqE3YSGFHA.2296@.TK2MSFTNGP15.phx.gbl...
> If you are running parallel queries:
> http://support.microsoft.com/kb/814509/en-us
> http://support.microsoft.com/default.aspx/kb/838166 (for getting the 878
> build).
> S. L.
> "Gary" <clgary@.yahoo.com> wrote in message
> news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
>sql

Redundant Indexes?

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], [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 [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
begin 666 prd_movcomision.sql
M:68@.97AI<W1S("AS96QE8W0@.*B!F<F]M(&1B;RYS>7-O8FIE8W1S('=H97)E
M(&ED(#T@.;V)J96-T7VED*$XG6V1B;UTN6W!R9%]M;W9C;VUI<VEO;ETG*2!A
M;F0@.3T)*14-44%)/4$525%DH:60L($XG27-5<V5R5&%B;&4G*2 ](#$I#0ID
M<F]P('1A8FQE(%MD8F]=+EMP<F1?;6]V8V]M:7-I;VY=#0I'3PT*#0I#4D5!
M5$4@.5$%"3$4@.6V1B;UTN6W!R9%]M;W9C;VUI<VEO;ET@.* T*"5MM;W9C7V-O
M9&EG;UT@.6VYU;65R:6-=*#$X+" P*2!.3U0@.3E5,3" L#0H)6VUO=F-?9F5C
M;6]V72!;9&%T971I;65=($Y/5"!.54Q,("P-"@.E;<&5R<U]R=71C:6%=(%MN
M=6UE<FEC72@.Y+" P*2!.54Q,("P-"@.E;:6YF:5]C;V1I9V]=(%MV87)C:&%R
M72 H-2D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y5
M3$P@.+ T*"5MD;V-O7VYU;65R;UT@.6VYU;65R:6-=*#$X+" P*2!.54Q,("P-
M"@.E;<&]L:5]C;V1I9V]=(%MN=6UE<FEC72@.Q."P@.,"D@.3E5,3" L#0H)6VET
M<&]?8V]D:6=O72!;;G5M97)I8UTH,3@.L(# I($Y53$P@.+ T*"5ME<W1A7V-O
M9&EG;UT@.6VYU;65R:6-=*#0L(# I($Y53$P@.+ T*"5MM;VYE7V-O9&EG;UT@.
M6V-H87)=("@.S*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3D]4($Y53$P@.+ T*"5MP<F]P7V-O9&EG;UT@.6VYU;65R:6-=*#$X+" P
M*2!.54Q,("P-"@.E;:71P<E]C;V1I9V]=(%MN=6UE<FEC72@.Q."P@.,"D@.3E5,
M3" L#0H)6V-O;F5?8V]D:6=O72!;;G5M97)I8UTH,3@.L(# I($Y53$P@.+ T*
M"5MP<F]P7V9E8V1O8W5M72!;9&%T971I;65=($Y53$P@.+ T*"5MP<F]P7V9E
M8V5M:7-I;VY=(%MD871E=&EM95T@.3E5,3" L#0H)6W!R;W!?9F5C:6YI8VEO
M72!;9&%T971I;65=($Y53$P@.+ T*"5MI='!R7V9E8VEN:6-I;UT@.6V1A=&5T
M:6UE72!.54Q,("P-"@.E;<&]L:5]F96-E;6ES:6]N72!;9&%T971I;65=($Y5
M3$P@.+ T*"5MP;VQI7V9E8VEN:6-I;UT@.6V1A=&5T:6UE72!.54Q,("P-"@.E;
M:71P;U]F96-I;FEC:6]=(%MD871E=&EM95T@.3E5,3" L#0H)6VUO=F-?8V]N
M8V5P=&]=(%MC:&%R72 H,2D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*"5MM;W9C7W1I<&]I;G1E<FU=(%MC:&%R72 H
M,2D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.
M+ T*"5MM;W9C7W!E<FEO9&]=(%MN=6UE<FEC72@.V+" P*2!.54Q,("P-"@.E;
M;6]V8U]M;W1N;W!A9V]=(%MV87)C:&%R72 H,C4U*2!#3TQ,051%(%-13%],
M871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H)6VUO=F-?:6YD8W)I
M=&5R:6]=(%MC:&%R72 H,2D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*"5MM;W9C7W1I<&]=(%MC:&%R72 H,2D@.0T],
M3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*"5MM
M;W9C7W5N:61A9&-O;5T@.6V-H87)=("@.Q*2!#3TQ,051%(%-13%],871I;C%?
M1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H)6VUO=F-?9FEJ;UT@.6VYU;65R
M:6-=*#$X+" T*2!.54Q,("P-"@.E;;6]V8U]V87)I86)L95T@.6VYU;65R:6-=
M*#<L(#,I($Y53$P@.+ T*"5MM;W9C7W!R:6UA72!;;G5M97)I8UTH,3@.L(#0I
M($Y53$P@.+ T*"5MM;W9C7VUO;G1O;6]=(%MN=6UE<FEC72@.Q."P@.-"D@.3D]4
M($Y53$P@.+ T*"5MM;W9C7VUO;G1O;6-=(%MN=6UE<FEC72@.Q."P@.-"D@.3E5,
M3" L#0H)6VUO=F-?9F5C=F%L;W)=(%MD871E=&EM95T@.3E5,3" L#0H)6VUO
M=F-?9F5C8VEE<G)E72!;9&%T971I;65=($Y53$P@.+ T*"5MM;W9C7V]B<V5R
M=F%C:6]N72!;=F%R8VAA<ET@.*#(U-2D@.0T],3$%412!344Q?3&%T:6XQ7T=E
M;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*"5MM;W9C7W1I<&]R86YG;UT@.6V-H
M87)=("@.Q*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.
M3E5,3" L#0H)6V-I97)?=&EP;UT@.6V-H87)=("@.Q*2!#3TQ,051%(%-13%],
M871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H)6V-I97)?8V]R<F5L
M871I=F]=(%MN=6UE<FEC72@.Q."P@.,"D@.3E5,3" L#0H)6V-U;W1?8V]D:6=O
M72!;;G5M97)I8UTH,3@.L(# I($Y53$P@.+ T*"5MC=6]T7VYU;65R;UT@.6VYU
M;65R:6-=*#0L(# I($Y53$P@.+ T*"5MM;W9C7V9E8VUE=&%=(%MD871E=&EM
M95T@.3E5,3" L#0H)6VUO=F-?<')I;6%N971A72!;=%]D;VU?;6]N=&]=($Y5
M3$P@.+ T*"5MM;W9C7V9E8W!A9V]C;VUI<UT@.6W1?9&]M7V9E8VAA72!.54Q,
M( T**2!/3B!;4%))34%265T-"D=/#0H-"D%,5$52(%1!0DQ%(%MD8F]=+EMP
M<F1?;6]V8V]M:7-I;VY=(%=)5$@.@.3D]#2$5#2R!!1$0@.#0H)0T].4U1204E.
M5"!;<&M?;6]V8V]M:7-I;VY=(%!224U!4ED@.2T59("!#3%535$52140@.#0H)
M* T*"0E;;6]V8U]C;V1I9V]=#0H)*2 @.3TX@.6U!224U!4EE=( T*1T\-"@.T*
M($-214%412 @.24Y$15@.@.6T%+7W!R9%]M;W9C;VUI<VEO;ET@.3TX@.6V1B;UTN
M6W!R9%]M;W9C;VUI<VEO;ETH6VUO=F-?=&EP;VEN=&5R;5TL(%MP97)S7W)U
M=&-I85TL(%MM;W9C7V-O;F-E<'1O72P@.6V-O;F5?8V]D:6=O72P@.6W!O;&E?
M8V]D:6=O72D@.3TX@.6U!224U!4EE=#0I'3PT*#0H@.0U)%051%("!)3 D1%6"!;
M06E?<')D7VUO=F-O;6ES:6]N,ET@.3TX@.6V1B;UTN6W!R9%]M;W9C;VUI<VEO
M;ETH6W!E<G-?<G5T8VEA72P@.6W!O;&E?8V]D:6=O72P@.6VET<&]?8V]D:6=O
M72D@.3TX@.6U!224U!4EE=#0I'3PT*#0H@.0U)%051%("!)3D1%6 "!;25A?<')D
M7VUO=F-O;6ES:6]N72!/3B!;9&)O72Y;<')D7VUO=F-O;6ES:6]N72A;<&]L
M:5]C;V1I9V]=+"!;:71P;U]C;V1I9V]=*2!/3B!;4%))34%265T-"D=/#0H-
M"B!#4D5!5$4@.($E.1$58(%M)6%]P<F1?;6]V8V]M:7-I;VXQ72!/3B!;9&)O
M72Y;<')D7VUO=F-O;6ES:6]N72A;<')O<%]C;V1I9V]=*2!/3B!;4%))34%2
M65T-"D=/#0H-"B!#4D5!5$4@.($E.1$58(%M)6%]P<F1?;6]V8V]M:7-I;VXR
M72!/3B!;9&)O72Y;<')D7VUO=F-O;6ES:6]N72A;<&]L:5]C;V1I9V]=*2!/
M3B!;4%))34%265T-"D=/#0H-"B\J*BHJ*BH@.5&AE(&EN9&5X(&-R96%T960@.
M8GD@.=&AE(&9O;&QO=VEN9R!S=&%T96UE;G0@.:7,@.9F]R(&EN=&5R;F%L('5S
M92!O;FQY+B J*BHJ*BHO#0HO*BHJ*BHJ($ET(&ES(&YO="!A(')E86P@.:6YD
M97@.@.8G5T(&5X:7-T<R!A<R!S=&%T:7-T:6-S(&]N;'DN("HJ*BHJ*B\-"FEF
M("A 0&UI8W)O<V]F='9E<G-I;VX@./B P># W,# P,# P("D-"D5814,@.*"=#
M4D5!5$4@.4U1!5$E35$E#4R!;4W1A=&ES=&EC7VUO=F-?9F5C<&%G;V-O;6ES
M72!/3B!;9&)O72Y;<')D7VUO=F-O;6ES:6]N72 H6VUO=F-?9F5C<&%G;V-O
M;6ES72D@.)RD-"D=/#0H-"D%,5$52(%1!0DQ%(%MD8F]=+EMP<F1?;6]V8V]M
M:7-I;VY=($%$1" -"@.E#3TY35%)!24Y4(%MF:U]R7V-O;F5?;6]V8V]M72!&
M3U)%24=.($M%62 -"@.DH#0H)"5MC;VYE7V-O9&EG;UT-"@.DI(%)%1D5214Y#
M15,@.6V1B;UTN6W!R9%]C;VUN96=O8VEO72 H#0H)"5MC;VYE7V-O9&EG;UT-
M"@.DI+ T*"4-/3E-44D%)3E0@.6V9K7W)?8W5O=%]M;W9C;VU=($9/4D5)1TX@.
M2T59( T*"2@.-"@.D)6V-U;W1?8V]D:6=O72P-"@.D)6V-U;W1?;G5M97)O70T*
M"2D@.4D5&15)%3D-%4R!;9&)O72Y;<')D7V-U;W1A72 H#0H)"5MC=6]T7V-O
M9&EG;UTL#0H)"5MC=6]T7VYU;65R;UT-"@.DI+ T*"4-/3E-44D%)3E0@.6V9K
M7W)?9&]C8V]M7VUO=F-O;5T@.1D]214E'3B!+15D@.#0H)* T*"0E;<&5R<U]R
M=71C:6%=+ T*"0E;:6YF:5]C;V1I9V]=+ T*"0E;9&]C;U]N=6UE<F]=#0H)
M*2!2149%4D5.0T53(%MD8F]=+EMP<F1?9&]C8V]M:7-I;VY=("@.-"@.D)6W!E
M<G-?<G5T8VEA72P-"@.D)6VEN9FE?8V]D:6=O72P-"@.D)6V1O8V]?;G5M97)O
M70T*"2DL#0H)0T].4U1204E.5"!;9FM?<E]E<W1?;6]V8V]M72!&3U)%24=.
M($M%62 -"@.DH#0H)"5ME<W1A7V-O9&EG;UT-"@.DI(%)%1D5214Y#15,@.6V1B
M;UTN6V%D;5]E<W1A9&]=("@.-"@.D)6V5S=&%?8V]D:6=O70T*"2DL#0H)0T].
M4U1204E.5"!;9FM?<E]E<W1C:65R7VUO=F-O;5T@.1D]214E'3B!+15D@.#0H)
M* T*"0E;;6]V8U]P97)I;V1O72P-"@.D)6V-I97)?=&EP;UTL#0H)"5MC:65R
M7V-O<G)E;&%T:79O70T*"2D@.4D5&15)%3D-%4R!;9&)O72Y;<')D7V5S=&%D
M;V-I97)R95T@.* T*"0E;8VEE<E]P97)I;V1O72P-"@.D)6V-I97)?=&EP;UTL
M#0H)"5MC:65R7V-O<G)E;&%T:79O70T*"2DL#0H)0T].4U1204E.5"!;9FM?
M<E]I=&UP<E]M;W9C;VU=($9/4D5)1TX@.2T59( T*"2@.-"@.D)6W!R;W!?8V]D
M:6=O72P-"@.D)6VET<')?8V]D:6=O70T*"2D@.4D5&15)%3D-%4R!;9&)O72Y;
M=G1A7VET;7!R;W!U97-T85T@.* T*"0E;<')O<%]C;V1I9V]=+ T*"0E;:71P
M<E]C;V1I9V]=#0H)*2P-"@.E#3TY35%)!24Y4(%MF:U]R7VUN9%]M;W9C;VU=
M($9/4D5)1TX@.2T59( T*"2@.-"@.D)6VUO;F5?8V]D:6=O70T*"2D@.4D5&15)%
M3D-%4R!;9&)O72Y;861M7VUO;F5D85T@.* T*"0E;;6]N95]C;V1I9V]=#0H)
M*0T*1T\-"@.T*86QT97(@.=&%B;&4@.6V1B;UTN6W!R9%]M;W9C;VUI<VEO;ET@.
M;F]C:&5C:R!C;VYS=')A:6YT(%MF:U]R7V5S=%]M;W9C;VU=#0I'3PT*#0IA
M;'1E<B!T86)L92!;9&)O72Y;<')D7VUO=F-O;6ES:6]N72!N;V-H96-K(&-O
M;G-T<F%I;G0@.6V9K7W)?97-T8VEE<E]M;W9C;VU=#0I'3PT*#0IA;'1E<B!T
M86)L92!;9&)O72Y;<')D7VUO=F-O;6ES:6]N72!N;V-H96-K(&-O;G-T<F%I
M;G0@.6V9K7W)?:71M<')?;6]V8V]M70T*1T\-"@.T*86QT97(@.=&%B;&4@.6V1B
M;UTN6W!R9%]M;W9C;VUI<VEO;ET@.;F]C:&5C:R!C;VYS=')A:6YT(%MF:U]R
47VUN9%]M;W9C;VU=#0I'3PT*#0H`
`
end
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],
[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 [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'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 NULL ,
[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 NULL
,
[movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS 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 [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_estcier_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_itmpr_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 NULL ,
> [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
NULL
> ,
> [movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS 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 [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_estcier_movcom][vbcol=seagreen]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_itmpr_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...
table,[vbcol=seagreen]
(your[vbcol=seagreen]
[PRIMARY]
>
|||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'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 NULL ,
[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 NULL
,
[movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS 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 [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_estcier_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_itmpr_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 [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/techinf...2000/books.asp

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