"select" only if theres a same record in the table

i have two tables A and B. relation is one to many for A. i want to select from A only if there are more than two records of A in table B and also checking some condition in table B. if the question's not very clear please let me know.

Substitute A_PK for whatever A's PK is and try the following:

SELECT *
FROM A
WHERE EXISTS
(SELECT B.A_PK
FROM B
WHERE B.A_PK = A.A_PK
GROUP BY B.A_PK
HAVING COUNT(*) >= 2)

"select into" vs "insert into" question

Hello,

I have read in a number of forums that when using temporary tables, it is best to create the tables first before inserting the records rather than simply executing a "select into" to ensure that locking issues are alleviated.

I have to say that I am one of those developers who has relied on using the "select into" statement - the main reason being that I have almost always found the "select into" statement to be much faster than an "insert into" statement.

Obviously, the number of users running queries simultaneously when scripts that use "select into" statements are executed is a big deciding factor on whether "select into" or "insert into" should be used.

I'd appreciate it if someone can shed more light on other factors that should be taken into consideration when deciding to use "select into" vs "insert into".

Thanks in advance for your time and reply.It used to be that SELECT INTO was a no-no because it was an unlogged transaction, after which database recoverability was questionable. With SQL Server 2000 I do not believe this is still an issue, although I have never found documentation clarifying the matter.

INSERT INTO is slower partly because it verifies unique keys and other data restraints as the data is entered. SELECT INTO does not do this, but if you are selecting from a table that already has these restrictions inforced then it is not an issue.

Also, in many cases a table variable is the fastest method to use. Check into them and see if they might be more appropriate for your application.

"Paging" in SP

I need to searches through records using Full Text Indexing. A search often
returns hundreds of records, and need the user to be able to page through
the result 50 at a time. I *ALSO* need the user to know the first and last
record of each page! (..On the client, i render a list of all pages in the
resultset, and add labels to each page, indicating to the user where in the
resultset the page is positioned).
I have therefor done an SP that inserts my initial search into an in-memory
table. After that i loop through all the records from my initial search, and
insert every 50th record into a different in-memory table. That allows me to
return a resultset with a) the first page of records (first 50 records), and
a table with a complete list of pages.
However, it has come to my attention that my loop through my initial
resultset is taking a lot of performance. So basically, my question is:
i have a large resultset in memory. I need to "sample" records from this,
eg. every 50th record. What would be the fastest way of doing this?
Cheers,
/Thomas.
You are using the wrong approach for this.
Here is some sample code that uses asp and getrows.
Approaches like the one you are using do not offer the best performance.
Check out
http://www.adopenstatic.com/experime...dsetpaging.asp
for more information.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Thomas Nielsen [AM Production A/S]" <jack_pot_dk@.h0tmail.com> wrote in
message news:%23rHETFErEHA.2796@.TK2MSFTNGP10.phx.gbl...
> I need to searches through records using Full Text Indexing. A search
often
> returns hundreds of records, and need the user to be able to page through
> the result 50 at a time. I *ALSO* need the user to know the first and last
> record of each page! (..On the client, i render a list of all pages in the
> resultset, and add labels to each page, indicating to the user where in
the
> resultset the page is positioned).
> I have therefor done an SP that inserts my initial search into an
in-memory
> table. After that i loop through all the records from my initial search,
and
> insert every 50th record into a different in-memory table. That allows me
to
> return a resultset with a) the first page of records (first 50 records),
and
> a table with a complete list of pages.
> However, it has come to my attention that my loop through my initial
> resultset is taking a lot of performance. So basically, my question is:
> i have a large resultset in memory. I need to "sample" records from this,
> eg. every 50th record. What would be the fastest way of doing this?
> Cheers,
> /Thomas.
>
begin 666 citrixNew.asp
M/"4-"F]P=&EO;B!E>'!L:6-I= T*0V]N<W0@.8615<V5#;&EE;G0@./2 S#0I#
M;VYS="!A9%5S95-E<G9E<B ](#(-"D-O;G-T(&%D3W!E;D9O<G=A<F1/;FQY
M(#T@., T*0V]N<W0@.861/<&5N4W1A=&EC(#T@.,PT*0V]N<W0@.861,;V-K4F5A
M9$]N;'D@./2 Q#0I#;VYS="!A9$-M9%-T;W)E9%!R;V,@./2 T#0I#;VYS="!A
M9%!A<F%M26YP=70@./2 Q#0I#;VYS="!A9%!A<F%M4F5T=7)N5F%L=64@./2 F
M2# P,#0-"D-O;G-T(&%D5F%R0VAA<B ](#(P, T*0V]N<W0@.861);G1E9V5R
M(#T@.,PT*0V]N<W0@.:5)O=W-097)086=E(#T@.,3 -"D-O;G-T('-H;W=";&%N
M:R ]("(F;F)S<#LB#0I#;VYS="!S:&]W3G5L;" ](").54Q,(@.T*#0I$:6T@.
M:5-T87)T+"!2971U<FY686QU92P@.5V5B2&ET<U%U97)Y+"!S=')39 6%R8V@.-
M"@.T*36%I;@.T*#0I3=6(@.36%I;B@.I#0H)1&EM(')S="P@.<U -13"P@.<T-O;FY3
M=')I;F<L<U-C<FEP=$YA;64L(%-E87)C:%]0:')A<V4L(%-E87)C:%]0:')A
M<V5?5')I;6UE9 T*"6E3=&%R=#U297%U97-T+E%U97)Y4W1R:6YG*")I4W1A
M<G0B*0T*"5-E87)C:%]0:')A<V4]4F5Q=65S="Y1=65R>5-T<FEN9R@.B4V5A
M<F-H7U!H<F%S92(I#0H))U-E87)C:%]0:')A<V5?5')I;6UE9#U297!L86-E
M*%-E87)C:%]0:')A<V4L(&-H<B@.S.2DL(&-H<B@.S.2DK8VAR*#,Y*2D-"@.DG
M4V5A<F-H7U!H<F%S95]4<FEM;65D/5)E<&QA8V4H4V5A<F-H7U!H<F%S95]4
M<FEM;65D+"!C:'(H,S0I+"!C:'(H,S0I*V-H<B@.S-"DI#0H):68@.:5-T87)T
M/2(B('1H96X-"@.D):5-T87)T/3 -"@.EE;F0@.:68-"@.T*"7-#;VYN4W1R:6YG
M(#T@.(E!R;W9I9&5R/5-13$],141"+C$[4&5R<VES="!396-U<FET>2!);F9O
M/49A;'-E.T1A=&$@.4V]U<F-E/2X[=7-E<B!)1#US83MP=V0]<V4Q8W5R92,[
M26YI=&EA;"!#871A;&]G/71E>'0[(@.T*#0H)5W)I=&5486)L94AE861E<B!S
M4V-R:7!T3F%M92P@.4V5A<F-H7U!H<F%S90T*#0H)<U-C<FEP=$YA;64@./2!2
M97%U97-T+E-E<G9E<E9A<FEA8FQE<R@.B4T-225!47TY!344B*0T*"6EF(%-E
M87)C:%]0:')A<V4@./#XB(B!T:&5N#0H)"5=R:71E5&%B;&5";V1Y(&E2;W=S
M4&5R4&%G92P@.:5-T87)T+"!S4V-R:7!T3F%M92P@.<T-O;FY3=')I;F<L('-3
M44PL(%-E87)C:%]0:')A<V4-"@.EE;F0@.:68-"@.E7<FET951A8FQE1F]O=&5R
M#0H-"D5N9"!3=6(-"@.T*4W5B(%=R:71E5&%B;&5(96%D97(H<U-C<FEP=$YA
M;64L4V5A<F-H7U!H<F%S92 I#0H)<F5S<&]N<V4N=W)I=&4@.(CQ(5$U,/B(-
M"@.ER97-P;VYS92YW<FET92 B/$A%040^(@.T*"7)E<W!O;G-E+G=R:71E("(\
M345402!H='1P+65Q=6EV/2(B0V]N=&5N="U4>7!E(B(@.8V]N=&5N=#TB(G1E
M>'0O:'1M;#L@.8VAA<G-E=#UW:6YD;W=S+3$R-3(B(CXB#0H)<F5S<&]N<V4N
M=W)I=&4@.(CQ,24Y+(&AR968](B(O25,O<W1Y;&4N8W-S(B(@.='EP93TB(G1E
M>'0O8W-S(B(@.<F5L/2(B<W1Y;&5S:&5E="(B/B(-"@.ER97-P;VYS92YW<FET
M92 B/"](14%$/B(-"@.ER97-P;VYS92YW<FET92 B/$)/1%D@.=DQI;FL](B(C
M,# P,# P(B(@.;&EN:STB(B,P,# P,# B(B!B9T-O;&]R/2(B(V9F9F9F9B(B
M(&QE9G1-87)G:6X](B(P(B(@.=&]P36%R9VEN/2(B,"(B(&UA<F=I;FAE:6=H
M=#TB(C B(B!M87)G:6YW:61T:#TB(C B(CXB#0H)<F5S<&]N<V4N=W)I=&4@.
M(CQF;W)M(&ED/2(B9F]R;3$B(B!M971H;V0](B)G970B(B!A8W1I;VX](B(B
M("8@.<U-C<FEP=$YA;64@.("8@.(B(B/B(-"@.ER97-P;VYS92YW<FET92 B/'1A
M8FQE(&)O<F1E<D-O;&]R/2(B(V$W8F(X-B(B(&AE:6=H=#TB(C$P,"4B(B!C
M96QL4W!A8VEN9STB(C B(B!C96QL4&%D9&EN9STB(C4B(B!W:61T:#TB(C$P
M,"4B(B!B9T-O;&]R/2(B(V8Y9F5E9"(B(&)O<F1E<CTB(C B(CXB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CQT<CXB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!W:61T
M:#TB(C$P,"(B(&)G0V]L;W(](B(C8V)E-6$V(B(@.:&5I9VAT/2(B-S B(CXF
M;F)S<#L\+W1D/B(-"@.ER97-P;VYS92YW<FET92 B/'1D(&-L87-S/2(B;F5W
M+71D+79E<G1I8V%L(B(@.=VED=&@.](B(U(B(@.8F=#;VQO<CTB(B-A-V)B.#8B
M(B!H96EG:'0](B(W,"(B/CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(DE3+W-H
M:6TN9VEF(B(@.=VED=&@.](B(Q(B(^/"]T9#XB#0H)<F5S<&]N<V4N=W)I=&4@.
M(CQT9"!W:61T:#TB(C(P(B(@.:&5I9VAT/2(B-S B(CXF;F)S<#LB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.=D%L
M:6=N/2(B=&]P(B(@.=VED=&@.](B(U,# B(B!H96EG:'0@./2 B(C@.P(B(^/$E-
M1R!H96EG:'0](B(V,"(B(&AS<&%C93TB(C(P(B(@.<W)C/2(B25,O:7-F7VQO
M9V\N9VEF(B(@.('=I9'1H/2(B,34R(B(^/"]T9#XB#0H)<F5S<&]N<V4N=W)I
M=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"UV97)T:6-A;"(B(&)G0V]L;W(](B(C
M8V)E-6$V(B(@.:&5I9VAT/2(B-S B(CXF;F)S<#L\+W1D/B(-"@.ER97-P;VYS
M92YW<FET92 B/"]T<CXB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT<CXB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"(B('=I9'1H /2(B,3 P
M(B(@.8F=#;VQO<CTB(B,U8CDS864B(B!H96EG:'0](B(Q,"(B/CQ)34<@.:&5I
M9VAT/2(B,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.=VED=&@.](B(Q(B(^/"]T
M9#XB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"(B('=I
M9'1H/2(B-2(B(&)G0V]L;W(](B(C,V4W-CDQ(B(@.:&5I9VAT/2(B,3 B(CX\
M24U'(&AE:6=H=#TB(C$B(B!S<F,](B))4R]S:&EM+F=I9B(B('=I9'1H/2(B
M,2(B/CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.8VQA<W,](B)N97<M
M=&0B(B!W:61T:#TB(C(P(B(@.8F=#;VQO<CTB(B,W.&(P8V(B( B!H96EG:'0]
M(B(Q,"(B/CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.
M=VED=&@.](B(Q(B(^/"]T9#XB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!C;&%S
M<STB(FYE=RUT9"(B('9!;&EG;CTB(F-E;G1E<B(B('=I9'1H/2(B-3 P(B(@.
M8F=#;VQO<CTB(B,W.&(P8V(B(B!H96EG:'0](B(Q(B(^/'-T<F]N9R!C;&%S
M<STB(G1E>'0B(B!S='EL93TB(DU!4D=)3BU,1494.B R,'!X(B(^4V5A<F-H
M.CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.=VED=&@.]
M(B(Q,"(B/B9N8G-P.R9N8G-P.R(-"@.ER97-P;VYS92YW<FET92 B/$E.4%54
M(&ED/2(B4V5A<F-H7U!H<F%S92(B('1Y<&4](B)T97AT(B(@.<VEZ93TB(C,P
M(B(@.;F%M93TB(E-E87)C:%]0:')A<V4B(B!V86QU93TB(B(@.)B!396%R8VA?
M4&AR87-E("8@.(B(B('-T>6QE/2(B34%21TE.+51/4#H@.+31P>#L@.34%21TE.
M+4)/5%1/33H@.+3-P>"(B('9A;'5E/2(B(B(^(#QA(&ED/2(B8G1H1V\B(B!C
M;&%S<STB(F=O(B(@.:')E9CTB(FIA=F%S8W)I<'0Z9F]R;3$N<W5B;6ET*"D[
M(B(^(@.T*"7)E<W!O;G-E+G=R:71E(")';R$\+V$^/$E-1R!H96EG:'0](B(Q
M(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C4B(CXF;F)S<#L@./"]S
M=')O;F<^(@.T*"7)E<W!O;G-E+G=R:71E("(\+W1D/B(-"@.ER97-P;VYS92YW
M<FET92 B/'1D(&-L87-S/2(B;F5W+71D(B)V06QI9VX](B)T;W B(B!B9T-O
M;&]R/2(B(S5B.3-A92(B(&AE:6=H=#TB(C$P(B(^/$E-1R!H96EG:'0](B(Q
M(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER
M97-P;VYS92YW<FET92 B/"]T<CXB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT<CXB
M#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!W:61T:#TB(C$P,"(B(&)G0V]L;W(]
M(B(C8V)E-6$V(B(@.:&5I9VAT/2(B,3 P(B(^)FYB<W [/"]T9#XB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"UV97)T:6-A;"(B('=I
M9'1H/2(B-2(B(&)G0V]L;W(](B(C83=B8C@.V(B(@.:&5I9VAT/2(B,3 P(B(^
M/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB
M(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET92 B/'1D('9!;&EG;CTB(G1O
M<"(B('=I9'1H/2(B,C B(B!B9T-O;&]R/2(B(V8Y9F5E9"(B/B9N8G-P.SPO
M=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.=D%L:6=N/2(B=&]P(B(@.=VED
M=&@.](B(U,# B(B!H96EG:'0@./2 B(C$P,"4B(CXB#0H-"D5N9"!3=6(-"@.T*
M4W5B(%=R:71E5&%B;&5";V1Y*&E2;W=S4&5R4&%G92P@.:5-T87)T+'-38W)I
M<'1.86UE+"!S0V]N;E-T<FEN9RP@.<U-13"Q396%R8VA?4&AR87-E*0T*"41I
M;2!I3&]O<"P@.86QL9&%T82P@.;G5M8V]L<RP@.;G5M<F]W<RP@.:5-T;W L(&E2
M;W=,;V]P+"!C;VQC;W5N=&5R+"!T:&ES9FEE;&0-"@.E$:6T@.8VYN+"!R<W0L
M(&-M9"P@.<&%R86TL('!A<F%M,2P@.<&%R86TR#0H-"@.ES970@.8VYN(#T@.4V5R
M=F5R+D-R96%T94]B:F5C="@.B041/1$(N0T].3D5#5$E/3B(I#0H)8VYN+D-O
M;FYE8W1I;VY3=')I;F<@./2!S0V]N;E-T<FEN9PT*"6-N;BY/<&5N#0H-"@.ES
M970@.8VUD/5-E<G9E<BY#<F5A=&5/8FIE8W0H(D%$3T1"+D-O;6UA;F0B*0T*
M#0H)8VUD+D-O;6UA;F1497AT/2)Q=65R>2(-"@.EC;60N0V]M;6%N9%1Y<&4]
M861#;613=&]R9610<F]C#0H)8VUD+D%C=&EV94-O;FYE8W1I;VX]8VYN#0H-
M"@.E3970@.<&%R86T@./2!C;60N0W)E871E4&%R86UE=&5R*") <W1R4V5A<F-H
M(BP@.861687)#:&%R("P@.861087)A;4EN<'5T+" R,# L4V5A<F-H7U!H<F%S
M92D-"@.EC;60N4&%R86UE=&5R<RY!<'!E;F0@.<&%R86T-"@.T*"7-E="!R<W0@.
M/2!C;60N97AE8W5T90T*#0H):4QO;W @./2 Q#0H):68@.<G-T+D5/1CU&04Q3
M12!T:&5N#0H)"6%L;&1A=&$]<G-T+D=E=%)O=W,-"@.D);G5M8V]L<SUU8F]U
M;F0H86QL9&%T82PQ*0T*"0EN=6U2;W=S/75B;W5N9"AA;&QD871A+#(I*S$-
M"@.EE;'-E( T*"0EN=6U2;W=S/3 -"@.EE;F0@.:68@.#0H-"@.T*"7)S="YC;&]S
M90T*"6EF(&YU;5)O=W,\/B P('1H96X-"@.T*"0EI9B!N=6U2;W=S/BAI4F]W
M<U!E<E!A9V4@.*VE3=&%R="D@.=&AE;@.T*"0D):5-T;W ]:5)O=W-097)086=E
M("MI4W1A<G0M,0T*"0EE;'-E#0H)"0EI4W1O<#UN=6U2;W=S("TQ#0H)"65N
M9"!I9@.T*#0H)"7)E<W!O;G-E+G=R:71E(&YU;5)O=W,@.)B B(')E8V]R9',@.
M9F]U;F0@.+2!$:7-P;&%Y:6YG("(@.)B!I4W1A<G0@.*S$@.)B B('1H<F]U9V@.@.
M(B F(&E3=&]P("LQ("8@.(CQ"4CXB#0H-"@.D)268@.:5-T87)T(#X@.,"!4:&5N
M#0H)"0E297-P;VYS92Y7<FET92 B(#QA(&AR968](B F('-38W)I<'1.86UE
M("8@.(C]396%R8VA?4&AR87-E/2(F4V5A<F-H7U!H<F%S92 F("(F:5-T87)T
M/2(F*&E3=&%R="UI4F]W<U!E<E!A9V4I("8@.(CX\/"!0<F5V:6]U<SPO83XB
M#0H)"45N9"!)9@.T*#0H)"4EF(&E3=&%R=" K(&E2;W=S4&5R4&%G92 \(&YU
M;5)O=W,@.5&AE;@.T*"0D)4F5S<&]N<V4N5W)I=&4@.(B \82!H<F5F/2(@.)B!S
M4V-R:7!T3F%M92 F("(_4V5A<F-H7U!H<F%S93TB)E-E87)C:%]0:')A<V4@.
M)B B)FE3=&%R=#TB)B H:5-T87)T*VE2;W=S4&5R4&%G92D@.)B B/DYE>'0^
M/CPO83XB#0H)"45N9"!)9@.T*"0ER97-P;VYS92YW<FET92 B/%1A8FQE(&)O
M<F1E<D-O;&]R/2(B(V$W8F(X-B(B(&AE:6=H=#TB(C4E(B(@.8V5L;%-P86-I
M;F<](B(P(B(@.8V5L;%!A9&1I;F<](B(U(B(@.=VED=&@.](B(Q,# E(B(@.8F=#
M;VQO<CTB(B-F.69E960B(B!B;W)D97(](B(Q(B(^(@.T*"0ER97-P;VYS92YW
M<FET92 B/%12/B(-"@.D)<F5S<&]N<V4N5W)I=&4@.(CQ42#Y(:6=H;&EG:'1I
M;F<\+U1(/CQ42#Y296-O<F0@.3G5M8F5R/"]42#X\5$@.^0VAA<F%C=&5R:7IA
M=&EO;CPO5$@.^/%1(/D-R96%T92!$871E/"]42#X\5$@.^4VEZ93PO5$@.^/%1(
M/E)A;FL\+U1(/B(-"@.D)<F5S<&]N<V4N=W)I=&4@.(CPO5%(^(@.T*#0H)"49/
M4B!I4F]W3&]O<#T@.:5-T87)T('1O(&E3=&]P#0H)"0ER97-P;VYS92YW<FET
M92 B/%12/B(@.)B!V8F-R;&8-"@.T*"0D)1D]2(&-O;&-O=6YT97(],"!T;R!N
M=6UC;VQS#0H)"2 @.(" @.(" @.<F5S<&]N<V4N=W)I=&4@.(CQT9"!V86QI9VX]
M=&]P/B(-"@.D)(" @.(" @.("!I9B )8V]L8V]U;G1E<CTP('1H96X-"@.D)(" @.
M(" @.(" @.5V5B2&ET<U%U97)Y(#T@.(B9#:5=E8DAI='-&:6QE/2(@.)B!397)V
M97(N55),16YC;V1E*')T<FEM*&%L;&1A=&$H8V]L8V]U;G1E<BLQ+&E2;W=,
M;V]P*2DI("8B)D-I4F5S=')I8W1I;VX](B F(%-E<G9E<BY54DQ%;F-O9&4H
M4V5A<F-H7U!H<F%S92D-"@.D)(" @.(" @.(" @.4F5S<&]N<V4N5W)I=&4@.(CQ0
M/CQA(&AR968]+V]O<"]Q:&ET+FAT=S]#:4AI;&ET951Y<&4]4W5M;6%R>2(@.
M)B!796)(:71S475E<GD@.)B B/CQ)34<@.<W)C/2(@.)B(O:&EL:6=H="YG:68B
M("8@.(B!A;&EG;CUL969T(&%L=#TB("8@.(DAI9VAL:6=H="!M8 71C:&EN9R!T
M97)M<R!I;B!D;V-U;65N="!U<VEN9R!3=6UM87)Y(&UO9&4N(B F("(^/% ^
M4W5M;6%R>3PO83X\4#XB#0H@.( D)"0D)"5)E<W!O;G-E+E=R:71E("(\0E(^
M/% ^/% ^/&$@.:')E9CTO;V]P+W%H:70N:'1W/T-I2&EL:71E5'EP93U&=6QL
M(B F(%=E8DAI='-1=65R>2 F("(^/$E-1R!S<F,](B8@.("(O:&EL:6=H="YG
M:68B)B(@.86QI9VX];&5F="!A;'0](B8B2&EG:&QI9VAT(&UA=&-H:6YG('1E
M<FUS(&EN(&1O8W5M96YT+B(F(CX\4#Y&=6QL/"]A/CQ0/B(-"@.D)(" @.(" @.
M("!E;'-E:68@."6-O;&-O=6YT97(],2!T:&5N#0H)"2 @.(" @.(" @."7)E<W!O
M;G-E+G=R:71E("(\82!H<F5F/2(B+V5D:70N87-P/U!+/2(F86QL9&%T82AC
M;VQC;W5N=&5R+3$L:5)O=TQO;W I)B(B(CXB("8@.(&E2;W=,;V]P*S$@.)B B
M/"]A/B(-"@.D)(" @.(" @.("!E;'-E#0H)"0D)(" @.(" @.("!T:&ES9FEE;&0]
M86QL9&%T82AC;VQC;W5N=&5R+&E2;W=,;V]P*0T*"0D)"2 @.(" @.(" @.:68@.
M:7-N=6QL*'1H:7-F:65L9"D@.=&AE;@.T*"0D)"0D)"0D@.('1H:7-F:65L9#US
M:&]W;G5L; T*"0D)"0D)"0EE;F0@.:68-"@.D)"0D)"0D):68@.=')I;2AT:&ES
M9FEE;&0I/2(B('1H96X-"@.D)"0D)(" )"0ET:&ES9FEE;&0]<VAO=V)L86YK
M#0H)"0D)"0D)96YD(&EF#0H-"@.D)"2 @.(" @.(" @.<F5S<&]N<V4N=W)I=&4@.
M=&AI<V9I96QD#0H)"0D@.(" @.96YD(&EF#0H)"2 @.(" @.(" @.<F5S<&]N<V4N
M=W)I=&4@.(CPO=&0^(@.T*"0D@.(" @.($Y%6%0-"@.D)(" @.("!R97-P;VYS92YW
M<FET92 B/"]T<CXB#0H)"4Y%6%0-"@.T*#0H)"7)E<W!O;G-E+G=R:71E("(\
M+W1A8FQE/B(-"@.D);VX@.97)R;W(@.9V]T;R P#0H)"4EF(&E3=&%R=" ^(# @.
M5&AE;@.T*(" @.(" @.(" )4F5S<&]N<V4N5W)I=&4@.(B \82!H<F5F/2(@.)B!S
M4V-R:7!T3F%M92 F("(_4V5A<F-H7U!H<F%S93TB)E-E87)C:%]0:')A<V4@.
M)B B)FE3=&%R=#TB)BAI4W1A<G0M:5)O=W-097)086=E*2 F("(^/#P@.4')E
M=FEO=7,\+V$^(@.T*"0E%;F0@.268-"@.T*"0E)9B!I4W1A<G0@.*R!I4F]W<U!E
M<E!A9V4@./"!N=6U2;W=S(%1H96X-"@.D)"5)E<W!O;G-E+E=R:71E("(@./&$@.
M:')E9CTB("8@.<U-C<FEP=$YA;64@.)B B/U-E87)C:%]0:')A<V4](B9396%R
M8VA?4&AR87-E("8@.(B9I4W1A<G0](B8@.*&E3=&%R="MI4F]W<U!E<E!A9V4I
M("8@.(CY.97AT/CX\+V$^(@.T*"0E%;F0@.268-"@.EE;'-E#0H)"7)E<W!O;G-E
M+G=R:71E(").;R!M871C:&EN9R!R96-O<F1S(&9O=6YD+B(-"@.EE;F0@.:68-
M"@.T*96YD('-U8@.T*#0I3=6(@.5W)I=&5486)L949O;W1E<@.T*#0H)<F5S<&]N
M<V4N=W)I=&4@.(CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.8VQA<W,]
M(B)N97<M=&0M=F5R=&EC86PB(B!B9T-O;&]R/2(B(V-B935A-B(B(&AE:6=H
M=#TB(C$P,"(B/B9N8G-P.SPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\+W1R
M/B(-"@.ER97-P;VYS92YW<FET92 B/'1R/B(-"@.ER97-P;VYS92YW<FET92 B
M/'1D(&-L87-S/2(B;F5W+71D(B(@.=VED=&@.](B(Q,# B(B!B9T-O;&]R/2(B
M(V$W8F(X-B(B(&AE:6=H=#TB(C$P(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C
M/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS
M92YW<FET92 B/'1D(&-L87-S/2(B;F5W+71D(B(@.8F=#;VQO<CTB(B,S93<V
M.3$B(B!H96EG:'0](B(Q,"(B/CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(FEM
M86=E<R]S:&EM+F=I9B(B('=I9'1H/2(B,2(B/CPO=&0^(@.T*"7)E<W!O;G-E
M+G=R:71E("(\=&0@.8VQA<W,](B)N97<M=&0B(B!W:61T:#TB(C(P(B(@.8F=#
M;VQO<CTB(B,W.&(P8V(B(B!H96EG:'0](B(Q,"(B/CQ)34<@.:&5I9VAT/2(B
M,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.=VED=&@.](B(Q(B(^/"]T9#XB#0H)
M<F5S<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"(B('9!;&EG; CTB
M(G1O<"(B('=I9'1H/2(B-3 P(B(@.8F=#;VQO<CTB(B,W.&(P8V(B(B!H96EG
M:'0](B(Q,"(B/B(-"@.ER97-P;VYS92YW<FET92 B/' @.8VQA<W,](B)P+6QI
M9VAT(B(^*&,I(&-O<'ER:6=H=#QA(&AR968](B)H='1P.B\O=W=W+G-M:6QL
M82YR=2(B/B9N8G-P.VAT=' Z+R]W=W<N<VUI;&QA+G)U/"]A/CPO<#XB#0H)
M<F5S<&]N<V4N=W)I=&4@.(CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.
M8VQA<W,](B)N97<M=&0B(G9!;&EG;CTB(G1O<"(B(&)G0V]L;W(](B(C-6(Y
M,V%E(B(@.:&5I9VAT/2(B,3 B(CX\24U'(&AE:6=H=#TB(C$B(B!S<F,](B))
M4R]S:&EM+F=I9B(B('=I9'1H/2(B,2(B/CPO=&0^(@.T*"7)E<W!O;G-E+G=R
M:71E("(\+W1R/B(-"@.ER97-P;VYS92YW<FET92 B/'1R/B(-"@.ER97-P;VYS
M92YW<FET92 B/'1D('=I9'1H/2(B,3 P(B(@.8F=#;VQO<CTB(B-C8F4U838B
M(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O<VAI
M;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET92 B
M/'1D(&-L87-S/2(B;F5W+71D+79E<G1I8V%L(B(@.8F=#;VQO<CTB(B-A-V)B
M.#8B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O
M<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET
M92 B/'1D('=I9'1H/2(B,C B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0]
M(B(Q(B(@.<W)C/2(B25-S:&EM+F=I9B(B('=I9'1H/2(B,2(B/CPO=&0^(@.T*
M"7)E<W!O;G-E+G=R:71E("(\=&0@.=D%L:6=N/2(B=&]P(B(@.=VED=&@.](B(U
M,# B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O
M<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET
M92 B/'1D(&-L87-S/2(B;F5W+71D+79E<G1I8V%L(B(@.=D%L:6=N/2(B=&]P
M(B(@.8F=#;VQO<CTB(B-C8F4U838B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG
M:'0](B(Q(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D
M/B(-"@.ER97-P;VYS92YW<FET92 B/"]T<CXB#0H)<F5S<&]N<V4N=W)I=&4@.
M(CPO=&%B;&4^(@.T*"7)E<W!O;G-E+G=R:71E("(\+V9O<FT^(@.T*"7)E<W!O
M;G-E+G=R:71E("(\2#$^(@.T*"7)E<W!O;G-E+G=R:71E("(\+T)/1%D^(@.T*
J"7)E<W!O;G-E+G=R:71E("(\+TA434P^(@.T*16YD(%-U8@.T*)3X-"@.T*
`
end
|||Thomas,
Take a look at a sample script I wrote for paging resultsets:
http://www.sqlservercentral.com/scri...tions/1233.asp
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23CbZeXGrEHA.332@.TK2MSFTNGP14.phx.gbl...
> You are using the wrong approach for this.
> Here is some sample code that uses asp and getrows.
> Approaches like the one you are using do not offer the best performance.
> Check out
> http://www.adopenstatic.com/experime...dsetpaging.asp
> for more information.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Thomas Nielsen [AM Production A/S]" <jack_pot_dk@.h0tmail.com> wrote in
> message news:%23rHETFErEHA.2796@.TK2MSFTNGP10.phx.gbl...
> often
> the
> in-memory
> and
> to
> and
>
>

"Order BY" question

I've got a table that holds real estate data. One of the column holds "category" data. Where there could be 100s of records, there are only four different types of categories:

listing
escrow
evict
rehab

The name of the column is "status". If I do an Order By Status ASC, I get the expected alphabetical listing. However, I would like to list things differently than that. Specifically, I'd like to list all of the records in the table by Status, BUT, list them in this order:

evict

rehab

listing

escrow

Is this doable? Thanks for your help!

Ron

tobias,

The best thing for you to do would be to have a lookup table that stores your "Status" values, and add a column called "SortOrder" to that table -- let this column define the order of the statuses. Then, in your query, join onto the Status table and do an ORDER BY SortOrder. This will also give you the flexibility of modifying the sort order to something else in the future.

|||

Thanks, vito. Forgive my lack of knowledge here, but could give an example of what the SQL select statement what look like that joined two tables and ORDER(ed) BY SortOrder? Thanks for your help.

|||

tobias5:

I've got a table that holds real estate data. One of the column holds "category" data. Where there could be 100s of records, there are only four different types of categories:

listing
escrow
evict
rehab

The name of the column is "status". If I do an Order By Status ASC, I get the expected alphabetical listing. However, I would like to list things differently than that. Specifically, I'd like to list all of the records in the table by Status, BUT, list them in this order:

evict

rehab

listing

escrow

Is this doable? Thanks for your help!

Ron

Vito's suggestion would be to build a lookup table, let's call it Status, that looks like this:

StatusCode SortOrder

evict 3
rehab 4
listing 1
escrow 2

Then your SQL statement would look like this:

SELECT

someColumns

FROM

myTable

INNER JOIN

Status ON myTable.StatusCode = Status.StatusCode

ORDER BY

Status.SortOrder

Alternately, and this is ultimately not as flexible, you could forego the additional table and use a CASE statement for the ordering:

SELECT

someColumns

FROM

myTable
ORDER BY

CASE StatusCode

WHEN 'evict' THEN 3

WHEN 'rehab' THEN 4

WHEN 'listing' THEN 1

WHEN 'escrow' THEN 2

ELSE 5

END

|||

Thanks vito and tmorton. Both of your suggestions worked beautifully.

I VERY much appreciate your help!

Ron

"Open table" doesn't show all records (sql server 2005).

Using sql managment studio in Sql Server 2005, I right-click on a (large)
table and select "open table," and then press the "move last" (go to last
record) button. After a minute or two, it claims all records (455,903 of
455,903) have been accessed (what it shows on the status bar, also the
"pause" button becomes greyed out), but in fact it has stopped at record
446,739, and browsing doesn't show the missing last records.
Running a query in Management Studio on that same table shows that all the
records really are there. Opening the same table from Access as a linked
table also shows all the records are there (Access only takes a few seconds
to execute a move to last record command on the table and go to record
455,903).
Is this a bug? It's hard to see how it's by design, at least the false
indication that I'm at the last (455,903th) record seems to be a bug.
Thanks.
Okay, I see that what "open table" has done is actually put the last set of
records somewhere in the middle of the table, in an order different from
SELECT * (and access). Setting a clustered index solved that problem (but
not the speed issue...).
|||On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Using sql managment studio in Sql Server 2005, I right-click on a (large)
> table and select "open table," and then press the "move last" (go to last
> record) button. After a minute or two, it claims all records (455,903 of
> 455,903) have been accessed (what it shows on the status bar, also the
> "pause" button becomes greyed out), but in fact it has stopped at record
> 446,739, and browsing doesn't show the missing last records.
>
What exactly do you mean by saying "it has stopped at record 446,739"?
There is no concept of a first or last record because tables are
unordered sets by definition. There is no such thing as a record
number.
Returning 400K rows for display in a single query is a rather absurd
thing to do. It's certainly the case that the Open Table feature
leaves a lot to be desired in many respects. On the other hand it
isn't so surprising that it struggles with such an unreasonable
request. Why would you really want to do that?
I suggest you write queries to extract the data that's of interest to
you. Otherwise you might as well be using a spreadsheet instead of a
database.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||On 4 Mar, 10:33, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Okay, I see that what "open table" has done is actually put the last set of
> records somewhere in the middle of the table, in an order different from
> SELECT * (and access). Setting a clustered index solved that problem (but
> not the speed issue...).
No. A table is unordered by definition. This has nothing to do with
clustered indexes and unless you specify ORDER BY in a query the
resulting order is undefined - i.e. the ordering could even be
different each time you run the query. You simply cannot use a SQL
database as if it were a spreadsheet.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Thanks. I meant the indexed primary key field, which is actually a record
count field in my table. However, as I said in the revised post, the
records were there after all (the upsizing wizard had not created a
clustered index as I expected).
Of course, I follow the standard practice of using queries to limit the size
of result sets. But it's by no means absurd to occasionally need to browse
an entire table, even a large one--as you can guess from the numerous
complaints when MS briefly eliminated "open table" from studio during the
beta. At any rate, given how fast the linked table feature to that same sql
server 2005 table works from Access, I would think some speed improvement
can be made here.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173004572.639515.95050@.64g2000cwx.googlegrou ps.com...
> On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> What exactly do you mean by saying "it has stopped at record 446,739"?
> There is no concept of a first or last record because tables are
> unordered sets by definition. There is no such thing as a record
> number.
> Returning 400K rows for display in a single query is a rather absurd
> thing to do. It's certainly the case that the Open Table feature
> leaves a lot to be desired in many respects. On the other hand it
> isn't so surprising that it struggles with such an unreasonable
> request. Why would you really want to do that?
> I suggest you write queries to extract the data that's of interest to
> you. Otherwise you might as well be using a spreadsheet instead of a
> database.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||Of course it's undefined by definition and one can't rely on the order
without an order by etc. However, some dbs do present the clustered order
on a simple table open (or equivalent) operation, and I checked for a
difference only after the post. I may have but didn't mean to imply that a
clustered index would lead to rows being in any given order on retrieval
(absent an order by), only that it in this case it did in fact cause a
difference between the select all and the open table operation.
|||On 4 Mar, 10:50, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Thanks. I meant the indexed primary key field, which is actually a record
> count field in my table. However, as I said in the revised post, the
> records were there after all (the upsizing wizard had not created a
> clustered index as I expected).
> Of course, I follow the standard practice of using queries to limit the size
> of result sets. But it's by no means absurd to occasionally need to browse
> an entire table, even a large one--as you can guess from the numerous
> complaints when MS briefly eliminated "open table" from studio during the
> beta. At any rate, given how fast the linked table feature to that same sql
> server 2005 table works from Access, I would think some speed improvement
> can be made here.
>
The key point is that "Open Table" is mainly a shortcut for "SELECT *
FROM tbl", which is not a smart thing to do in many environments.
Now you might have expected that MS would provide a more sophisticated
page-and-requery style of interface. Unfortunately, designing a single
generic UI to do that for every possible case isn't really feasible.
There are too many potential variables around indexing, requery,
locking and sorting strategies prefererred for different situations.
Even if it were achievable, some people who liked the "traditional"
open table feature would complain that a requery interface was not
what they wanted. So instead the lobby who favoured an Access legacy
UI got the one-size-fits-all solution they asked for. Unfortunately
that really means one-size-fits... very little.
For most practical purposes the solution for browsing 1000 rows will
likely be different to the solution required for browsing 1 billion
rows. That's why the only real answer is to build it yourself. The
compasison to Access ADP is irrelevant. It would be insane even to
attempt that in an enterprise-scale environment with thousands of
concurrent users.
HTH.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Yes, one might well have expected an interface much better, and not much
worse, than a good but cheaper product. What you said makes a lot of sense,
yet I'm not persuaded it's necessarily decisive. First, a minor point, this
is not being compared with an access adp, but an access mdb linked to sql
server via the native client. It may or not be insane in an enterprise
environment with thousands of concurrent users for only *one* user, the
developer, to browse the entire table with a linked mdb. However, it
doesn't seem insane to allow it *as an option* ("open table via ODBC" or
"open table in workgroup mode"?), nor impossible to implement, since already
used in the Access linked table scheme (you could also have an option to
"open table after table lock" or "try to acquire table lock on open
table"--there are several different possibilities here). Instead of "build
it myself," I prefer to have MS build it, and I'll "check the option box
myself." And why does it have to be one size fits all, when practically
everything else in sql server changes as environment and parameters change.
It would be an extremely useful feature for those of us who need it. Not
everyone is running sql server on the high end; increasingly people are
migrating in from the low end, and this is a very useful feature that I know
works well with sql server data in the small workgroup environment, but it's
only available as an option in Access. Of course, I can always open Access
and link to the same data, to avoid the minutes of delay, but then I need to
have Access installed on the machine and I need to set up a database there
and link.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173008179.376755.126190@.s48g2000cws.googlegr oups.com...
> The key point is that "Open Table" is mainly a shortcut for "SELECT *
> FROM tbl", which is not a smart thing to do in many environments.
> Now you might have expected that MS would provide a more sophisticated
> page-and-requery style of interface. Unfortunately, designing a single
> generic UI to do that for every possible case isn't really feasible.
> There are too many potential variables around indexing, requery,
> locking and sorting strategies prefererred for different situations.
> Even if it were achievable, some people who liked the "traditional"
> open table feature would complain that a requery interface was not
> what they wanted. So instead the lobby who favoured an Access legacy
> UI got the one-size-fits-all solution they asked for. Unfortunately
> that really means one-size-fits... very little.
> For most practical purposes the solution for browsing 1000 rows will
> likely be different to the solution required for browsing 1 billion
> rows. That's why the only real answer is to build it yourself. The
> compasison to Access ADP is irrelevant. It would be insane even to
> attempt that in an enterprise-scale environment with thousands of
> concurrent users.
> HTH.
> --
> David Portas, SQL Server MVP
>

"Open table" doesn't show all records (sql server 2005).

Using sql managment studio in Sql Server 2005, I right-click on a (large)
table and select "open table," and then press the "move last" (go to last
record) button. After a minute or two, it claims all records (455,903 of
455,903) have been accessed (what it shows on the status bar, also the
"pause" button becomes greyed out), but in fact it has stopped at record
446,739, and browsing doesn't show the missing last records.
Running a query in Management Studio on that same table shows that all the
records really are there. Opening the same table from Access as a linked
table also shows all the records are there (Access only takes a few seconds
to execute a move to last record command on the table and go to record
455,903).
Is this a bug? It's hard to see how it's by design, at least the false
indication that I'm at the last (455,903th) record seems to be a bug.
Thanks.Okay, I see that what "open table" has done is actually put the last set of
records somewhere in the middle of the table, in an order different from
SELECT * (and access). Setting a clustered index solved that problem (but
not the speed issue...).|||On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Using sql managment studio in Sql Server 2005, I right-click on a (large)
> table and select "open table," and then press the "move last" (go to last
> record) button. After a minute or two, it claims all records (455,903 of
> 455,903) have been accessed (what it shows on the status bar, also the
> "pause" button becomes greyed out), but in fact it has stopped at record
> 446,739, and browsing doesn't show the missing last records.
>
What exactly do you mean by saying "it has stopped at record 446,739"?
There is no concept of a first or last record because tables are
unordered sets by definition. There is no such thing as a record
number.
Returning 400K rows for display in a single query is a rather absurd
thing to do. It's certainly the case that the Open Table feature
leaves a lot to be desired in many respects. On the other hand it
isn't so surprising that it struggles with such an unreasonable
request. Why would you really want to do that?
I suggest you write queries to extract the data that's of interest to
you. Otherwise you might as well be using a spreadsheet instead of a
database.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 4 Mar, 10:33, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Okay, I see that what "open table" has done is actually put the last set o
f
> records somewhere in the middle of the table, in an order different from
> SELECT * (and access). Setting a clustered index solved that problem (but
> not the speed issue...).
No. A table is unordered by definition. This has nothing to do with
clustered indexes and unless you specify ORDER BY in a query the
resulting order is undefined - i.e. the ordering could even be
different each time you run the query. You simply cannot use a SQL
database as if it were a spreadsheet.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks. I meant the indexed primary key field, which is actually a record
count field in my table. However, as I said in the revised post, the
records were there after all (the upsizing wizard had not created a
clustered index as I expected).
Of course, I follow the standard practice of using queries to limit the size
of result sets. But it's by no means absurd to occasionally need to browse
an entire table, even a large one--as you can guess from the numerous
complaints when MS briefly eliminated "open table" from studio during the
beta. At any rate, given how fast the linked table feature to that same sql
server 2005 table works from Access, I would think some speed improvement
can be made here.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173004572.639515.95050@.64g2000cwx.googlegroups.com...
> On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> What exactly do you mean by saying "it has stopped at record 446,739"?
> There is no concept of a first or last record because tables are
> unordered sets by definition. There is no such thing as a record
> number.
> Returning 400K rows for display in a single query is a rather absurd
> thing to do. It's certainly the case that the Open Table feature
> leaves a lot to be desired in many respects. On the other hand it
> isn't so surprising that it struggles with such an unreasonable
> request. Why would you really want to do that?
> I suggest you write queries to extract the data that's of interest to
> you. Otherwise you might as well be using a spreadsheet instead of a
> database.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Of course it's undefined by definition and one can't rely on the order
without an order by etc. However, some dbs do present the clustered order
on a simple table open (or equivalent) operation, and I checked for a
difference only after the post. I may have but didn't mean to imply that a
clustered index would lead to rows being in any given order on retrieval
(absent an order by), only that it in this case it did in fact cause a
difference between the select all and the open table operation.|||On 4 Mar, 10:50, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Thanks. I meant the indexed primary key field, which is actually a record
> count field in my table. However, as I said in the revised post, the
> records were there after all (the upsizing wizard had not created a
> clustered index as I expected).
> Of course, I follow the standard practice of using queries to limit the si
ze
> of result sets. But it's by no means absurd to occasionally need to browse
> an entire table, even a large one--as you can guess from the numerous
> complaints when MS briefly eliminated "open table" from studio during the
> beta. At any rate, given how fast the linked table feature to that same s
ql
> server 2005 table works from Access, I would think some speed improvement
> can be made here.
>
The key point is that "Open Table" is mainly a shortcut for "SELECT *
FROM tbl", which is not a smart thing to do in many environments.
Now you might have expected that MS would provide a more sophisticated
page-and-requery style of interface. Unfortunately, designing a single
generic UI to do that for every possible case isn't really feasible.
There are too many potential variables around indexing, requery,
locking and sorting strategies prefererred for different situations.
Even if it were achievable, some people who liked the "traditional"
open table feature would complain that a requery interface was not
what they wanted. So instead the lobby who favoured an Access legacy
UI got the one-size-fits-all solution they asked for. Unfortunately
that really means one-size-fits... very little.
For most practical purposes the solution for browsing 1000 rows will
likely be different to the solution required for browsing 1 billion
rows. That's why the only real answer is to build it yourself. The
compasison to Access ADP is irrelevant. It would be insane even to
attempt that in an enterprise-scale environment with thousands of
concurrent users.
HTH.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Yes, one might well have expected an interface much better, and not much
worse, than a good but cheaper product. What you said makes a lot of sense,
yet I'm not persuaded it's necessarily decisive. First, a minor point, this
is not being compared with an access adp, but an access mdb linked to sql
server via the native client. It may or not be insane in an enterprise
environment with thousands of concurrent users for only *one* user, the
developer, to browse the entire table with a linked mdb. However, it
doesn't seem insane to allow it *as an option* ("open table via ODBC" or
"open table in workgroup mode"?), nor impossible to implement, since already
used in the Access linked table scheme (you could also have an option to
"open table after table lock" or "try to acquire table lock on open
table"--there are several different possibilities here). Instead of "build
it myself," I prefer to have MS build it, and I'll "check the option box
myself." And why does it have to be one size fits all, when practically
everything else in sql server changes as environment and parameters change.
It would be an extremely useful feature for those of us who need it. Not
everyone is running sql server on the high end; increasingly people are
migrating in from the low end, and this is a very useful feature that I know
works well with sql server data in the small workgroup environment, but it's
only available as an option in Access. Of course, I can always open Access
and link to the same data, to avoid the minutes of delay, but then I need to
have Access installed on the machine and I need to set up a database there
and link.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173008179.376755.126190@.s48g2000cws.googlegroups.com...
> The key point is that "Open Table" is mainly a shortcut for "SELECT *
> FROM tbl", which is not a smart thing to do in many environments.
> Now you might have expected that MS would provide a more sophisticated
> page-and-requery style of interface. Unfortunately, designing a single
> generic UI to do that for every possible case isn't really feasible.
> There are too many potential variables around indexing, requery,
> locking and sorting strategies prefererred for different situations.
> Even if it were achievable, some people who liked the "traditional"
> open table feature would complain that a requery interface was not
> what they wanted. So instead the lobby who favoured an Access legacy
> UI got the one-size-fits-all solution they asked for. Unfortunately
> that really means one-size-fits... very little.
> For most practical purposes the solution for browsing 1000 rows will
> likely be different to the solution required for browsing 1 billion
> rows. That's why the only real answer is to build it yourself. The
> compasison to Access ADP is irrelevant. It would be insane even to
> attempt that in an enterprise-scale environment with thousands of
> concurrent users.
> HTH.
> --
> David Portas, SQL Server MVP
>

"Open table" doesn't show all records (sql server 2005).

Using sql managment studio in Sql Server 2005, I right-click on a (large)
table and select "open table," and then press the "move last" (go to last
record) button. After a minute or two, it claims all records (455,903 of
455,903) have been accessed (what it shows on the status bar, also the
"pause" button becomes greyed out), but in fact it has stopped at record
446,739, and browsing doesn't show the missing last records.
Running a query in Management Studio on that same table shows that all the
records really are there. Opening the same table from Access as a linked
table also shows all the records are there (Access only takes a few seconds
to execute a move to last record command on the table and go to record
455,903).
Is this a bug? It's hard to see how it's by design, at least the false
indication that I'm at the last (455,903th) record seems to be a bug.
Thanks.Okay, I see that what "open table" has done is actually put the last set of
records somewhere in the middle of the table, in an order different from
SELECT * (and access). Setting a clustered index solved that problem (but
not the speed issue...).|||On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Using sql managment studio in Sql Server 2005, I right-click on a (large)
> table and select "open table," and then press the "move last" (go to last
> record) button. After a minute or two, it claims all records (455,903 of
> 455,903) have been accessed (what it shows on the status bar, also the
> "pause" button becomes greyed out), but in fact it has stopped at record
> 446,739, and browsing doesn't show the missing last records.
>
What exactly do you mean by saying "it has stopped at record 446,739"?
There is no concept of a first or last record because tables are
unordered sets by definition. There is no such thing as a record
number.
Returning 400K rows for display in a single query is a rather absurd
thing to do. It's certainly the case that the Open Table feature
leaves a lot to be desired in many respects. On the other hand it
isn't so surprising that it struggles with such an unreasonable
request. Why would you really want to do that?
I suggest you write queries to extract the data that's of interest to
you. Otherwise you might as well be using a spreadsheet instead of a
database.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 4 Mar, 10:33, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Okay, I see that what "open table" has done is actually put the last set of
> records somewhere in the middle of the table, in an order different from
> SELECT * (and access). Setting a clustered index solved that problem (but
> not the speed issue...).
No. A table is unordered by definition. This has nothing to do with
clustered indexes and unless you specify ORDER BY in a query the
resulting order is undefined - i.e. the ordering could even be
different each time you run the query. You simply cannot use a SQL
database as if it were a spreadsheet.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks. I meant the indexed primary key field, which is actually a record
count field in my table. However, as I said in the revised post, the
records were there after all (the upsizing wizard had not created a
clustered index as I expected).
Of course, I follow the standard practice of using queries to limit the size
of result sets. But it's by no means absurd to occasionally need to browse
an entire table, even a large one--as you can guess from the numerous
complaints when MS briefly eliminated "open table" from studio during the
beta. At any rate, given how fast the linked table feature to that same sql
server 2005 table works from Access, I would think some speed improvement
can be made here.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173004572.639515.95050@.64g2000cwx.googlegroups.com...
> On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
>> Using sql managment studio in Sql Server 2005, I right-click on a (large)
>> table and select "open table," and then press the "move last" (go to last
>> record) button. After a minute or two, it claims all records (455,903 of
>> 455,903) have been accessed (what it shows on the status bar, also the
>> "pause" button becomes greyed out), but in fact it has stopped at record
>> 446,739, and browsing doesn't show the missing last records.
> What exactly do you mean by saying "it has stopped at record 446,739"?
> There is no concept of a first or last record because tables are
> unordered sets by definition. There is no such thing as a record
> number.
> Returning 400K rows for display in a single query is a rather absurd
> thing to do. It's certainly the case that the Open Table feature
> leaves a lot to be desired in many respects. On the other hand it
> isn't so surprising that it struggles with such an unreasonable
> request. Why would you really want to do that?
> I suggest you write queries to extract the data that's of interest to
> you. Otherwise you might as well be using a spreadsheet instead of a
> database.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Of course it's undefined by definition and one can't rely on the order
without an order by etc. However, some dbs do present the clustered order
on a simple table open (or equivalent) operation, and I checked for a
difference only after the post. I may have but didn't mean to imply that a
clustered index would lead to rows being in any given order on retrieval
(absent an order by), only that it in this case it did in fact cause a
difference between the select all and the open table operation.|||On 4 Mar, 10:50, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Thanks. I meant the indexed primary key field, which is actually a record
> count field in my table. However, as I said in the revised post, the
> records were there after all (the upsizing wizard had not created a
> clustered index as I expected).
> Of course, I follow the standard practice of using queries to limit the size
> of result sets. But it's by no means absurd to occasionally need to browse
> an entire table, even a large one--as you can guess from the numerous
> complaints when MS briefly eliminated "open table" from studio during the
> beta. At any rate, given how fast the linked table feature to that same sql
> server 2005 table works from Access, I would think some speed improvement
> can be made here.
>
The key point is that "Open Table" is mainly a shortcut for "SELECT *
FROM tbl", which is not a smart thing to do in many environments.
Now you might have expected that MS would provide a more sophisticated
page-and-requery style of interface. Unfortunately, designing a single
generic UI to do that for every possible case isn't really feasible.
There are too many potential variables around indexing, requery,
locking and sorting strategies prefererred for different situations.
Even if it were achievable, some people who liked the "traditional"
open table feature would complain that a requery interface was not
what they wanted. So instead the lobby who favoured an Access legacy
UI got the one-size-fits-all solution they asked for. Unfortunately
that really means one-size-fits... very little.
For most practical purposes the solution for browsing 1000 rows will
likely be different to the solution required for browsing 1 billion
rows. That's why the only real answer is to build it yourself. The
compasison to Access ADP is irrelevant. It would be insane even to
attempt that in an enterprise-scale environment with thousands of
concurrent users.
HTH.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Yes, one might well have expected an interface much better, and not much
worse, than a good but cheaper product. What you said makes a lot of sense,
yet I'm not persuaded it's necessarily decisive. First, a minor point, this
is not being compared with an access adp, but an access mdb linked to sql
server via the native client. It may or not be insane in an enterprise
environment with thousands of concurrent users for only *one* user, the
developer, to browse the entire table with a linked mdb. However, it
doesn't seem insane to allow it *as an option* ("open table via ODBC" or
"open table in workgroup mode"?), nor impossible to implement, since already
used in the Access linked table scheme (you could also have an option to
"open table after table lock" or "try to acquire table lock on open
table"--there are several different possibilities here). Instead of "build
it myself," I prefer to have MS build it, and I'll "check the option box
myself." And why does it have to be one size fits all, when practically
everything else in sql server changes as environment and parameters change.
It would be an extremely useful feature for those of us who need it. Not
everyone is running sql server on the high end; increasingly people are
migrating in from the low end, and this is a very useful feature that I know
works well with sql server data in the small workgroup environment, but it's
only available as an option in Access. Of course, I can always open Access
and link to the same data, to avoid the minutes of delay, but then I need to
have Access installed on the machine and I need to set up a database there
and link.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173008179.376755.126190@.s48g2000cws.googlegroups.com...
> The key point is that "Open Table" is mainly a shortcut for "SELECT *
> FROM tbl", which is not a smart thing to do in many environments.
> Now you might have expected that MS would provide a more sophisticated
> page-and-requery style of interface. Unfortunately, designing a single
> generic UI to do that for every possible case isn't really feasible.
> There are too many potential variables around indexing, requery,
> locking and sorting strategies prefererred for different situations.
> Even if it were achievable, some people who liked the "traditional"
> open table feature would complain that a requery interface was not
> what they wanted. So instead the lobby who favoured an Access legacy
> UI got the one-size-fits-all solution they asked for. Unfortunately
> that really means one-size-fits... very little.
> For most practical purposes the solution for browsing 1000 rows will
> likely be different to the solution required for browsing 1 billion
> rows. That's why the only real answer is to build it yourself. The
> compasison to Access ADP is irrelevant. It would be insane even to
> attempt that in an enterprise-scale environment with thousands of
> concurrent users.
> HTH.
> --
> David Portas, SQL Server MVP
>

"insert on existing update" in MS SQL Server?

Hello

I used to work in a Sybase database environment. When I had to insert/
update records in the database, I always used "insert on existing
update", in this way, you didn't have to check whether a record
already existed (avoid errors) and you were always sure that after
running the scripts, the last version was in the database.

Now I'm looking for the same functionality in MS SQL Server, asked a
few people, but nobody knows about such an option.
Does anybody here knows the SQL Server counterpart of "insert on
existing skip/update"? If this doesn't exist, this is a minus for
MS ;).

Greetz,

BartBart op de grote markt wrote:

Quote:

Originally Posted by

Hello
>
I used to work in a Sybase database environment. When I had to insert/
update records in the database, I always used "insert on existing
update", in this way, you didn't have to check whether a record
already existed (avoid errors) and you were always sure that after
running the scripts, the last version was in the database.
>
Now I'm looking for the same functionality in MS SQL Server, asked a
few people, but nobody knows about such an option.
Does anybody here knows the SQL Server counterpart of "insert on
existing skip/update"? If this doesn't exist, this is a minus for
MS ;).

In ANSI/SQL it's called a MERGE statement.
In SQL Server I'd do an UPDATE FROM, if no row found follow up with an
INSERT.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab|||Bart op de grote markt (bartwarnez@.freegates.be) writes:

Quote:

Originally Posted by

I used to work in a Sybase database environment. When I had to insert/
update records in the database, I always used "insert on existing
update", in this way, you didn't have to check whether a record
already existed (avoid errors) and you were always sure that after
running the scripts, the last version was in the database.
>
Now I'm looking for the same functionality in MS SQL Server, asked a
few people, but nobody knows about such an option.
Does anybody here knows the SQL Server counterpart of "insert on
existing skip/update"? If this doesn't exist, this is a minus for
MS ;).

I'm afraid that you will have to chalk up one minus for MS SQL Server.

You will have to do:

UPDATE ...

INSERT ...
SELECT ...
WHERE NOT EXISTS (...)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ok, thank you all for the replies! It's a pity but well :-). If MS
reads this post: hello MS, you know what to do next.

Grtz,

Bart|||On Feb 6, 7:28 am, "Bart op de grote markt" <bartwar...@.freegates.be>
wrote:

Quote:

Originally Posted by

Hello
>
I used to work in a Sybase database environment. When I had to insert/
update records in the database, I always used "insert on existing
update", in this way, you didn't have to check whether a record
already existed (avoid errors) and you were always sure that after
running the scripts, the last version was in the database.
>
Now I'm looking for the same functionality in MS SQL Server, asked a
few people, but nobody knows about such an option.
Does anybody here knows the SQL Server counterpart of "insert on
existing skip/update"? If this doesn't exist, this is a minus for
MS ;).
>
Greetz,
>
Bart

Also look up "Mimicking MERGE Statement in SQL Server 2005 ":

http://sqlserver-tips.blogspot.com/...ent-in-sql.html