different row count, but same condition ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

different row count, but same condition ?

Author

Topic

ingo
Starting Member

4 Posts

Posted - 2005-03-28 : 14:36:25

hello,

i want to do a case sensitive search using wildcards, it works well with something like

select <colA,colB,colC,...> from <table> WHERE (( <colA> LIKE '%foo%' COLLATE SQL_Latin1_General_CP1_CS_AS) AND (<colB> LIKE '%foo%' COLLATE SQL_Latin1_General_CP1_CS_AS) AND ....... )

my PROBLEM now:
when using

select count(*) from <table> WHERE ((<colA> LIKE '%foo%' COLLATE SQL_Latin1_General_CP1_CS_AS) AND (<colB> LIKE '%foo%' COLLATE SQL_Latin1_General_CP1_CS_AS) AND ....... )

i get a different row count than the select statement returns (tested with the sql query analyzer).
when not using wildcards "%" this problem does not occur..

but how are wildcards related to collations and why can row counts differ anyway ?

i penetrated the search function of this forum since several hours, but i still dont have a solution to my problem, so i give a try now ...

if needed, i will post the table definition (~50 columns, mostly varchar, some are text) ...

amachanic
SQL Server MVP

169 Posts

Posted - 2005-03-28 : 17:37:57

Check out the execution plans to see if different indexes are being used for the COUNT(*) vs. the select w/ column list. You might have some corruption in one of your indexes. You can run DBCC CHECKTABLE as well to see if it detects anything. If you do find that an index is causing the problem, it might be easier to just drop and recreate it than to try repairing it, but YMMV on that one :)

---
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

ingo
Starting Member

4 Posts

Posted - 2005-03-29 : 10:00:27

thanks for this fast answer ...
I checked what you mentioned:

first:

according to the execution plans, there is only one unique clustered index on the primary key column (type int), which is used by both queries.

In the execution plan I found, that the filters argument contains this like() function for each column:

'like(Convert([fred2].[<columnName>]), '%foo%',NULL)'

Is it possible that the filter behaves unexpected ? Because, the statistics, of the clustered index scan at the start of the execution plan are the same for both queries, except the cost which is at 100% for the select with columns and 0% for the select with count (probably because of the non-existence of indicies).

second:

DBCC CHECKTABLE ('fred2') detects nothing, it gives me:


DBCC results for 'berta.fred2'.
There are 2120 rows in 61 pages for object 'berta.fred2'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

'fred2' is the tablename, 'berta' the user

I'm rather new to sql server, so please be patient with me ;)

amachanic
SQL Server MVP

169 Posts

Posted - 2005-03-29 : 10:23:12

Can you run SET STATISTICS PROFILE ON and then paste the StmtText columns returned for both queries?

That might give some better insight into what's happening.

---
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

ingo
Starting Member

4 Posts

Posted - 2005-03-30 : 18:08:55

with "SET STATISTICS PROFILE ON":
( queries executed with "sqlcmd" commandline utility, because the query analyzer has a fixed maximum width for each column, so the stmtText column gets cut-off somewhere in the middle ...)

A) the "SELECT COUNT(*) ...." query gives me this StmtText column:

SELECT COUNT(*) FROM fred2 WHERE (dbsitename='0' OR dbsiteacc='no') AND ((( NR LIKE 'br%') OR ( Project LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Project_Other LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Report_Nr LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Spin_No LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Su_No LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Engine_No LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Cycle_and_Hours LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Module LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Module_Other LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Part LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Part_Other LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Material_Non_corrosion_r LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Material_Corrosion_resis LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Material_Aluminium__allo LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Material_Magnesium_alloy LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Material_Titanium_alloys LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Material_Nickel___cobalt LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Material_Others LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Material_Remarks LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Manufacturing_Process LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Manufacturing__Chemical_Electr LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Manufacturing__Hardened LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Manufacturing__Coated LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Manufacturing__Other LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Reason_for_Investigation LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Reason_for_Inv_Other LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Investigation__Visual LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Investigation__Sections LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Investigation__SEM LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Investigation__Analysis LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Investigation__NDT LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Investigation__Additional LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Failure_Type___Mechanical LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Failure_Type___Thermal LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Failure_Type___Wear LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Failure_Type___Corrosion___non LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Failure_Type___Other LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Cause_of_failure LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Cause_of_failu_Component_speci LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Cause_of_failu_Material LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Cause_of_failu_Service_Develop LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Cause_of_failu_Manufacture___O LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Cause_of_failu_Other LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Size_of_damage__length___depth LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Site LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Link LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Reference LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Remark LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) ))

|--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1005])))

|--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))

|--Filter(WHERE:((((((((((((((((((((((((((((((((((((((((((((((((like(Convert([fred2].[NR]), 'br%', NULL) OR like(Convert([fred2].[Project]), 'br%', NULL)) OR like(Convert([fred2].[Project_Other]), 'br%', NULL)) OR like(Convert([fred2].[Report_Nr]), 'br%', NULL)) OR like(Convert([fred2].[Spin_No]), 'br%', NULL)) OR like(Convert([fred2].[Su_No]), 'br%', NULL)) OR like(Convert([fred2].[Engine_No]), 'br%', NULL)) OR like(Convert([fred2].[Cycle_and_Hours]), 'br%', NULL)) OR like(Convert([fred2].[Module]), 'br%', NULL)) OR like(Convert([fred2].[Module_Other]), 'br%', NULL)) OR like(Convert([fred2].[Part]), 'br%', NULL)) OR like(Convert([fred2].[Part_Other]), 'br%', NULL)) OR like(Convert([fred2].[Material_Non_corrosion_r]), 'br%', NULL)) OR like(Convert([fred2].[Material_Corrosion_resis]), 'br%', NULL)) OR like(Convert([fred2].[Material_Aluminium__allo]), 'br%', NULL)) OR like(Convert([fred2].[Material_Magnesium_alloy]), 'br%', NULL)) OR like(Convert([fred2].[Material_Titanium_alloys]), 'br%', NULL)) OR like(Convert([fred2].[Material_Nickel___cobalt]), 'br%', NULL)) OR like(Convert([fred2].[Material_Others]), 'br%', NULL)) OR like(Convert([fred2].[Material_Remarks]), 'br%', NULL)) OR like(Convert([fred2].[Manufacturing_Process]), 'br%', NULL)) OR like(Convert([fred2].[Manufacturing__Chemical_Electr]), 'br%', NULL)) OR like(Convert([fred2].[Manufacturing__Hardened]), 'br%', NULL)) OR like(Convert([fred2].[Manufacturing__Coated]), 'br%', NULL)) OR like(Convert([fred2].[Manufacturing__Other]), 'br%', NULL)) OR like(Convert([fred2].[Reason_for_Investigation]), 'br%', NULL)) OR like(Convert([fred2].[Reason_for_Inv_Other]), 'br%', NULL)) OR like(Convert([fred2].[Investigation__Visual]), 'br%', NULL)) OR like(Convert([fred2].[Investigation__Sections]), 'br%', NULL)) OR like(Convert([fred2].[Investigation__SEM]), 'br%', NULL)) OR like(Convert([fred2].[Investigation__Analysis]), 'br%', NULL)) OR like(Convert([fred2].[Investigation__NDT]), 'br%', NULL)) OR like(Convert([fred2].[Investigation__Additional]), 'br%', NULL)) OR like(Convert([fred2].[Failure_Type___Mechanical]), 'br%', NULL)) OR like(Convert([fred2].[Failure_Type___Thermal]), 'br%', NULL)) OR like(Convert([fred2].[Failure_Type___Wear]), 'br%', NULL)) OR like(Convert([fred2].[Failure_Type___Corrosion___non]), 'br%', NULL)) OR like(Convert([fred2].[Failure_Type___Other]), 'br%',NULL)) OR like(Convert([fred2].[Cause_of_failure]), 'br%', NULL)) OR like(Convert([fred2].[Cause_of_failu_Component_speci]), 'br%', NULL)) OR like(Convert([fred2].[Cause_of_failu_Material]), 'br%', NULL)) OR like(Convert([fred2].[Cause_of_failu_Service_Develop]), 'br%', NULL)) OR like(Convert([fred2].[Cause_of_failu_Manufacture___O]), 'br%', NULL)) OR like(Convert([fred2].[Cause_of_failu_Other]), 'br%', NULL)) OR like(Convert([fred2].[Size_of_damage__length___depth]), 'br%',NULL)) OR like(Convert([fred2].[Site]), 'br%', NULL)) OR like([fred2].[Link], 'br%', NULL)) OR like([fred2].[Reference], 'br%', NULL)) OR like([fred2].[Remark], 'br%', NULL)))

|--Clustered Index Scan(OBJECT:([kdi-test].[berta].[fred2].[PK__fred2__023D5A04]), WHERE:([fred2].[dbsitename]='0' OR [fred2].[dbsiteacc]='no'))

B) the "SELECT <columns> ...." query gives me this StmtText column:

SELECT dbsitename,NR,dbsiteacc,Project,Project_Other,Report_Nr,Spin_No,Su_No,Engine_No,Cycle_and_Hours,Module,Module_Other,Part,Part_Other,Material_Non_corrosion_r,Material_Corrosion_resis,Material_Aluminium__allo,Material_Magnesium_alloy,Material_Titanium_alloys,Material_Nickel___cobalt,Material_Others,Material_Remarks,Manufacturing_Process,Manufacturing__Chemical_Electr,Manufacturing__Hardened,Manufacturing__Coated,Manufacturing__Other,Reason_for_Investigation,Reason_for_Inv_Other,Investigation__Visual,Investigation__Sections,Investigation__SEM,Investigation__Analysis,Investigation__NDT,Investigation__Additional,Failure_Type___Mechanical,Failure_Type___Thermal,Failure_Type___Wear,Failure_Type___Corrosion___non,Failure_Type___Other,Cause_of_failure,Cause_of_failu_Component_speci,Cause_of_failu_Material,Cause_of_failu_Service_Develop,Cause_of_failu_Manufacture___O,Cause_of_failu_Other,Size_of_damage__length___depth,Site,Link,Reference,Remark FROM fred2 WHERE (dbsitename='0' OR dbsiteacc='no') AND ((( NR LIKE 'br%') OR
( Project LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Project_Other LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Report_Nr LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Spin_No LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Su_No LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Engine_No LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Cycle_and_Hours LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Module LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Module_Other LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Part LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Part_Other LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Material_Non_corrosion_r LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Material_Corrosion_resis LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Material_Aluminium__allo LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Material_Magnesium_alloy LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Material_Titanium_alloys LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Material_Nickel___cobalt LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Material_Others LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Material_Remarks LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Manufacturing_Process LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Manufacturing__Chemical_Electr LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Manufacturing__Hardened LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Manufacturing__Coated LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Manufacturing__Other LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Reason_for_Investigation LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Reason_for_Inv_Other LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Investigation__Visual LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Investigation__Sections LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Investigation__SEM LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Investigation__Analysis LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Investigation__NDT LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Investigation__Additional LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Failure_Type___Mechanical LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Failure_Type___Thermal LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Failure_Type___Wear LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Failure_Type___Corrosion___non LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Failure_Type___Other LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Cause_of_failure LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Cause_of_failu_Component_speci LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Cause_of_failu_Material LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Cause_of_failu_Service_Develop LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Cause_of_failu_Manufacture___O LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Cause_of_failu_Other LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Size_of_damage__length___depth LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Site LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Link LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Reference LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) OR ( Remark LIKE 'br%' COLLATE SQL_Latin1_General_CP1_CS_AS) ))

|--Compute Scalar(DEFINE:([fred2].[Remark]=[fred2].[Remark], [fred2].[Reference]=[fred2].[Reference], [fred2].[Link]=[fred2].[Link]))

|--Filter(WHERE:((((((((((((((((((((((((((((((((((((((((((((((((like(Convert([fred2].[NR]), 'br%', NULL) OR like(Convert([fred2].[Project]), 'br%', NULL)) OR like(Convert([fred2].[Project_Other]), 'br%', NULL)) OR like(Convert([fred2].[Report_Nr]), 'br%', NULL)) OR like(Convert([fred2].[Spin_No]), 'br%', NULL)) OR like(Convert([fred2].[Su_No]), 'br%', NULL)) OR like(Convert([fred2].[Engine_No]), 'br%', NULL)) OR like(Convert([fred2].[Cycle_and_Hours]), 'br%', NULL)) OR like(Convert([fred2].[Module]), 'br%', NULL)) OR like(Convert([fred2].[Module_Other]), 'br%', NULL)) OR like(Convert([fred2].[Part]), 'br%', NULL)) OR like(Convert([fred2].[Part_Other]), 'br%', NULL)) OR like(Convert([fred2].[Material_Non_corrosion_r]), 'br%', NULL)) OR like(Convert([fred2].[Material_Corrosion_resis]), 'br%', NULL)) OR like(Convert([fred2].[Material_Aluminium__allo]), 'br%', NULL)) OR like(Convert([fred2].[Material_Magnesium_alloy]),'br%', NULL)) OR like(Convert([fred2].[Material_Titanium_alloys]), 'br%', NULL)) OR like(Convert([fred2].[Material_Nickel___cobalt]), 'br%', NULL)) OR like(Convert([fred2].[Material_Others]), 'br%', NULL)) OR like(Convert([fred2].[Material_Remarks]), 'br%', NULL)) OR like(Convert([fred2].[Manufacturing_Process]), 'br%', NULL)) OR like(Convert([fred2].[Manufacturing__Chemical_Electr]), 'br%', NULL)) OR like(Convert([fred2].[Manufacturing__Hardened]), 'br%', NULL)) OR like(Convert([fred2].[Manufacturing__Coated]), 'br%', NULL)) OR like(Convert([fred2].[Manufacturing__Other]), 'br%', NULL)) OR like(Convert([fred2].[Reason_for_Investigation]), 'br%', NULL)) OR like(Convert([fred2].[Reason_for_Inv_Other]), 'br%', NULL)) OR like(Convert([fred2].[Investigation__Visual]), 'br%', NULL)) OR like(Convert([fred2].[Investigation__Sections]), 'br%', NULL)) OR like(Convert([fred2].[Investigation__SEM]), 'br%', NULL)) OR like(Convert([fred2].[Investigation__Analysis]), 'br%', NULL)) OR like(Convert([fred2].[Investigation__NDT]), 'br%', NULL)) OR like(Convert([fred2].[Investigation__Additional]), 'br%', NULL)) OR like(Convert([fred2].[Failure_Type___Mechanical]), 'br%', NULL)) OR like(Convert([fred2].[Failure_Type___Thermal]), 'br%', NULL)) OR like(Convert([fred2].[Failure_Type___Wear]), 'br%', NULL)) OR like(Convert([fred2].[Failure_Type___Corrosion___non]), 'br%', NULL)) OR like(Convert([fred2].[Failure_Type___Other]), 'br%', NULL)) OR like(Convert([fred2].[Cause_of_failure]), 'br%', NULL)) OR like(Convert([fred2].[Cause_of_failu_Component_speci]),'br%', NULL)) OR like(Convert([fred2].[Cause_of_failu_Material]), 'br%', NULL)) OR like(Convert([fred2].[Cause_of_failu_Service_Develop]), 'br%', NULL)) OR like(Convert([fred2].[Cause_of_failu_Manufacture___O]), 'br%', NULL)) OR like(Convert([fred2].[Cause_of_failu_Other]), 'br%', NULL)) OR like(Convert([fred2].[Size_of_damage__length___depth]), 'br%', NULL)) OR like(Convert([fred2].[Site]), 'br%', NULL)) OR like(Convert([fred2].[Link]), 'br%', NULL)) OR like(Convert([fred2].[Reference]), 'br%', NULL)) OR like(Convert([fred2].[Remark]), 'br%', NULL)))

|--Clustered Index Scan(OBJECT:([kdi-test].[berta].[fred2].[PK__fred2__023D5A04]), WHERE:([fred2].[dbsitename]='0' OR [fred2].[dbsiteacc]='no'))

A) gives me 666 rows as result, B) gives me 3 rows as result
columns remark, link, reference are of type text

hmm, ok a lot of text, but i think to see the reason here now:
at the end of the filter arguments of case A) there is no convert function around the last three columns (type text), so the sqlserver there does a case-insensitive search which results in a higher number (~600) of records found ...

hmm, but why does the filter not include the convert function for text-columns only when the COUNT(*) function is used ?

thanks in advance

amachanic
SQL Server MVP

169 Posts

Posted - 2005-03-31 : 16:57:00

I have only one idea, and it's a loong shot: Check the messages pane when you run this. Any messages there? Once I saw a situation like this in which there was some data corruption, and instead of returning an error it just sent a message back ("by the way, your data is crap!") I worked on it for hours without ever flipping over to the messages pane and was entirely stymied -- I think my head hitting against the keyboard finally flipped it over and showed me the answer ;)

Other than that, I'm clueless. Anyone else reading who wants to give this a shot?

---
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

ingo
Starting Member

4 Posts

Posted - 2005-03-31 : 18:32:42

Hmm, its worth a try ... but there is nothing interesting at the messages pane, just the regular "x row(s) affected" ...

but thanks anyway

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources