Incorrect results if no TOP clause

I have a problem with a query. When I omit the TOP clause I get more
rows returned than I expect (12 rows out of a 119 in the table), but
when I include TOP I get 5 rows from the same data with the otherwise
unchanged table.
Thing is, the query where I include TOP 1000 is the correct result.
I'm on SQL Server 2000, select @.@.version -
"Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000
00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 1) "
I've cut the query down quite a bit, the table
create table Test (
CDMA_PDSN63 FLOAT,
AVE__CORE_46_CountINTEGER,
AVE__CORE_46_Sum FLOAT);
The query is
SELECT * FROM (
SELECT
-- TOP 1000 -- comment this in to get correct results
D.CDMA_PDSN63, NM.AVE__CORE_46x AVE__CORE_46
FROM (SELECT DISTINCT CDMA_PDSN63
FROM(SELECT CDMA_PDSN63 FROM Test ) AS dims
) AS D,
(SELECT CDMA_PDSN63,
CASE when (Sum( AVE__CORE_46_Count))=0 then NULL else
SUM(AVE__CORE_46_Sum) / SUM( AVE__CORE_46_Count) end AS AVE__CORE_46x
FROM Test GROUP BY CDMA_PDSN63) AS NM
WHERE D.CDMA_PDSN63*=NM.CDMA_PDSN63
) as MainQuery
WHERE ROUND(MainQuery.AVE__CORE_46,0) >= 120
--AND MainQuery.AVE__CORE_46 IS NOT NULL - Note 1
--ORDER BY CDMA_PDSN63 - Note 2
Note 1: I tried adding this but it makes not difference
Note 2: this was in the original query, it makes sense to have it but
again it makes no difference, I've used in on the inner and outer
select, no change
I've also tried TOP 1000 in the outer select again no difference.
Changing the outer join to a regular join fixes the problem, but I
need the outer join in the original query. (In the original query
there are many more sub-selects and several more outer join clauses to
put them back together.)
If I use small values for TOP n (e.g. 1, 2, ... < 10) the results are
even stranger, I get a subset of the expected rows, and not the same
number as n.
I tried setting a rowcount as well, no difference.
I've seen several other queries on this newsgroup which talk about
similar problems but the threads have never been concluded with a
clear answer.
Any ideas? Thanks
allan
> "Microsoft SQL Server 2000 - 8.00.194
You're on RTM! Install Service Pack 3a, right away, please! There are many
query processor bugs that have been fixed since the product was released.
Thanks for the CREATE TABLE, but once you've done that, you're going to have
to provide us with enough sample data to reproduce your problem (as well as
tell us which rows you were expecting in the result set!). Otherwise, it's
impossible for us to determine exactly what's happening, why it doesn't meet
your criteria, and test our suggestions on fixing it...
See http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
|||Allan,
After you install service pack 3a as Aaron suggested, I suggest you
consider two other things:
* Rwrite the query using ANSI outer join syntax (... left outer join on
...), which, unlike *=, is always unambiguous
* Consider a different data type than FLOAT for data that must be
compared with the = operator. Because FLOAT values are approximate, you
cannot count on tests of equality to be reliable.
Steve Kass
Drew University
Allan Kelly wrote:

>I have a problem with a query. When I omit the TOP clause I get more
>rows returned than I expect (12 rows out of a 119 in the table), but
>when I include TOP I get 5 rows from the same data with the otherwise
>unchanged table.
>Thing is, the query where I include TOP 1000 is the correct result.
>I'm on SQL Server 2000, select @.@.version -
>"Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000
>00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer
>Edition on Windows NT 5.1 (Build 2600: Service Pack 1) "
>I've cut the query down quite a bit, the table
>create table Test (
>CDMA_PDSN63 FLOAT,
>AVE__CORE_46_CountINTEGER,
>AVE__CORE_46_Sum FLOAT);
>The query is
>SELECT * FROM (
>SELECT
>-- TOP 1000 -- comment this in to get correct results
>D.CDMA_PDSN63, NM.AVE__CORE_46x AVE__CORE_46
>FROM (SELECT DISTINCT CDMA_PDSN63
>FROM(SELECT CDMA_PDSN63 FROM Test ) AS dims
>) AS D,
>(SELECT CDMA_PDSN63,
>CASE when (Sum( AVE__CORE_46_Count))=0 then NULL else
>SUM(AVE__CORE_46_Sum) / SUM( AVE__CORE_46_Count) end AS AVE__CORE_46x
>FROM Test GROUP BY CDMA_PDSN63) AS NM
>WHERE D.CDMA_PDSN63*=NM.CDMA_PDSN63
>) as MainQuery
>WHERE ROUND(MainQuery.AVE__CORE_46,0) >= 120
>--AND MainQuery.AVE__CORE_46 IS NOT NULL - Note 1
>--ORDER BY CDMA_PDSN63 - Note 2
>Note 1: I tried adding this but it makes not difference
>Note 2: this was in the original query, it makes sense to have it but
>again it makes no difference, I've used in on the inner and outer
>select, no change
>I've also tried TOP 1000 in the outer select again no difference.
>Changing the outer join to a regular join fixes the problem, but I
>need the outer join in the original query. (In the original query
>there are many more sub-selects and several more outer join clauses to
>put them back together.)
>If I use small values for TOP n (e.g. 1, 2, ... < 10) the results are
>even stranger, I get a subset of the expected rows, and not the same
>number as n.
>I tried setting a rowcount as well, no difference.
>I've seen several other queries on this newsgroup which talk about
>similar problems but the threads have never been concluded with a
>clear answer.
>Any ideas? Thanks
>allan
>
|||Steve Kass <skass@.drew.edu> wrote in message news:<#Tj3pA0dEHA.1604@.TK2MSFTNGP11.phx.gbl>...
> Allan,
> After you install service pack 3a as Aaron suggested, I suggest you
> consider two other things:
> * Rwrite the query using ANSI outer join syntax (... left outer join on
> ...), which, unlike *=, is always unambiguous
> * Consider a different data type than FLOAT for data that must be
> compared with the = operator. Because FLOAT values are approximate, you
> cannot count on tests of equality to be reliable.
>
Thanks Aaron, Steve,
I rewrote the SQL using ANSI outer join syntax and that fixes the
problem. Great!
Thanks for the reminder to look into the service pack, my code need to
run against MSDE in the final product so I need to check out the
service pack situation there.
allan

Incorrect results if no TOP clause

I have a problem with a query. When I omit the TOP clause I get more
rows returned than I expect (12 rows out of a 119 in the table), but
when I include TOP I get 5 rows from the same data with the otherwise
unchanged table.
Thing is, the query where I include TOP 1000 is the correct result.
I'm on SQL Server 2000, select @.@.version -
"Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000
00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 1) "
I've cut the query down quite a bit, the table
create table Test (
CDMA_PDSN63 FLOAT,
AVE__CORE_46_Count INTEGER,
AVE__CORE_46_Sum FLOAT);
The query is
SELECT * FROM (
SELECT
-- TOP 1000 -- comment this in to get correct results
D.CDMA_PDSN63, NM.AVE__CORE_46x AVE__CORE_46
FROM (SELECT DISTINCT CDMA_PDSN63
FROM(SELECT CDMA_PDSN63 FROM Test ) AS dims
) AS D,
(SELECT CDMA_PDSN63,
CASE when (Sum( AVE__CORE_46_Count))=0 then NULL else
SUM(AVE__CORE_46_Sum) / SUM( AVE__CORE_46_Count) end AS AVE__CORE_46x
FROM Test GROUP BY CDMA_PDSN63) AS NM
WHERE D.CDMA_PDSN63*=NM.CDMA_PDSN63
) as MainQuery
WHERE ROUND(MainQuery.AVE__CORE_46,0) >= 120
--AND MainQuery.AVE__CORE_46 IS NOT NULL - Note 1
--ORDER BY CDMA_PDSN63 - Note 2
Note 1: I tried adding this but it makes not difference
Note 2: this was in the original query, it makes sense to have it but
again it makes no difference, I've used in on the inner and outer
select, no change
I've also tried TOP 1000 in the outer select again no difference.
Changing the outer join to a regular join fixes the problem, but I
need the outer join in the original query. (In the original query
there are many more sub-selects and several more outer join clauses to
put them back together.)
If I use small values for TOP n (e.g. 1, 2, ... < 10) the results are
even stranger, I get a subset of the expected rows, and not the same
number as n.
I tried setting a rowcount as well, no difference.
I've seen several other queries on this newsgroup which talk about
similar problems but the threads have never been concluded with a
clear answer.
Any ideas? Thanks
allan> "Microsoft SQL Server 2000 - 8.00.194
You're on RTM! Install Service Pack 3a, right away, please! There are many
query processor bugs that have been fixed since the product was released.
Thanks for the CREATE TABLE, but once you've done that, you're going to have
to provide us with enough sample data to reproduce your problem (as well as
tell us which rows you were expecting in the result set!). Otherwise, it's
impossible for us to determine exactly what's happening, why it doesn't meet
your criteria, and test our suggestions on fixing it...
See http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)|||Allan,
After you install service pack 3a as Aaron suggested, I suggest you
consider two other things:
* Rwrite the query using ANSI outer join syntax (... left outer join on
...), which, unlike *=, is always unambiguous
* Consider a different data type than FLOAT for data that must be
compared with the = operator. Because FLOAT values are approximate, you
cannot count on tests of equality to be reliable.
Steve Kass
Drew University
Allan Kelly wrote:

>I have a problem with a query. When I omit the TOP clause I get more
>rows returned than I expect (12 rows out of a 119 in the table), but
>when I include TOP I get 5 rows from the same data with the otherwise
>unchanged table.
>Thing is, the query where I include TOP 1000 is the correct result.
>I'm on SQL Server 2000, select @.@.version -
>"Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000
>00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer
>Edition on Windows NT 5.1 (Build 2600: Service Pack 1) "
>I've cut the query down quite a bit, the table
>create table Test (
> CDMA_PDSN63 FLOAT,
> AVE__CORE_46_Count INTEGER,
> AVE__CORE_46_Sum FLOAT);
>The query is
>SELECT * FROM (
>SELECT
>-- TOP 1000 -- comment this in to get correct results
>D.CDMA_PDSN63, NM.AVE__CORE_46x AVE__CORE_46
>FROM (SELECT DISTINCT CDMA_PDSN63
> FROM(SELECT CDMA_PDSN63 FROM Test ) AS dims
> ) AS D,
> (SELECT CDMA_PDSN63,
> CASE when (Sum( AVE__CORE_46_Count))=0 then NULL else
>SUM(AVE__CORE_46_Sum) / SUM( AVE__CORE_46_Count) end AS AVE__CORE_46x
> FROM Test GROUP BY CDMA_PDSN63) AS NM
>WHERE D.CDMA_PDSN63*=NM.CDMA_PDSN63
> ) as MainQuery
>WHERE ROUND(MainQuery.AVE__CORE_46,0) >= 120
>--AND MainQuery.AVE__CORE_46 IS NOT NULL - Note 1
>--ORDER BY CDMA_PDSN63 - Note 2
>Note 1: I tried adding this but it makes not difference
>Note 2: this was in the original query, it makes sense to have it but
>again it makes no difference, I've used in on the inner and outer
>select, no change
>I've also tried TOP 1000 in the outer select again no difference.
>Changing the outer join to a regular join fixes the problem, but I
>need the outer join in the original query. (In the original query
>there are many more sub-selects and several more outer join clauses to
>put them back together.)
>If I use small values for TOP n (e.g. 1, 2, ... < 10) the results are
>even stranger, I get a subset of the expected rows, and not the same
>number as n.
>I tried setting a rowcount as well, no difference.
>I've seen several other queries on this newsgroup which talk about
>similar problems but the threads have never been concluded with a
>clear answer.
>Any ideas? Thanks
>allan
>|||Steve Kass <skass@.drew.edu> wrote in message news:<#Tj3pA0dEHA.1604@.TK2MSFTNGP11.phx.gbl>...

> Allan,
> After you install service pack 3a as Aaron suggested, I suggest you
> consider two other things:
> * Rwrite the query using ANSI outer join syntax (... left outer join on
> ...), which, unlike *=, is always unambiguous
> * Consider a different data type than FLOAT for data that must be
> compared with the = operator. Because FLOAT values are approximate, you
> cannot count on tests of equality to be reliable.
>
Thanks Aaron, Steve,
I rewrote the SQL using ANSI outer join syntax and that fixes the
problem. Great!
Thanks for the reminder to look into the service pack, my code need to
run against MSDE in the final product so I need to check out the
service pack situation there.
allan

Incorrect results if no TOP clause

I have a problem with a query. When I omit the TOP clause I get more
rows returned than I expect (12 rows out of a 119 in the table), but
when I include TOP I get 5 rows from the same data with the otherwise
unchanged table.
Thing is, the query where I include TOP 1000 is the correct result.
I'm on SQL Server 2000, select @.@.version -
"Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000
00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 1) "
I've cut the query down quite a bit, the table
create table Test (
CDMA_PDSN63 FLOAT,
AVE__CORE_46_Count INTEGER,
AVE__CORE_46_Sum FLOAT);
The query is
SELECT * FROM (
SELECT
-- TOP 1000 -- comment this in to get correct results
D.CDMA_PDSN63, NM.AVE__CORE_46x AVE__CORE_46
FROM (SELECT DISTINCT CDMA_PDSN63
FROM(SELECT CDMA_PDSN63 FROM Test ) AS dims
) AS D,
(SELECT CDMA_PDSN63,
CASE when (Sum( AVE__CORE_46_Count))=0 then NULL else
SUM(AVE__CORE_46_Sum) / SUM( AVE__CORE_46_Count) end AS AVE__CORE_46x
FROM Test GROUP BY CDMA_PDSN63) AS NM
WHERE D.CDMA_PDSN63*=NM.CDMA_PDSN63
) as MainQuery
WHERE ROUND(MainQuery.AVE__CORE_46,0) >= 120
--AND MainQuery.AVE__CORE_46 IS NOT NULL - Note 1
--ORDER BY CDMA_PDSN63 - Note 2
Note 1: I tried adding this but it makes not difference
Note 2: this was in the original query, it makes sense to have it but
again it makes no difference, I've used in on the inner and outer
select, no change
I've also tried TOP 1000 in the outer select again no difference.
Changing the outer join to a regular join fixes the problem, but I
need the outer join in the original query. (In the original query
there are many more sub-selects and several more outer join clauses to
put them back together.)
If I use small values for TOP n (e.g. 1, 2, ... < 10) the results are
even stranger, I get a subset of the expected rows, and not the same
number as n.
I tried setting a rowcount as well, no difference.
I've seen several other queries on this newsgroup which talk about
similar problems but the threads have never been concluded with a
clear answer.
Any ideas? Thanks
allan> "Microsoft SQL Server 2000 - 8.00.194
You're on RTM! Install Service Pack 3a, right away, please! There are many
query processor bugs that have been fixed since the product was released.
Thanks for the CREATE TABLE, but once you've done that, you're going to have
to provide us with enough sample data to reproduce your problem (as well as
tell us which rows you were expecting in the result set!). Otherwise, it's
impossible for us to determine exactly what's happening, why it doesn't meet
your criteria, and test our suggestions on fixing it...
See http://www.aspfaq.com/5006
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Allan,
After you install service pack 3a as Aaron suggested, I suggest you
consider two other things:
* Rwrite the query using ANSI outer join syntax (... left outer join on
...), which, unlike *=, is always unambiguous
* Consider a different data type than FLOAT for data that must be
compared with the = operator. Because FLOAT values are approximate, you
cannot count on tests of equality to be reliable.
Steve Kass
Drew University
Allan Kelly wrote:
>I have a problem with a query. When I omit the TOP clause I get more
>rows returned than I expect (12 rows out of a 119 in the table), but
>when I include TOP I get 5 rows from the same data with the otherwise
>unchanged table.
>Thing is, the query where I include TOP 1000 is the correct result.
>I'm on SQL Server 2000, select @.@.version -
>"Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000
>00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer
>Edition on Windows NT 5.1 (Build 2600: Service Pack 1) "
>I've cut the query down quite a bit, the table
>create table Test (
> CDMA_PDSN63 FLOAT,
> AVE__CORE_46_Count INTEGER,
> AVE__CORE_46_Sum FLOAT);
>The query is
>SELECT * FROM (
>SELECT
>-- TOP 1000 -- comment this in to get correct results
>D.CDMA_PDSN63, NM.AVE__CORE_46x AVE__CORE_46
>FROM (SELECT DISTINCT CDMA_PDSN63
> FROM(SELECT CDMA_PDSN63 FROM Test ) AS dims
>) AS D,
> (SELECT CDMA_PDSN63,
> CASE when (Sum( AVE__CORE_46_Count))=0 then NULL else
>SUM(AVE__CORE_46_Sum) / SUM( AVE__CORE_46_Count) end AS AVE__CORE_46x
> FROM Test GROUP BY CDMA_PDSN63) AS NM
>WHERE D.CDMA_PDSN63*=NM.CDMA_PDSN63
>) as MainQuery
>WHERE ROUND(MainQuery.AVE__CORE_46,0) >= 120
>--AND MainQuery.AVE__CORE_46 IS NOT NULL - Note 1
>--ORDER BY CDMA_PDSN63 - Note 2
>Note 1: I tried adding this but it makes not difference
>Note 2: this was in the original query, it makes sense to have it but
>again it makes no difference, I've used in on the inner and outer
>select, no change
>I've also tried TOP 1000 in the outer select again no difference.
>Changing the outer join to a regular join fixes the problem, but I
>need the outer join in the original query. (In the original query
>there are many more sub-selects and several more outer join clauses to
>put them back together.)
>If I use small values for TOP n (e.g. 1, 2, ... < 10) the results are
>even stranger, I get a subset of the expected rows, and not the same
>number as n.
>I tried setting a rowcount as well, no difference.
>I've seen several other queries on this newsgroup which talk about
>similar problems but the threads have never been concluded with a
>clear answer.
>Any ideas? Thanks
>allan
>|||Steve Kass <skass@.drew.edu> wrote in message news:<#Tj3pA0dEHA.1604@.TK2MSFTNGP11.phx.gbl>...
> Allan,
> After you install service pack 3a as Aaron suggested, I suggest you
> consider two other things:
> * Rwrite the query using ANSI outer join syntax (... left outer join on
> ...), which, unlike *=, is always unambiguous
> * Consider a different data type than FLOAT for data that must be
> compared with the = operator. Because FLOAT values are approximate, you
> cannot count on tests of equality to be reliable.
>
Thanks Aaron, Steve,
I rewrote the SQL using ANSI outer join syntax and that fixes the
problem. Great!
Thanks for the reminder to look into the service pack, my code need to
run against MSDE in the final product so I need to check out the
service pack situation there.
allansql

Incorrect query stats from full text engine

Hi
We have a table that is approx 800,000 rows with about 6 columns of which 3
are full text indexed.
This table is full text queried then the results are used to filter other
tables in a
"select * from maintable where category=.. and primaryid in (select key from
CONTAINSTABLE(fulltexttable, Keywords, 'FORMSOF(INFLECTIONAL, "glass")'))"
Every so often we get a slow perfoming query with these. Putting them into
Management Studio and asking for a query plan shows its the remote scan
consuming all the time. Hovering the mouse over the "Remote Scan" to get the
statistics gives wildly inaccurate estimates of number of rows, and then also
gives impossible number of "Actual Rows Returned" e.g. on this query I have
just done it has claims to have returned 13,350,965 rows - this from a table
that only has approx 800,000 rows.
The indexed columns may have duplicate words but checking the specific
example the searched for word definately appears less that 800,000 times in
the whole table.
Any one got any suggestions?
Thanks
Chris
Just to say, is MS SQL 2005 Standard Edition SP2 64 bit edition
|||The statistics returned my SQL FTS are not accurate as you have discovered.
You should work on tuning other parts of the query to remove spooling, etc.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"chrisredburn" <chrisredburn@.discussions.microsoft.com> wrote in message
news:DC403EF1-81FC-42FE-BF36-AA8457E48755@.microsoft.com...
> Hi
> We have a table that is approx 800,000 rows with about 6 columns of which
> 3
> are full text indexed.
> This table is full text queried then the results are used to filter other
> tables in a
> "select * from maintable where category=.. and primaryid in (select key
> from
> CONTAINSTABLE(fulltexttable, Keywords, 'FORMSOF(INFLECTIONAL, "glass")'))"
> Every so often we get a slow perfoming query with these. Putting them
> into
> Management Studio and asking for a query plan shows its the remote scan
> consuming all the time. Hovering the mouse over the "Remote Scan" to get
> the
> statistics gives wildly inaccurate estimates of number of rows, and then
> also
> gives impossible number of "Actual Rows Returned" e.g. on this query I
> have
> just done it has claims to have returned 13,350,965 rows - this from a
> table
> that only has approx 800,000 rows.
> The indexed columns may have duplicate words but checking the specific
> example the searched for word definately appears less that 800,000 times
> in
> the whole table.
> Any one got any suggestions?
> Thanks
> Chris
|||In the execution plan returned for this query, the remote scan cost is 90% of
the query. The plan says that the next stage that the FTS gets passed to is
a filter that takes 1% and reduces the 13,000,000 down to 110 rows (the final
number of rows returned). The next largest part of the query, at 4%, is an
Index Seek. The rest is made up of 1 merge join, 2 inner joins and a few
filters, but as these are only working on a small (approx 600) number of rows
they aren't appearing to take any time up.
Surely if the statistics are wrong, then the query planneris going to start
making bad choices about how to plan the query?
"Hilary Cotter" wrote:

> The statistics returned my SQL FTS are not accurate as you have discovered.
> You should work on tuning other parts of the query to remove spooling, etc.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "chrisredburn" <chrisredburn@.discussions.microsoft.com> wrote in message
> news:DC403EF1-81FC-42FE-BF36-AA8457E48755@.microsoft.com...
>
>
|||A profiler trace of the query, showing SQL:BatchCompleted and
SQL:FullTextQuery events for the query, has...
SQL:FullTextQuey Duration = 164596
SQL:BatchCompleted Duration = 165274
|||I'd recommend look at a couple of 2 options:
1) use top_n_by_rank parameter if don't have to retrieve all of the results
but just a window (say top 100). The "n" value for the should be in this case
100*avg selectivity of the non-ft part (or actually smaller - the smaller,
the better)
2) If this is a plan issue, consider using OPTIMIZE FOR parameter to hint
the FT search string to CONTAINSTABLE.
3) make sure your index is not too fragmented; reorganization of an index
will improve card estimates. FTS cardinality estimation typically works ok
for single terms but worse for expressions.
Regards,
-Denis.
"chrisredburn" wrote:
[vbcol=seagreen]
> In the execution plan returned for this query, the remote scan cost is 90% of
> the query. The plan says that the next stage that the FTS gets passed to is
> a filter that takes 1% and reduces the 13,000,000 down to 110 rows (the final
> number of rows returned). The next largest part of the query, at 4%, is an
> Index Seek. The rest is made up of 1 merge join, 2 inner joins and a few
> filters, but as these are only working on a small (approx 600) number of rows
> they aren't appearing to take any time up.
> Surely if the statistics are wrong, then the query planneris going to start
> making bad choices about how to plan the query?
>
> "Hilary Cotter" wrote:

Incorrect query results with embedded comments

The following two identical queries return different
results. The first one with comments embedded in the query
returns all rows (ignoring the condition after the
comment). The second query with no comment works fine.
My SQL Server version is:
Microsoft SQL Server 2000 - 8.00.679 (Intel X86) Aug 26
2002 15:09:48 Copyright (c) 1988-2000 Microsoft
Corporation Enterprise Edition on Windows NT 5.2 (Build
3718: )
--
select CLEC_EU_DISCONNECT_INFORMATION.DNUM
CLEC_EU_DISCONNECT_INFORMATION_DNUM,
CLEC_TXN_PON.PON_VER
CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
left outer join CLEC_EU_DISCONNECT_INFORMATION
on
CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATION
.DISC_INFO_PON_VER
and
CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DISC
_INFO_LOCNUM
--left outer join CLEC_EU_TRANSFER_CALLS on
--
CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TRA
NSFER_CALLS.TC_LOCNUM
--and
CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_TR
ANSFER_CALLS.TC_PON_VER
--and
CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS.
TC_DNUM
where CLEC_TXN_PON.PON_VER='217-00'
select CLEC_EU_DISCONNECT_INFORMATION.DNUM
CLEC_EU_DISCONNECT_INFORMATION_DNUM,
CLEC_TXN_PON.PON_VER
CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
left outer join CLEC_EU_DISCONNECT_INFORMATION
on
CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATION
.DISC_INFO_PON_VER
and
CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DISC
_INFO_LOCNUM
where CLEC_TXN_PON.PON_VER='217-00'Shame this lost it's formatting - but I'm guessing all the
joins are on single lines.
I've come across this a couple of times where comments
mess up (or help) a query but never with v2000.
Look at syscomments to see what the query looks like.
Especially the line termination character on the last line.
It's worthwhile just retyping the query too.
Are you using query analyser?
>--Original Message--
>The following two identical queries return different
>results. The first one with comments embedded in the
query
>returns all rows (ignoring the condition after the
>comment). The second query with no comment works fine.
>My SQL Server version is:
>Microsoft SQL Server 2000 - 8.00.679 (Intel X86) Aug
26
>2002 15:09:48 Copyright (c) 1988-2000 Microsoft
>Corporation Enterprise Edition on Windows NT 5.2 (Build
>3718: )
>--
>
>select CLEC_EU_DISCONNECT_INFORMATION.DNUM
>CLEC_EU_DISCONNECT_INFORMATION_DNUM,
>CLEC_TXN_PON.PON_VER
>CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
>from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
>on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
>left outer join CLEC_EU_DISCONNECT_INFORMATION
>on
>CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
N
>..DISC_INFO_PON_VER
>and
>CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
C
>_INFO_LOCNUM
>--left outer join CLEC_EU_TRANSFER_CALLS on
>--
>CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TR
A
>NSFER_CALLS.TC_LOCNUM
>--and
>CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_T
R
>ANSFER_CALLS.TC_PON_VER
>--and
>CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS
.
>TC_DNUM
>where CLEC_TXN_PON.PON_VER='217-00'
>
>select CLEC_EU_DISCONNECT_INFORMATION.DNUM
>CLEC_EU_DISCONNECT_INFORMATION_DNUM,
>CLEC_TXN_PON.PON_VER
>CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
>from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
>on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
>left outer join CLEC_EU_DISCONNECT_INFORMATION
>on
>CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
N
>..DISC_INFO_PON_VER
>and
>CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
C
>_INFO_LOCNUM
>where CLEC_TXN_PON.PON_VER='217-00'
>.
>|||Hi Swami,
Does the problem occur if you place the two queries in Query Analyzer? I
think the command is not correctly generated.
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
--
| Content-Class: urn:content-classes:message
| From: "Swami Muthuvelu" <swami@.mclsystems.com>
| Sender: "Swami Muthuvelu" <swami@.mclsystems.com>
| References: <00b301c340c4$a033c1a0$a501280a@.phx.gbl>
| Subject: Incorrect query results with embedded comments
| Date: Wed, 2 Jul 2003 11:34:41 -0700
| Lines: 78
| Message-ID: <463701c340c8$99cc8460$a401280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcNAyJnM9s4j20agSQe1sjFP5SfVvw==| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa09.phx.gbl
| Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.server:22629
| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| The query mentioned below was a programmatically generated
| query, with lines ending with "Carriage return" (char(13))
| character, and did not have line feed (char(10)). When
| such generated SQL was copied and pasted into SQL Query
| analyzer displayed perfectly fine, but did not recognize
| the break of the line.
|
|
| >--Original Message--
| >The following two identical queries return different
| >results. The first one with comments embedded in the
| query
| >returns all rows (ignoring the condition after the
| >comment). The second query with no comment works fine.
| >
| >My SQL Server version is:
| >
| >Microsoft SQL Server 2000 - 8.00.679 (Intel X86) Aug
| 26
| >2002 15:09:48 Copyright (c) 1988-2000 Microsoft
| >Corporation Enterprise Edition on Windows NT 5.2 (Build
| >3718: )
| >
| >--
| >
| >
| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
| >CLEC_TXN_PON.PON_VER
| >CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
|
| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
| >left outer join CLEC_EU_DISCONNECT_INFORMATION
| >on
| >CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
| N
| >..DISC_INFO_PON_VER
| >and
| >CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
| C
| >_INFO_LOCNUM
| >--left outer join CLEC_EU_TRANSFER_CALLS on
| >--
| >CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TR
| A
| >NSFER_CALLS.TC_LOCNUM
| >--and
| >CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_T
| R
| >ANSFER_CALLS.TC_PON_VER
| >--and
| >CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS
| .
| >TC_DNUM
| >where CLEC_TXN_PON.PON_VER='217-00'
| >
| >
| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
| >CLEC_TXN_PON.PON_VER
| >CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
|
| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
| >left outer join CLEC_EU_DISCONNECT_INFORMATION
| >on
| >CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
| N
| >..DISC_INFO_PON_VER
| >and
| >CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
| C
| >_INFO_LOCNUM
| >where CLEC_TXN_PON.PON_VER='217-00'
| >
| >.
| >
||||Here are two queries. Each one generates a SQL query. Copy
and paste the results of the query to the query pane of
query analyzer and run it. Both looks identical. The first
one does not work, but the second one does.
--
select 'select * ' + char(13) + '--comment--' + char(13)
+ 'from sysobjects'
select 'select * ' + char(13) + '--comment--' + char(10)
+ 'from sysobjects'
--
You are not considering a carriage return as a line break
in the server engine, but you are considering as a line
break for the query analyzer.
Swami
>--Original Message--
>Hi Swami,
>Does the problem occur if you place the two queries in
Query Analyzer? I
>think the command is not correctly generated.
>
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Regards,
>Bill Cheng
>Microsoft Support Engineer
>--
>| Content-Class: urn:content-classes:message
>| From: "Swami Muthuvelu" <swami@.mclsystems.com>
>| Sender: "Swami Muthuvelu" <swami@.mclsystems.com>
>| References: <00b301c340c4$a033c1a0$a501280a@.phx.gbl>
>| Subject: Incorrect query results with embedded comments
>| Date: Wed, 2 Jul 2003 11:34:41 -0700
>| Lines: 78
>| Message-ID: <463701c340c8$99cc8460$a401280a@.phx.gbl>
>| MIME-Version: 1.0
>| Content-Type: text/plain;
>| charset="iso-8859-1"
>| Content-Transfer-Encoding: 7bit
>| X-Newsreader: Microsoft CDO for Windows 2000
>| Thread-Index: AcNAyJnM9s4j20agSQe1sjFP5SfVvw==>| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>| Newsgroups: microsoft.public.sqlserver.server
>| Path: cpmsftngxa09.phx.gbl
>| Xref: cpmsftngxa09.phx.gbl
microsoft.public.sqlserver.server:22629
>| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>| X-Tomcat-NG: microsoft.public.sqlserver.server
>|
>| The query mentioned below was a programmatically
generated
>| query, with lines ending with "Carriage return" (char
(13))
>| character, and did not have line feed (char(10)). When
>| such generated SQL was copied and pasted into SQL Query
>| analyzer displayed perfectly fine, but did not
recognize
>| the break of the line.
>|
>|
>| >--Original Message--
>| >The following two identical queries return different
>| >results. The first one with comments embedded in the
>| query
>| >returns all rows (ignoring the condition after the
>| >comment). The second query with no comment works fine.
>| >
>| >My SQL Server version is:
>| >
>| >Microsoft SQL Server 2000 - 8.00.679 (Intel X86)
Aug
>| 26
>| >2002 15:09:48 Copyright (c) 1988-2000 Microsoft
>| >Corporation Enterprise Edition on Windows NT 5.2
(Build
>| >3718: )
>| >
>| >--
>| >
>| >
>| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
>| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
>| >CLEC_TXN_PON.PON_VER
>|
>CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
>|
>| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
>| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
>| >left outer join CLEC_EU_DISCONNECT_INFORMATION
>| >on
>|
>CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
>| N
>| >..DISC_INFO_PON_VER
>| >and
>|
>CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
>| C
>| >_INFO_LOCNUM
>| >--left outer join CLEC_EU_TRANSFER_CALLS on
>| >--
>|
>CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TR
>| A
>| >NSFER_CALLS.TC_LOCNUM
>| >--and
>|
>CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_T
>| R
>| >ANSFER_CALLS.TC_PON_VER
>| >--and
>|
>CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS
>| .
>| >TC_DNUM
>| >where CLEC_TXN_PON.PON_VER='217-00'
>| >
>| >
>| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
>| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
>| >CLEC_TXN_PON.PON_VER
>|
>CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
>|
>| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
>| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
>| >left outer join CLEC_EU_DISCONNECT_INFORMATION
>| >on
>|
>CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
>| N
>| >..DISC_INFO_PON_VER
>| >and
>|
>CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
>| C
>| >_INFO_LOCNUM
>| >where CLEC_TXN_PON.PON_VER='217-00'
>| >
>| >.
>| >
>|
>.
>|||Hi Swami,
I think the comment to be generated inside a single query causes the
problem. Could you make it generated before or after the query?
In addition, none of the query works on my side in Query Analyzer.
select 'select * ' + char(13) + '--comment--' + char(13)
+ 'from sysobjects'
select 'select * ' + char(13) + '--comment--' + char(10)
+ 'from sysobjects'
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
--
| Content-Class: urn:content-classes:message
| From: "Swami Muthuvelu" <swami@.mclsystems.com>
| Sender: "Swami Muthuvelu" <swami@.mclsystems.com>
| References: <00b301c340c4$a033c1a0$a501280a@.phx.gbl>
<463701c340c8$99cc8460$a401280a@.phx.gbl>
<8zvyhVVQDHA.412@.cpmsftngxa09.phx.gbl>
| Subject: RE: Incorrect query results with embedded comments
| Date: Thu, 3 Jul 2003 07:06:21 -0700
| Lines: 155
| Message-ID: <0a1e01c3416c$47c51e80$a501280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcNBbEfCiVpFmLFfSJ+NOObv2qtfuw==| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa09.phx.gbl
| Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.server:22750
| NNTP-Posting-Host: TK2MSFTNGXA13 10.40.1.165
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Here are two queries. Each one generates a SQL query. Copy
| and paste the results of the query to the query pane of
| query analyzer and run it. Both looks identical. The first
| one does not work, but the second one does.
|
| --
| select 'select * ' + char(13) + '--comment--' + char(13)
| + 'from sysobjects'
|
| select 'select * ' + char(13) + '--comment--' + char(10)
| + 'from sysobjects'
| --
|
| You are not considering a carriage return as a line break
| in the server engine, but you are considering as a line
| break for the query analyzer.
|
| Swami
|
|
|
| >--Original Message--
| >Hi Swami,
| >
| >Does the problem occur if you place the two queries in
| Query Analyzer? I
| >think the command is not correctly generated.
| >
| >
| >This posting is provided "AS IS" with no warranties, and
| confers no rights.
| >
| >Regards,
| >
| >Bill Cheng
| >Microsoft Support Engineer
| >--
| >| Content-Class: urn:content-classes:message
| >| From: "Swami Muthuvelu" <swami@.mclsystems.com>
| >| Sender: "Swami Muthuvelu" <swami@.mclsystems.com>
| >| References: <00b301c340c4$a033c1a0$a501280a@.phx.gbl>
| >| Subject: Incorrect query results with embedded comments
| >| Date: Wed, 2 Jul 2003 11:34:41 -0700
| >| Lines: 78
| >| Message-ID: <463701c340c8$99cc8460$a401280a@.phx.gbl>
| >| MIME-Version: 1.0
| >| Content-Type: text/plain;
| >| charset="iso-8859-1"
| >| Content-Transfer-Encoding: 7bit
| >| X-Newsreader: Microsoft CDO for Windows 2000
| >| Thread-Index: AcNAyJnM9s4j20agSQe1sjFP5SfVvw==| >| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| >| Newsgroups: microsoft.public.sqlserver.server
| >| Path: cpmsftngxa09.phx.gbl
| >| Xref: cpmsftngxa09.phx.gbl
| microsoft.public.sqlserver.server:22629
| >| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| >| X-Tomcat-NG: microsoft.public.sqlserver.server
| >|
| >| The query mentioned below was a programmatically
| generated
| >| query, with lines ending with "Carriage return" (char
| (13))
| >| character, and did not have line feed (char(10)). When
| >| such generated SQL was copied and pasted into SQL Query
| >| analyzer displayed perfectly fine, but did not
| recognize
| >| the break of the line.
| >|
| >|
| >| >--Original Message--
| >| >The following two identical queries return different
| >| >results. The first one with comments embedded in the
| >| query
| >| >returns all rows (ignoring the condition after the
| >| >comment). The second query with no comment works fine.
| >| >
| >| >My SQL Server version is:
| >| >
| >| >Microsoft SQL Server 2000 - 8.00.679 (Intel X86)
| Aug
| >| 26
| >| >2002 15:09:48 Copyright (c) 1988-2000 Microsoft
| >| >Corporation Enterprise Edition on Windows NT 5.2
| (Build
| >| >3718: )
| >| >
| >| >--
| >| >
| >| >
| >| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
| >| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
| >| >CLEC_TXN_PON.PON_VER
| >|
| >CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
| >|
| >| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
| >| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
| >| >left outer join CLEC_EU_DISCONNECT_INFORMATION
| >| >on
| >|
| >CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
| >| N
| >| >..DISC_INFO_PON_VER
| >| >and
| >|
| >CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
| >| C
| >| >_INFO_LOCNUM
| >| >--left outer join CLEC_EU_TRANSFER_CALLS on
| >| >--
| >|
| >CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TR
| >| A
| >| >NSFER_CALLS.TC_LOCNUM
| >| >--and
| >|
| >CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_T
| >| R
| >| >ANSFER_CALLS.TC_PON_VER
| >| >--and
| >|
| >CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS
| >| .
| >| >TC_DNUM
| >| >where CLEC_TXN_PON.PON_VER='217-00'
| >| >
| >| >
| >| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
| >| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
| >| >CLEC_TXN_PON.PON_VER
| >|
| >CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
| >|
| >| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
| >| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
| >| >left outer join CLEC_EU_DISCONNECT_INFORMATION
| >| >on
| >|
| >CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
| >| N
| >| >..DISC_INFO_PON_VER
| >| >and
| >|
| >CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
| >| C
| >| >_INFO_LOCNUM
| >| >where CLEC_TXN_PON.PON_VER='217-00'
| >| >
| >| >.
| >| >
| >|
| >
| >.
| >
|sql

incorrect number of rows

I have a table 'detail_curr' with a column called
'tams_id'.
1. If the column is indexed (non-cluster,non-unique),
select count(*) from detail_curr
where tams_id is null;
(result): 4003464
2. If the column is NOT indexed,
(result): 3902727
What's wrong? Any help is appreciated.Check out:
http://support.microsoft.com/default.aspx?scid=kb;en-us;814509
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"yren" <yren@.cc3.com> wrote in message
news:065801c35c8b$8d2f1b30$a501280a@.phx.gbl...
> I have a table 'detail_curr' with a column called
> 'tams_id'.
> 1. If the column is indexed (non-cluster,non-unique),
> select count(*) from detail_curr
> where tams_id is null;
> (result): 4003464
> 2. If the column is NOT indexed,
> (result): 3902727
> What's wrong? Any help is appreciated.|||Thanks. That's very helpful.
yren
>--Original Message--
>Check out:
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;814509
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>--
>SQL FAQ links (courtesy Neil Pike):
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>--
>

Incorrect date in column for 25,000 rows

I have a table with a column in it called Date, which is of the type DateTime, and for the last two years I have been adding data which I found out was incorrect.

My dates are all a day in the future, so I need to reduce each date by one day.

I can easily use a select script to reveal the 25,000 rows which are all incorrect dates. But I can't figure out how to update each and every row to subtract one day from each date.

So where I have:

26/01/2005

I would like to have:

25/01/2005

and of course for every record. Obviously way too many to do manually :-(

Can anyone show me a script that will get what I'm after.

Tia

Tailwag

After one your 25000 rows with invalid data, that is not nice to find out. But it is easy to solve, just run this query:

UPDATE [MyTable] SET [MyDate] = DATEADD(year, -1, [MyDate])

Don't forget to replace MyTable with your table name and MyDate with your datetime field name.
I have tested it before posting this solution, because i don't want to be responsible for lozing 25000 rows on friday.

|||

Also, replace year with day!

So the update query must be:

UPDATE [MyTable] SET [MyDate] = DATEADD(day, -1, [MyDate])

- Jeroen Boiten

|||Indeed! Thanks Jeroen, i overlooked that one. Thought only the years where incorrect.|||

Thank you PJ, and also Jeroen. I ran the query and 'Viola' it worked flawlessly, you guys are now extremely high on my best friends of all time list

Tia.

Tailwag

P.S. The B_ _ _ _ Friday curse has finally been thwarted!!!

Inconsistent sp_spaceused

SQL 2000 Enterprise, SP4, 8.00.2175
sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
name rows reserved data index_size
unused
-- -- -- -- --
--
table 0 7248 KB 5032 KB 32 KB
2184 KB
How come my table has 0 records yet still occupies space?Check out DBCC UPDATEUSAGE and the ROWS_COUNT option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> SQL 2000 Enterprise, SP4, 8.00.2175
> sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> name rows reserved data index_size
> unused
> -- -- -- -- --
> --
> table 0 7248 KB 5032 KB 32 KB
> 2184 KB
> How come my table has 0 records yet still occupies space?|||Nope, didn't help a bit: still shows 0 records yet roughly 7MB of taken space.
"Tibor Karaszi" wrote:
> Check out DBCC UPDATEUSAGE and the ROWS_COUNT option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
> news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> > SQL 2000 Enterprise, SP4, 8.00.2175
> >
> > sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> >
> > name rows reserved data index_size
> > unused
> > -- -- -- -- --
> > --
> > table 0 7248 KB 5032 KB 32 KB
> > 2184 KB
> >
> > How come my table has 0 records yet still occupies space?
>
>|||Leon Shargorodsky,
if you dropped a variable length column, you can reclaim the space using
"dbcc cleantable".
If it is a heap (table without clustered index), create a clustered index
and if you do not want to keep it then drop it.
If it is not a heap, use "dbcc dbreindex" or "alter index ... rebuild" if
you are using 2005.
AMB
"Leon Shargorodsky" wrote:
> Nope, didn't help a bit: still shows 0 records yet roughly 7MB of taken space.
> "Tibor Karaszi" wrote:
> > Check out DBCC UPDATEUSAGE and the ROWS_COUNT option.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
> > news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> > > SQL 2000 Enterprise, SP4, 8.00.2175
> > >
> > > sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> > >
> > > name rows reserved data index_size
> > > unused
> > > -- -- -- -- --
> > > --
> > > table 0 7248 KB 5032 KB 32 KB
> > > 2184 KB
> > >
> > > How come my table has 0 records yet still occupies space?
> >
> >
> >|||sp_spaceused is not guaranteed to provide actual, up-to-the-minute correct
values. DO NOT rely on it for such.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> SQL 2000 Enterprise, SP4, 8.00.2175
> sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> name rows reserved data index_size
> unused
> -- -- -- -- --
> --
> table 0 7248 KB 5032 KB 32 KB
> 2184 KB
> How come my table has 0 records yet still occupies space?

Inconsistent sp_spaceused

When I run sp_spaceused 'table', @.updateusage = 'TRUE', here is what I get:
name rows reserved data index_size unused
-- -- -- -- -- --
table 0 7253 KB 5020 KB 32 KB 2201 KB
How come that empty table still occupies lots of space?It seems that in some moment before such table contained rows (and had an
index).
Try a DBCC UPDATEUSAGE on the whole database or DBCC CHECKTABLE on the
interested table
"Leon Shargorodsky" wrote:
> When I run sp_spaceused 'table', @.updateusage = 'TRUE', here is what I get:
> name rows reserved data index_size unused
> -- -- -- -- -- --
> table 0 7253 KB 5020 KB 32 KB 2201 KB
> How come that empty table still occupies lots of space?

Inconsistent sp_spaceused

SQL 2000 Enterprise, SP4, 8.00.2175
sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
name rows reserved data index_size
unused
-- -- -- -- --
--
table 0 7248 KB 5032 KB 32 KB
2184 KB
How come my table has 0 records yet still occupies space?Check out DBCC UPDATEUSAGE and the ROWS_COUNT option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in me
ssage
news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> SQL 2000 Enterprise, SP4, 8.00.2175
> sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> name rows reserved data index_size
> unused
> -- -- -- -- --
--
> --
> table 0 7248 KB 5032 KB 32 KB
> 2184 KB
> How come my table has 0 records yet still occupies space?|||Nope, didn't help a bit: still shows 0 records yet roughly 7MB of taken spac
e.
"Tibor Karaszi" wrote:

> Check out DBCC UPDATEUSAGE and the ROWS_COUNT option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message
> news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
>
>|||Leon Shargorodsky,
if you dropped a variable length column, you can reclaim the space using
"dbcc cleantable".
If it is a heap (table without clustered index), create a clustered index
and if you do not want to keep it then drop it.
If it is not a heap, use "dbcc dbreindex" or "alter index ... rebuild" if
you are using 2005.
AMB
"Leon Shargorodsky" wrote:
[vbcol=seagreen]
> Nope, didn't help a bit: still shows 0 records yet roughly 7MB of taken sp
ace.
> "Tibor Karaszi" wrote:
>|||sp_spaceused is not guaranteed to provide actual, up-to-the-minute correct
values. DO NOT rely on it for such.
TheSQLGuru
President
Indicium Resources, Inc.
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> SQL 2000 Enterprise, SP4, 8.00.2175
> sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> name rows reserved data index_size
> unused
> -- -- -- -- --
--
> --
> table 0 7248 KB 5032 KB 32 KB
> 2184 KB
> How come my table has 0 records yet still occupies space?

Inconsistent sp_spaceused

When I run sp_spaceused 'table', @.updateusage = 'TRUE', here is what I get:
name rows reserved data index_size unused
-- -- -- -- -- --
table 0 7253 KB 5020 KB 32 KB 2201 KB
How come that empty table still occupies lots of space?It seems that in some moment before such table contained rows (and had an
index).
Try a DBCC UPDATEUSAGE on the whole database or DBCC CHECKTABLE on the
interested table
"Leon Shargorodsky" wrote:

> When I run sp_spaceused 'table', @.updateusage = 'TRUE', here is what I get
:
> name rows reserved data index_size unused
> -- -- -- -- -- --
> table 0 7253 KB 5020 KB 32 KB 2201 KB
> How come that empty table still occupies lots of space?

Inconsistent Results after Update

I have never seen anything like this, so I am quite baffled:

I have a large (6 million rows) table in a data warehouse. Because of a new user requirement, I ran an update on that table to update two columns changing the value from null to a 'real' value.

I ran the update and it completed in 14 minutes.

Now I query the table searching for a count of the records where the value in one of the columns is null. And I keep getting different answers; the results vary by as much as 100,000 records.

Here are the scripts:

CREATE TABLE TASKTRN (
TASKTRNKEY VARCHAR(10) NOT NULL,
TASKHDRKEY VARCHAR(10) NOT NULL,
TASKDTLKEY VARCHAR(10) NULL,
RECEIPTKEY VARCHAR(20) NULL,
RECEIPTLINE VARCHAR(5) NULL,
TASKTYPE INT NOT NULL
)
GO

ALTER TABLE TASKTRN ADD
CONSTRAINT PK_TASKTRN PRIMARY KEY CLUSTERED (TASKTRNKEY)
GO

CREATE INDEX TASKTRN_TASKHDRKEY ON TASKTRN (TASKHDRKEY)
GO

CREATE INDEX TASKTRN_RECEIPTKEY ON TASKTRN (RECEIPTKEY)
GO

/****** Object: Table [dbo].[TASK_TMP] Script Date: 03/21/2003 12:27:40 ******/
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TASK_TMP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [TASK_TMP] (
[TASKTRNKEY] [char] (10) NOT NULL ,
[TASKHDRKEY] [char] (10) NULL ,
[RECEIPTKEY] [char] (10) NULL ,
[RECEIPTLINE] [char] (5) NULL
) ON [PRIMARY]
END

Print 'Created Temp table'

CREATE INDEX TASK_TMP_TASKTRNKEY ON TASK_TMP (TASKTRNKEY)

CREATE INDEX TASK_TMP_TASKHDRKEY ON TASK_TMP (TASKHDRKEY)

Print 'created Indexes'

INSERT INTO TASK_TMP
SELECT TASKTRNKEY, TASKHDRKEY, RECEIPTKEY, RECEIPTLINE
FROM TASKTRN
WHERE TASKTYPE = 1 AND RECEIPTKEY IS NULL

Print 'Insert Records'

UPDATE TASK_TMP
SET RECEIPTKEY = B.RECEIPTKEY, RECEIPTLINE = B.RECEIPTLINE
FROM
TASK_TMP A JOIN
(SELECT TASKHDRKEY, RECEIPTKEY, RECEIPTLINE
FROM TASKTRN
WHERE TASKTYPE = 1 AND RECEIPTKEY IS NOT NULL) B ON

A.TASKHDRKEY = B.TASKHDRKEY

Print 'Updated null values in temp table'

UPDATE TASKTRN
SET RECEIPTKEY = B.RECEIPTKEY, RECEIPTLINE = B.RECEIPTLINE
FROM
TASKTRN A JOIN
TASK_TMP B ON
A.TASKTRNKEY = B.TASKTRNKEY

Print 'Updated null values in permanent table'

This is the SQL that generates the disparate results:

select count(tasktrnkey) From tasktrn_roc where tasktype = 1 and receiptkey is null

Does anyone have any idea what may be going on?

Regards,

Hugh Scottbad statistics after the update, maybe?

after the update (or large inserts), try running sp_updatestats and see if it affects your results.

running DBCC INDEXDEFRAG might be a good idea as well

-isaac|||Yep,

I did that and still came up with some funky results. Now the mystery deepens a little further.

I ran a two different queries:

SELECT COUNT(TASKTRNKEY) WHERE RECEIPTKEY IS NULL AND TASKTYPE = 1

SELECT COUNT(TASKTRNKEY) WHERE RECEIPTKEY IS NOT NULL AND TASKTYPE = 1

In theory the sum of these two queries should add up to:

SELECT COUNT(TASKTRNKEY) WHERE TASKTYPE = 1

Didn't work; the sum of the results from the first two queries is slightly less than twice the actual number of records where TASKTYPE = 1.

Finally, I ran this query:

SELECT
CASE
WHEN RECEIPTKEY IS NULL THEN 'Null'
ELSE 'Not Null'
END as 'RECEIPTKEY',
COUNT(TASKTRNKEY)
FROM
TASKTRN
WHERE
TASKTYPE = 1
GROUP BY
CASE
WHEN RECEIPTKEY IS NULL THEN 'Null'
ELSE 'Not Null'
END

This returned what I expected it to return. But I am baffled to explain why or why the other select statements return such bizarre and conflicting results.

Regards,

Hugh Scott

Originally posted by isaacfain
bad statistics after the update, maybe?

after the update (or large inserts), try running sp_updatestats and see if it affects your results.

running DBCC INDEXDEFRAG might be a good idea as well

-isaac

Incomplete data in excel fileshare -missing many rows

Hello,
I have a report with 35 columns, meant as a datafeed. The report uses a view
on the (separate) SQL server so it only needs to put the values in the table.
When ik run the report for medium datasets, everything works fine. However
when I run it for more then 20,000 rows the exel-sheet only contains about
16300 rows. The report is meant as a subscription with a fileshare on the
local server. When i run the report via the viewer, an export to excel is
timed out, however an export to csv works and contains all rows. Sadly this
is not the format in which i need to supply the data.
can anybody offer any assistance?What version of Excel are you using? Have you passed the row limit?
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
news:AD96B5F8-4A8C-4D2B-87B2-DE343CF8C3F6@.microsoft.com...
> Hello,
> I have a report with 35 columns, meant as a datafeed. The report uses a
> view
> on the (separate) SQL server so it only needs to put the values in the
> table.
> When ik run the report for medium datasets, everything works fine. However
> when I run it for more then 20,000 rows the exel-sheet only contains about
> 16300 rows. The report is meant as a subscription with a fileshare on the
> local server. When i run the report via the viewer, an export to excel is
> timed out, however an export to csv works and contains all rows. Sadly
> this
> is not the format in which i need to supply the data.
> can anybody offer any assistance?|||I kept testing in the weekeind and it's my bad. RS generates the file fine,
Our sending software seems to truncate the file. Sorry.
"Mary Bray [MVP]" wrote:
> What version of Excel are you using? Have you passed the row limit?
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
> news:AD96B5F8-4A8C-4D2B-87B2-DE343CF8C3F6@.microsoft.com...
> > Hello,
> >
> > I have a report with 35 columns, meant as a datafeed. The report uses a
> > view
> > on the (separate) SQL server so it only needs to put the values in the
> > table.
> >
> > When ik run the report for medium datasets, everything works fine. However
> > when I run it for more then 20,000 rows the exel-sheet only contains about
> > 16300 rows. The report is meant as a subscription with a fileshare on the
> > local server. When i run the report via the viewer, an export to excel is
> > timed out, however an export to csv works and contains all rows. Sadly
> > this
> > is not the format in which i need to supply the data.
> >
> > can anybody offer any assistance?
>
>

Including newline in SQL SELECT statement

Hi,

I'm trying to write a SQL SELECT statement where the phone numer ("telnr") is divided on three rows. How do I write a newline? I've tried \n, NEWLINE, and a few others.

Thanks in advance!

Pettrer, Sweden (VB, Sql Server, VWD Express, Asp.Net 2.0)

Code:

SelectCommand

="SELECT [gID], [enamn], [fnamn], telnr1 + ' ' + telnr2 + ' ' + telnr3 As telnr, [epost] FROM...The corresponding gridview's cell's value is

08-43 244 234 08-432

23 08-424333

and should be

08-43 244 234

08-432 23

08-424333

If you want to display it on your page,you can add <br> to break line.

SelectCommand

="SELECT [gID], [enamn], [fnamn], telnr1 + '<br />' + telnr2 + '<br />' + telnr3 As telnr, [epost] FROM...

|||

Hi,

Thanks but that doesn't work in tool tips. I think I'll solve it with commas instead. It's still an intesting question though.

Best,

Pettrer

|||In my opinion, this should be handled in the presentation layer, not in the stored procedure returning the data.|||

Hello,

Couldn't get that to work either. However, I converted the phone numbers nicely. MIght be of value to someone else.

aspx page (SELECT command for gridview):

SelectCommand

="SELECT [gID], [enamn], [fnamn], (COALESCE(telnr1,'') + ', ' + COALESCE(telnr2,'') + ', ' + COALESCE(telnr3,'')) As telnr, [epost] FROM [Grund] (etc.)

aspx.vb page:

ProtectedSub MemberGridView_RowDataBound(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.GridViewRowEventArgs)Handles MemberGridView.RowDataBound

If e.Row.RowType = DataControlRowType.DataRowThen'gets a phone symbol in the gridview if there is a phone no in the db. I have three phone no fields and the code below takes out the additional commas if they shouldn't be there (08-43243, , , becomes 08-43243 for example)Dim telnrAsString = e.Row.Cells(4).TextIf telnr.Length <= 5Then e.Row.Cells(4).Text =" "If telnr.Length > 5Then

e.Row.Cells(4).Text =

"<img src="http://pics.10026.com/?src="imgadmin/tfn.gif"">"Dim MyCharAsChar() = {" "c, ","c}'h?r tas mellanslag och komman utanf?r telnr bort

telnr = telnr.TrimStart(MyChar)

telnr = telnr.TrimEnd(MyChar)

e.Row.Cells(4).ToolTip = Replace(telnr,

" ,","")'tar bort ev " ," i mittenEndIf

Pettrer

|||

The following code works fine,you can try it :

<%@. Page Language="C#" AutoEventWireup="true" ValidateRequest="false" CodeFile="Gridview_Test.aspx.cs" Inherits="Gridview_Test" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> </div> <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="Column1" HeaderText="Column1" HtmlEncode="False" ReadOnly="True" SortExpression="Column1" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:pubsConnectionString%>" SelectCommand="SELECT [col] +'
</asp:SqlDataSource> </form></body></html>

Remember to set htmlencode to false.|||

yyy8347:

The following code works fine

Maybe, or maybe not, anyway I do not see how it applies to this topic... or to this forum...?

-LV

|||

;-)

P

Inaccurate HTML Rendering

(RS 2000)
I have 6 rows of text boxes in my body header running the width of the
report. All look fine in the report designer and when rendered to Adobe.
When I output to HTML, there are an extra 2 blank rows between the 5 and 6
TB. All the TB properties are exactly the same; I have tried
deleting/recreating, shifting around, nothing seems to help.
Thanks for any suggestionsFor anyone else that has the same problem I ran into where the HTML output
looks different than the designer or other output: I found if I slightly
reduced the vertical height of all the text boxes it fixed the problem.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:%23zD8P27TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> (RS 2000)
> I have 6 rows of text boxes in my body header running the width of the
> report. All look fine in the report designer and when rendered to Adobe.
> When I output to HTML, there are an extra 2 blank rows between the 5 and 6
> TB. All the TB properties are exactly the same; I have tried
> deleting/recreating, shifting around, nothing seems to help.
> Thanks for any suggestions
>