Hi
Can someone confirm that the refreshRow method on a ResultSet class does not
work and that it is a bug in the driver. I've tested the same code with
different drivers and it works ok... unless Microsoft has implemented the
functionallity different, can someone tell me what that is.....
Thanks
Carel
| Thread-Topic: refreshRow not working
| thread-index: AcUqLYcVVyNsCTdOQQKygCm51b/9Fg==
| X-WBNR-Posting-Host: 195.212.29.75
| From: "=?Utf-8?B?Q2FyZWwgZHUgdG9pdA==?="
<Careldutoit@.discussions.microsoft.com>
| Subject: refreshRow not working
| Date: Wed, 16 Mar 2005 05:39:07 -0800
| Lines: 8
| Message-ID: <D1611FB1-186E-45AD-B1F0-7F4B39D2178A@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.jdbcdriver:6758
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi
| Can someone confirm that the refreshRow method on a ResultSet class does
not
| work and that it is a bug in the driver. I've tested the same code with
| different drivers and it works ok... unless Microsoft has implemented
the
| functionallity different, can someone tell me what that is.....
|
| Thanks
| Carel
|
Hello,
The JDBC spec says that ResultSet.refreshRow() is not supported for
ResultSet objects that are type TYPE_FORWARD_ONLY, and does nothing for
those that are type TYPE_SCROLL_INSENSITIVE. This means that it can only
be used with TYPE_SCROLL_SENSITIVE. Since the Microsoft JDBC driver does
not support scroll sensitive ResultSets, the refreshRow() method is
behaving as expected.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
sql
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
Refresh problem after installing MSDE
Hi,
After installing a MSDE instance, in the Servcie Manager I don't see the
server I've just installed. But if I enter the name of the instance in
Service Manager, I can acces it and then start/pause/stop it. If I reboot
the PC, the server will not start even if the checkbox in Service Manager is
check. In Service Manager, I see no server again. Seems the system can't see
the server but it is here and I can access it manually since I know the
name.
I have found nothing avout this problem for the moment.
hi David,
David G. wrote:
> Hi,
> After installing a MSDE instance, in the Servcie Manager I don't see
> the server I've just installed. But if I enter the name of the
> instance in Service Manager, I can acces it and then start/pause/stop
> it. If I reboot the PC, the server will not start even if the
> checkbox in Service Manager is check. In Service Manager, I see no
> server again. Seems the system can't see the server but it is here
> and I can access it manually since I know the name.
> I have found nothing avout this problem for the moment.
please have a look at
http://support.microsoft.com/default...b;EN-US;814132
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||After reading this, the Sql Server is "started" and "automatically" in the
services. But in the systray, the Service Manager doesn't have a green
arrow. It's just full white.
If I open the Service Manager, type the Sql Server instance name and hit
the button "refresh services ..." then I can see the instance. I must do
this each time after I boot the computer.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> a crit dans le message de
news: 47dpi7Ff36s5U1@.individual.net...
> hi David,
> please have a look at
> http://support.microsoft.com/default...b;EN-US;814132
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
sql
After installing a MSDE instance, in the Servcie Manager I don't see the
server I've just installed. But if I enter the name of the instance in
Service Manager, I can acces it and then start/pause/stop it. If I reboot
the PC, the server will not start even if the checkbox in Service Manager is
check. In Service Manager, I see no server again. Seems the system can't see
the server but it is here and I can access it manually since I know the
name.
I have found nothing avout this problem for the moment.
hi David,
David G. wrote:
> Hi,
> After installing a MSDE instance, in the Servcie Manager I don't see
> the server I've just installed. But if I enter the name of the
> instance in Service Manager, I can acces it and then start/pause/stop
> it. If I reboot the PC, the server will not start even if the
> checkbox in Service Manager is check. In Service Manager, I see no
> server again. Seems the system can't see the server but it is here
> and I can access it manually since I know the name.
> I have found nothing avout this problem for the moment.
please have a look at
http://support.microsoft.com/default...b;EN-US;814132
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||After reading this, the Sql Server is "started" and "automatically" in the
services. But in the systray, the Service Manager doesn't have a green
arrow. It's just full white.
If I open the Service Manager, type the Sql Server instance name and hit
the button "refresh services ..." then I can see the instance. I must do
this each time after I boot the computer.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> a crit dans le message de
news: 47dpi7Ff36s5U1@.individual.net...
> hi David,
> please have a look at
> http://support.microsoft.com/default...b;EN-US;814132
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
sql
Wednesday, March 7, 2012
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
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
Subscribe to:
Posts (Atom)