Comparision of string values - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Comparision of string values

Author

Topic

learner_shikha
Starting Member

4 Posts

Posted - 2010-08-22 : 15:56:44

Hi,

I want to understand how the comparision of 2 empty strings would work eg:

set @productID = null

select * from dbo.keyword
where ISNULL(productID,'') = ISNULL(@ProductID,'')

so if the productid is also null then what result would come for a comparison of '' = ''

Awaiting response at the earliest.

Thanks,
Shikha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-22 : 16:16:45

Yes it will, but it will also make any index over ProductID useless.
From SQL Server 2008 and after, this kind of queries have been optimized be the storage engine

SELECT * FROM dbo.Keyword
WHERE (ProductID = @ProductID OR @ProductID IS NULL)

N 56°04'39.26"
E 12°55'05.63"

learner_shikha
Starting Member

4 Posts

Posted - 2010-08-22 : 16:26:14

quote:
Originally posted by Peso

Yes it will, but it will also make any index over ProductID useless.
From SQL Server 2008 and after, this kind of queries have been optimized be the storage engine

SELECT * FROM dbo.Keyword
WHERE (ProductID = @ProductID OR @ProductID IS NULL)

N 56°04'39.26"
E 12°55'05.63"

I am working on sql 2005

Still not clear what would be the result if we compare two empty strings. Supposed we have just one record int he keyword table with productid 5, and we are passing @productid = null then will the comparision be considered as select * from dbo.keyword where 5= 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-22 : 16:32:31

ISNULL(productID, @productid) = productID

What is your goal? What do you want to return when using null for @productid?

N 56°04'39.26"
E 12°55'05.63"

learner_shikha
Starting Member

4 Posts

Posted - 2010-08-22 : 16:41:08

I am trying to understand how this query works. I have to make changes to an existing piece of code which has the following logic:

SELECT distinct KeywordName
FROM dbo.Keyword
WHERE isnull(Keyword.CategoryID,'') = isnull(@CategoryID, '')
AND isnull(Keyword.ProductID,'') = isnull(@ProductID, '')
AND isnull(Keyword.LiteratureID,'') = isnull(@LiteratureID, '')
AND Keyword.LanguageCode = isnull(@LanguageCode,LanguageCode)
AND Keyword.CountryCode = isnull(@CountryCode, CountryCode)

So i am trying to understand how the comparision will work if
1) Either CategoryID or ProductID are null.
2) Both CategoryID and @CategoryID are null

Please let me know if i am clear enough in explaining my question

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-08-22 : 21:21:13

SELECT distinct KeywordName
FROM dbo.Keyword
WHERE isnull(Keyword.CategoryID,'') = isnull(@CategoryID, '')
AND isnull(Keyword.ProductID,'') = isnull(@ProductID, '')
AND isnull(Keyword.LiteratureID,'') = isnull(@LiteratureID, '')
AND Keyword.LanguageCode = isnull(@LanguageCode,LanguageCode)
AND Keyword.CountryCode = isnull(@CountryCode, CountryCode)

So i am trying to understand how the comparision will work if
1) Either CategoryID or ProductID are null.
2) Both CategoryID and @CategoryID are null

With isnull function, you convert all null values to empty string values, so (1,2) comparison works as regular values.

However, the below part returns result that may be different from your expectation.

AND Keyword.LanguageCode = isnull(@LanguageCode,LanguageCode)
AND Keyword.CountryCode = isnull(@CountryCode, CountryCode)

In the first part, if both CategoryID and @CategoryID are null, the condition is true, so the row will return. But if Keyword.LanguageCode and @LanguageCode are null, the row will not return, null is not equal null. More confusing, null is not unequal null either. So the word "AND" before that condition means something you do not expect.

The same thing for Keyword.CountryCode.

Also remember, using function in column name may make the index created on that column useless as Peso mentioned. But look like it has no index, anyway.

SELECT * FROM dbo.Keyword
WHERE (ProductID = @ProductID OR @ProductID IS NULL)

I think the red part makes the index on ProductID useless, doesn't matter what/how many columns on the select phase and what value of @ProductID is.

Correct me if I am wrong.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources