table hosed: clause

Showing posts with label clause. Show all posts

Friday, February 24, 2012

is null in where clause does not work properly

We have a very simple select statement:
select E.COURSE_YEAR, E.COURSE_SESSION, E.COURSE_CODE, E.FLAG
FROM enrollment e
where
e.course_year=2005
and e.course_session=2
AND E.FLAG IS NULL
There were 16743 records returned but more than half of them contain a value
in the FLAG field. What can be happening here? What can I check?
Funny enough (not really), when I take out the "AND E.FLAG IS NULL" clause,
there were 16778 records returned, but when I do "AND E.FLAG IS NOT NULL", I
got 7514 records, which is correct. I'm losing my mind over this.
We are running SQL Server 2000 w/SP3 on W2k3 standard edition.
We ran into a similar problem with a slightly more complex sql statement.
The strange thing is that if we ran the query 20 times we would probably
have the correct data returned to us somewhere between 2 and 4 times.
The .818 patch fixed the issue for us. As always, test the patch on a test
system first.
http://support.microsoft.com/kb/821277
Keith
"Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
news:E5958FF9-7B60-44D9-BCC0-E5FDF562AE8A@.microsoft.com...
> We have a very simple select statement:
> select E.COURSE_YEAR, E.COURSE_SESSION, E.COURSE_CODE, E.FLAG
> FROM enrollment e
> where
> e.course_year=2005
> and e.course_session=2
> AND E.FLAG IS NULL
> There were 16743 records returned but more than half of them contain a
> value
> in the FLAG field. What can be happening here? What can I check?
> Funny enough (not really), when I take out the "AND E.FLAG IS NULL"
> clause,
> there were 16778 records returned, but when I do "AND E.FLAG IS NOT NULL",
> I
> got 7514 records, which is correct. I'm losing my mind over this.
> We are running SQL Server 2000 w/SP3 on W2k3 standard edition.
|||Thanks for the reply. We haven't tried the patch yet. Luckily the system is
not in production yet and we still have time to play around. We found out
that it is a multi-processor issue - similar to the one documented for SQL
Server 7.0. When we assign SQL Server to use one processor only, the problem
goes away.
"Keith Kratochvil" wrote:

> We ran into a similar problem with a slightly more complex sql statement.
> The strange thing is that if we ran the query 20 times we would probably
> have the correct data returned to us somewhere between 2 and 4 times.
> The .818 patch fixed the issue for us. As always, test the patch on a test
> system first.
> http://support.microsoft.com/kb/821277
> --
> Keith
>
> "Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
> news:E5958FF9-7B60-44D9-BCC0-E5FDF562AE8A@.microsoft.com...
>
>
|||Just looked around and yes, the security fix you mentioned has that fix. I'll
give it a try next week.
Thanks.
"Keith Kratochvil" wrote:

> We ran into a similar problem with a slightly more complex sql statement.
> The strange thing is that if we ran the query 20 times we would probably
> have the correct data returned to us somewhere between 2 and 4 times.
> The .818 patch fixed the issue for us. As always, test the patch on a test
> system first.
> http://support.microsoft.com/kb/821277
> --
> Keith
>
> "Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
> news:E5958FF9-7B60-44D9-BCC0-E5FDF562AE8A@.microsoft.com...
>
>

IS NULL bug(?)

Hello,
My problem is; weird results with a IS NULL clause in a query.
I have a sql2000 dbase, table, 2 columns and the following query:
dt_column = type: datetime
computed: no
length:8
nullable: no
TrimTrailingBlanks:n/a
FixedLenNullnSource:n/a
Collation: null
vc_column = type:varchar
computed: no
length:15
nullable: yes
TrimTrailingBlanks:no
FixedLenNullnSource: no
Collation: SQL_Latin1_General_CP1_CI_AS
q1: select dt_column,vc_colomn from mytable where vc_column is null
->result; >20.000 records where vc_column indeed is null
but, if i expand the query with a date clause like so:
q2: select dt_column,vc_column from mytable where vc_column is null and
year(dt_column) = '2006'
->result; <20.000 records where SOME of the results DO have a value for
the vc_column!
This is strange isnt it ?
I already have a solution to overcome this; but I do not see why i have
to do this:
When I cast the vc_column to a varchar like so:
q3: select dt_column,vc_column from mytable where cast(vc_column as
varchar) and year(dt_column) ='2006'
->the result is good! only null values for the vc_column !
but why do i have to cast a varchar column to a varchar before the is
null will work '?
Thanks very much,
JeroenThis sounds a bit familiar. I ran into a similar problem where unexpected
results were returned from a query (that was a bit more complex than the one
you posted).
What version of SQL Server 2000 are you running?
execute this within Query Analyzer:
SELECT @.@.version
and compare the results to the build list here:
http://www.aspfaq.com/sql2000Builds.asp
In our case we experienced the error on SP4 (SP3 worked fine). Applying the
.2187 hotfix solved our problem. Do you have a test system that you can use
to reproduce the probem and test various patches to see if they solve your
problem?
Keith Kratochvil
"Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
news:1160054422.157730.217980@.c28g2000cwb.googlegroups.com...
> Hello,
> My problem is; weird results with a IS NULL clause in a query.
> I have a sql2000 dbase, table, 2 columns and the following query:
> dt_column => type: datetime
> computed: no
> length:8
> nullable: no
> TrimTrailingBlanks:n/a
> FixedLenNullnSource:n/a
> Collation: null
> vc_column => type:varchar
> computed: no
> length:15
> nullable: yes
> TrimTrailingBlanks:no
> FixedLenNullnSource: no
> Collation: SQL_Latin1_General_CP1_CI_AS
> q1: select dt_column,vc_colomn from mytable where vc_column is null
> ->result; >20.000 records where vc_column indeed is null
> but, if i expand the query with a date clause like so:
> q2: select dt_column,vc_column from mytable where vc_column is null and
> year(dt_column) = '2006'
> ->result; <20.000 records where SOME of the results DO have a value for
> the vc_column!
> This is strange isnt it ?
> I already have a solution to overcome this; but I do not see why i have
> to do this:
> When I cast the vc_column to a varchar like so:
> q3: select dt_column,vc_column from mytable where cast(vc_column as
> varchar) and year(dt_column) ='2006'
> ->the result is good! only null values for the vc_column !
> but why do i have to cast a varchar column to a varchar before the is
> null will work '?
> Thanks very much,
> Jeroen
>|||Hi Keith,
Thanks for the quick reply;
The version of sql server is:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
on the > http://www.aspfaq.com/sql2000Builds.asp website I can see no
hotfixes available for this release...?
(seems to stop at build 2187)...
Maybe the 2187 hotfix works here to; I'll test; thanks again.
Jeroen
Keith Kratochvil wrote:
> This sounds a bit familiar. I ran into a similar problem where unexpected
> results were returned from a query (that was a bit more complex than the one
> you posted).
> What version of SQL Server 2000 are you running?
> execute this within Query Analyzer:
> SELECT @.@.version
> and compare the results to the build list here:
> http://www.aspfaq.com/sql2000Builds.asp
> In our case we experienced the error on SP4 (SP3 worked fine). Applying the
> .2187 hotfix solved our problem. Do you have a test system that you can use
> to reproduce the probem and test various patches to see if they solve your
> problem?
>
> --
> Keith Kratochvil
>
> "Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
> news:1160054422.157730.217980@.c28g2000cwb.googlegroups.com...
> > Hello,
> >
> > My problem is; weird results with a IS NULL clause in a query.
> >
> > I have a sql2000 dbase, table, 2 columns and the following query:
> > dt_column => > type: datetime
> > computed: no
> > length:8
> > nullable: no
> > TrimTrailingBlanks:n/a
> > FixedLenNullnSource:n/a
> > Collation: null
> >
> > vc_column => > type:varchar
> > computed: no
> > length:15
> > nullable: yes
> > TrimTrailingBlanks:no
> > FixedLenNullnSource: no
> > Collation: SQL_Latin1_General_CP1_CI_AS
> >
> > q1: select dt_column,vc_colomn from mytable where vc_column is null
> > ->result; >20.000 records where vc_column indeed is null
> >
> > but, if i expand the query with a date clause like so:
> > q2: select dt_column,vc_column from mytable where vc_column is null and
> > year(dt_column) = '2006'
> > ->result; <20.000 records where SOME of the results DO have a value for
> > the vc_column!
> >
> > This is strange isnt it ?
> >
> > I already have a solution to overcome this; but I do not see why i have
> > to do this:
> > When I cast the vc_column to a varchar like so:
> > q3: select dt_column,vc_column from mytable where cast(vc_column as
> > varchar) and year(dt_column) ='2006'
> > ->the result is good! only null values for the vc_column !
> > but why do i have to cast a varchar column to a varchar before the is
> > null will work '?
> >
> > Thanks very much,
> > Jeroen
> >|||Hi
You are currently on service pack 3a, you can apply service pack 4
http://www.microsoft.com/downloads/details.aspx?familyid=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en
followed by the subsequent hot fixes. I would recommend at least going to
build 2040. This is the link for the 2187 download:
http://www.microsoft.com/downloads/details.aspx?familyid=9C9AB140-BDEE-44DF-B7A3-E6849297754A&displaylang=en
John
"Shuwi" wrote:
> Hi Keith,
> Thanks for the quick reply;
> The version of sql server is:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
> Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> on the > http://www.aspfaq.com/sql2000Builds.asp website I can see no
> hotfixes available for this release...?
> (seems to stop at build 2187)...
> Maybe the 2187 hotfix works here to; I'll test; thanks again.
> Jeroen
> Keith Kratochvil wrote:
> > This sounds a bit familiar. I ran into a similar problem where unexpected
> > results were returned from a query (that was a bit more complex than the one
> > you posted).
> >
> > What version of SQL Server 2000 are you running?
> > execute this within Query Analyzer:
> > SELECT @.@.version
> > and compare the results to the build list here:
> > http://www.aspfaq.com/sql2000Builds.asp
> >
> > In our case we experienced the error on SP4 (SP3 worked fine). Applying the
> > .2187 hotfix solved our problem. Do you have a test system that you can use
> > to reproduce the probem and test various patches to see if they solve your
> > problem?
> >
> >
> > --
> > Keith Kratochvil
> >
> >
> > "Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
> > news:1160054422.157730.217980@.c28g2000cwb.googlegroups.com...
> > > Hello,
> > >
> > > My problem is; weird results with a IS NULL clause in a query.
> > >
> > > I have a sql2000 dbase, table, 2 columns and the following query:
> > > dt_column => > > type: datetime
> > > computed: no
> > > length:8
> > > nullable: no
> > > TrimTrailingBlanks:n/a
> > > FixedLenNullnSource:n/a
> > > Collation: null
> > >
> > > vc_column => > > type:varchar
> > > computed: no
> > > length:15
> > > nullable: yes
> > > TrimTrailingBlanks:no
> > > FixedLenNullnSource: no
> > > Collation: SQL_Latin1_General_CP1_CI_AS
> > >
> > > q1: select dt_column,vc_colomn from mytable where vc_column is null
> > > ->result; >20.000 records where vc_column indeed is null
> > >
> > > but, if i expand the query with a date clause like so:
> > > q2: select dt_column,vc_column from mytable where vc_column is null and
> > > year(dt_column) = '2006'
> > > ->result; <20.000 records where SOME of the results DO have a value for
> > > the vc_column!
> > >
> > > This is strange isnt it ?
> > >
> > > I already have a solution to overcome this; but I do not see why i have
> > > to do this:
> > > When I cast the vc_column to a varchar like so:
> > > q3: select dt_column,vc_column from mytable where cast(vc_column as
> > > varchar) and year(dt_column) ='2006'
> > > ->the result is good! only null values for the vc_column !
> > > but why do i have to cast a varchar column to a varchar before the is
> > > null will work '?
> > >
> > > Thanks very much,
> > > Jeroen
> > >
>|||Don't be confused by "Service Pack 4" in the results. That is the WINDOWS
service pack, not the SQL Server Service pack.
Here is another select statement that returns database service pack level:
select serverproperty('productlevel')
By the way,
.760 is SP3a
There are a few publically available hotfixes for SP3:
.818
.878
You could upgrade to SP4 (version 2039)
There are two publically available hotfixes for SP4: 2040 and 2187
--
Keith Kratochvil
"Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
news:1160056347.136146.322120@.h48g2000cwc.googlegroups.com...
> Hi Keith,
> Thanks for the quick reply;
> The version of sql server is:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
> Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> on the > http://www.aspfaq.com/sql2000Builds.asp website I can see no
> hotfixes available for this release...?
> (seems to stop at build 2187)...
> Maybe the 2187 hotfix works here to; I'll test; thanks again.
> Jeroen
> Keith Kratochvil wrote:
>> This sounds a bit familiar. I ran into a similar problem where
>> unexpected
>> results were returned from a query (that was a bit more complex than the
>> one
>> you posted).
>> What version of SQL Server 2000 are you running?
>> execute this within Query Analyzer:
>> SELECT @.@.version
>> and compare the results to the build list here:
>> http://www.aspfaq.com/sql2000Builds.asp
>> In our case we experienced the error on SP4 (SP3 worked fine). Applying
>> the
>> .2187 hotfix solved our problem. Do you have a test system that you can
>> use
>> to reproduce the probem and test various patches to see if they solve
>> your
>> problem?
>>
>> --
>> Keith Kratochvil
>>
>> "Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
>> news:1160054422.157730.217980@.c28g2000cwb.googlegroups.com...
>> > Hello,
>> >
>> > My problem is; weird results with a IS NULL clause in a query.
>> >
>> > I have a sql2000 dbase, table, 2 columns and the following query:
>> > dt_column =>> > type: datetime
>> > computed: no
>> > length:8
>> > nullable: no
>> > TrimTrailingBlanks:n/a
>> > FixedLenNullnSource:n/a
>> > Collation: null
>> >
>> > vc_column =>> > type:varchar
>> > computed: no
>> > length:15
>> > nullable: yes
>> > TrimTrailingBlanks:no
>> > FixedLenNullnSource: no
>> > Collation: SQL_Latin1_General_CP1_CI_AS
>> >
>> > q1: select dt_column,vc_colomn from mytable where vc_column is null
>> > ->result; >20.000 records where vc_column indeed is null
>> >
>> > but, if i expand the query with a date clause like so:
>> > q2: select dt_column,vc_column from mytable where vc_column is null and
>> > year(dt_column) = '2006'
>> > ->result; <20.000 records where SOME of the results DO have a value for
>> > the vc_column!
>> >
>> > This is strange isnt it ?
>> >
>> > I already have a solution to overcome this; but I do not see why i have
>> > to do this:
>> > When I cast the vc_column to a varchar like so:
>> > q3: select dt_column,vc_column from mytable where cast(vc_column as
>> > varchar) and year(dt_column) ='2006'
>> > ->the result is good! only null values for the vc_column !
>> > but why do i have to cast a varchar column to a varchar before the is
>> > null will work '?
>> >
>> > Thanks very much,
>> > Jeroen
>> >
>|||Thanks all;
Seems I have some upgrading to do...
Kind regards,
Jeroen
Keith Kratochvil wrote:
> Don't be confused by "Service Pack 4" in the results. That is the WINDOWS
> service pack, not the SQL Server Service pack.
> Here is another select statement that returns database service pack level:
> select serverproperty('productlevel')
> By the way,
> .760 is SP3a
> There are a few publically available hotfixes for SP3:
> .818
> .878
> You could upgrade to SP4 (version 2039)
> There are two publically available hotfixes for SP4: 2040 and 2187
> --
> Keith Kratochvil
>
> "Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
> news:1160056347.136146.322120@.h48g2000cwc.googlegroups.com...
> > Hi Keith,
> >
> > Thanks for the quick reply;
> > The version of sql server is:
> >
> > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> > 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
> > Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> >
> > on the > http://www.aspfaq.com/sql2000Builds.asp website I can see no
> > hotfixes available for this release...?
> > (seems to stop at build 2187)...
> >
> > Maybe the 2187 hotfix works here to; I'll test; thanks again.
> >
> > Jeroen
> > Keith Kratochvil wrote:
> >> This sounds a bit familiar. I ran into a similar problem where
> >> unexpected
> >> results were returned from a query (that was a bit more complex than the
> >> one
> >> you posted).
> >>
> >> What version of SQL Server 2000 are you running?
> >> execute this within Query Analyzer:
> >> SELECT @.@.version
> >> and compare the results to the build list here:
> >> http://www.aspfaq.com/sql2000Builds.asp
> >>
> >> In our case we experienced the error on SP4 (SP3 worked fine). Applying
> >> the
> >> .2187 hotfix solved our problem. Do you have a test system that you can
> >> use
> >> to reproduce the probem and test various patches to see if they solve
> >> your
> >> problem?
> >>
> >>
> >> --
> >> Keith Kratochvil
> >>
> >>
> >> "Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
> >> news:1160054422.157730.217980@.c28g2000cwb.googlegroups.com...
> >> > Hello,
> >> >
> >> > My problem is; weird results with a IS NULL clause in a query.
> >> >
> >> > I have a sql2000 dbase, table, 2 columns and the following query:
> >> > dt_column => >> > type: datetime
> >> > computed: no
> >> > length:8
> >> > nullable: no
> >> > TrimTrailingBlanks:n/a
> >> > FixedLenNullnSource:n/a
> >> > Collation: null
> >> >
> >> > vc_column => >> > type:varchar
> >> > computed: no
> >> > length:15
> >> > nullable: yes
> >> > TrimTrailingBlanks:no
> >> > FixedLenNullnSource: no
> >> > Collation: SQL_Latin1_General_CP1_CI_AS
> >> >
> >> > q1: select dt_column,vc_colomn from mytable where vc_column is null
> >> > ->result; >20.000 records where vc_column indeed is null
> >> >
> >> > but, if i expand the query with a date clause like so:
> >> > q2: select dt_column,vc_column from mytable where vc_column is null and
> >> > year(dt_column) = '2006'
> >> > ->result; <20.000 records where SOME of the results DO have a value for
> >> > the vc_column!
> >> >
> >> > This is strange isnt it ?
> >> >
> >> > I already have a solution to overcome this; but I do not see why i have
> >> > to do this:
> >> > When I cast the vc_column to a varchar like so:
> >> > q3: select dt_column,vc_column from mytable where cast(vc_column as
> >> > varchar) and year(dt_column) ='2006'
> >> > ->the result is good! only null values for the vc_column !
> >> > but why do i have to cast a varchar column to a varchar before the is
> >> > null will work '?
> >> >
> >> > Thanks very much,
> >> > Jeroen
> >> >
> >

IS NULL bug(?)

Monday, February 20, 2012

Is not Null clause not working for me

I've got a strange problem with my sql not working. I'm trying to query a
table and have a where clause to return all the values in a field which are
not null(in other words left blank) The query can be seen below.
However this query is still returning all of the records in the table even
though the u_creditor_ref field has mainly null values.
My table looks like the following: -
u_surname u_firstname u_account_name u_creditor_ref
Cairns Stephen
Kelly Michael 456789
McCartney&Son
Walsh John 4565
Has anyone any idea what i'm doing wrong. Im running this query in query
analyzer
Select u_surname, u_firstname, u_account_name, u_creditor_ref
from UserObject
where u_creditor_ref is not nullStephen
I think you have an empty string in the row
Try adding WHERE col <>''
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which
are
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||Perhaps you don't have NULL for those rows. Perhaps it is a blank or similar
? Can you post a
reproduction script?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which ar
e
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||"which are
not null(in other words left blank) "
Keep in mind that "blank" LEN(Column) = 0 , doesnt mean that they are NULL
!
If there is an empty string inserted you have to check for that LEN(
u_creditor_ref) = 0, if you have addtionaly also NULL values in there you
have to add "AND u_creditor_ref IS NULL)
Check the Column for NULL Values With (Select Count(*), CASE u_creditor_ref
WHEN NULL THEN 'NULL' ELSE 'NON-NULL' END From Yourtable Group by CASE
u_creditor_ref WHEN NULL THEN 'NULL' ELSE 'NON-NULL' END)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which
> are
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||That works cheers
"Uri Dimant" wrote:

> Stephen
> I think you have an empty string in the row
> Try adding WHERE col <>''
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> are
>
>|||Yeah your right. it looked like a null but if i did either of the following
it worked
where u_creditor_ref <>''
or
where u_creditor_ref != ''
which of these is better to use or do they mean the exact same thing?
"Tibor Karaszi" wrote:

> Perhaps you don't have NULL for those rows. Perhaps it is a blank or simil
ar? Can you post a
> reproduction script?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
>
>|||Use <>. They do the same, but <> is ANSI SQL compliant, so you might just wa
nt to get into the
habit...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:90D68211-3C36-44EC-A7A3-5582947F456C@.microsoft.com...
> Yeah your right. it looked like a null but if i did either of the followin
g
> it worked
> where u_creditor_ref <>''
> or
> where u_creditor_ref != ''
> which of these is better to use or do they mean the exact same thing?
> "Tibor Karaszi" wrote:
>|||Use <> for spec purposes.
Jens Suessmeyer.
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:90D68211-3C36-44EC-A7A3-5582947F456C@.microsoft.com...
> Yeah your right. it looked like a null but if i did either of the
> following
> it worked
> where u_creditor_ref <>''
> or
> where u_creditor_ref != ''
> which of these is better to use or do they mean the exact same thing?
> "Tibor Karaszi" wrote:
>

Is Not Null and Query Optimization

How does the phrase "Is Not Null" in the where clause effect the
effectiveness of a query?

If it is a determent to the effectiveness of the query, how do you
work around it?

Thanks,On Thu, 28 Jun 2007 16:22:12 -0000, db55 <chfran@.gmail.comwrote:

Quote:

Originally Posted by

>How does the phrase "Is Not Null" in the where clause effect the
>effectiveness of a query?
>
>If it is a determent to the effectiveness of the query, how do you
>work around it?
>
>Thanks,

It really isn't an issue in general, with is the only way your
question can be answered. If you provide a specific case a more
specific response might be possible.

If the requirement is to test that something is NOT NULL then it is
intrinsic to the effectiveness of the query - the query can't be
writen without it.

As far as the efficiency of the query, the optimizer will do the best
it can to take advantage of any appropriate indexes when choosing an
execution plan.

Roy Harvey
Beacon Falls, CT|||Are you sure this is the question you want to ask?

The "Is Not Null" is treated as a requirement, and will force the
optimizer to choose a query plan that will eliminate all NULL rows.

If you want to know about the effects on efficiency, well, the use of
"Is Not Null" in and of itself does not negate the use of indexes. In
other words (and somewhat simplified): if most rows are NULL and a few
rows are NOT NULL, and you have an index on this column, then the
optimizer will most likely use the index.

HTH,
Gert-Jan

db55 wrote:

Quote:

Originally Posted by

>
How does the phrase "Is Not Null" in the where clause effect the
effectiveness of a query?
>
If it is a determent to the effectiveness of the query, how do you
work around it?
>
Thanks,

|||db55 (chfran@.gmail.com) writes:

Quote:

Originally Posted by

How does the phrase "Is Not Null" in the where clause effect the
effectiveness of a query?

About the same as "col = 3" will.

That is, if you say

SELECT * FROM tbl WHERE col IS NOT NULL

and col has a non-clustered index, SQL Server will use that index if only
a few rows has non-NULL values, but will ignore it it, if many rows have.
Simply because a scan is more efficient in this case.

--
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

Friday, February 24, 2012

is null in where clause does not work properly

IS NULL bug(?)

IS NULL bug(?)

IS NULL bug(?)

Monday, February 20, 2012

Is not Null clause not working for me

Is Not Null and Query Optimization

table hosed

Blog Archive

About Me