Which is better? (SQL Server Best Practices)

Question

Post reply

Which is better? (SQL Server Best Practices)

jj011185

Old Hand

Points: 324
More actions
June 30, 2008 at 8:17 am

#187654

Hi all,
I'm new here, but not new to SQL Server, I just like to ask everyone's opinion with negation, which is better and why?
having...
Where LEN(Message) > 0
OR
Where Message IS NOT NULL
IMHO, I prefer option 1.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

This is not a great question.

The two options may not give you the same result.

It is typically better to not use a function within a join or where clause in a query because they have to be evaluated before doing the comparison - making indexes irrelevant.

kevin.kunz SSChasing Mays Points: 635 More actions · Answer 2

kevin.kunz

SSChasing Mays

Points: 635

July 7, 2008 at 12:56 pm

#839115

This is similar to the SARG discussion

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

They do not produce the same results; see below:

create table #t ( Message varchar(20), seq int not null identity(1,1))

insert #t

select null union all

select '' union all

select 'xxx'

select * from #t where len(Message) > 0

select * from #t where Message is not null

Results:

Message seq

-------------------- -----------

xxx 3

(1 row(s) affected)

Message seq

-------------------- -----------

2

xxx 3

(2 row(s) affected)

einman33 SSCrazy Points: 2813 More actions · Answer 4

Better suggestion, make the app do length check and take the appopriate action.

Jose-cin SSC-Addicted Points: 487 More actions · Answer 5

Ahi va:

Where LEN(isnull(Message, '')) > 0

Saludos,

José Miguel Gutiérrez Hilaccama
Database Developer - MCTS SQL Server 2005
No way Jose? ... bah

rameshr-455703 SSC Rookie Points: 32 More actions · Answer 6

WHERE (MESSAGE IS NOT NULL) AND (MESSAGE<>'')

This is better compare to Len function.

* For More info read Index Topic.

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 7

Depending on what you are trying to achieve you can also use LIKE '_%'.

This will exclude NULLs an empty strings '' (but not the single space strings ' ').

Best Regards,

Chris Büttner

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

Christian Buettner (7/16/2008)
Depending on what you are trying to achieve you can also use LIKE '_%'.
This will exclude NULLs an empty strings '' (but not the single space strings ' ').

I like this answer the best, although as he says, "Depending", but in general using a function on a column in a where clause or join is a bad idea.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 9

I prefer the (Where LEN(isnull(Message, '')) > 0) method.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Mike Mullen Hall of Fame Points: 3254 More actions · Answer 10

Here is another option that could be used:

where isnull(Message,'') > ''

Toby White SSCertifiable Points: 6187 More actions · Answer 11

Another option could be an implicit varbinary conversion such as

where message > 0x