Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How many rows?


How many rows?

Author
Message
prashant.bhatt
prashant.bhatt
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 177
Comments posted to this topic are about the item How many rows?

Prashant Bhatt
Sr Engineer - Application Programming
dogramone
dogramone
SSChasing Mays
SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)

Group: General Forum Members
Points: 630 Visits: 3610
This really highlights why I don't like SQL Server's implicit data type conversion.



UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 Visits: 2204
Nice question, thanks!
malleswarareddy_m
malleswarareddy_m
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2009 Visits: 1189
but the same will not return any rows when we use

select * from #temp where code ='0'

this will return nothing.When we use 0 in quotes it will not return any no of rows.Implicits conversion happens from varchar to int.but it will not happen from int to varchar.We want to convert explicitly.

select * from #temp where code =convert(int,'0')

this will return three rows even we put zero in sigle quotes.

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Nakul Vachhrajani
Nakul Vachhrajani
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1823 Visits: 2126
The essence of this question is in the following:


SELECT CONVERT(NVARCHAR(2),0) -- returns 0
SELECT CONVERT(INT,N'') -- returns 0



Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
ziangij
ziangij
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2905 Visits: 374
good one... thanks :-)
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16380 Visits: 13199
Good question. It points out that you have to be very careful with implicit conversions.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Nakul Vachhrajani
Nakul Vachhrajani
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1823 Visits: 2126
I am trying to find out "why" this behaviour is seen and believe that there is definitely something influencing this (I am just not able to find out what). It's not Data Type Precedence; but can be COLLATION Precedence.

Based on what I found in BOL, I developed the following small test:


SELECT CONVERT(NVARCHAR(2),0) -- returns 0

-- returns 0, i.e. is NOT a Numeric value
SELECT ISNUMERIC('')

-- returns 0
SELECT CONVERT(INT,N'')
SELECT CONVERT(INT,N' ')
SELECT CAST(N' ' AS INT)
/*
Data type conversion (Database Engine):
"Character expressions that are being converted to an exact numeric data type must consist of digits, a decimal point, and an optional plus (+) or minus (-). Leading blanks are ignored. Comma separators, such as the thousands separator in 123,456.00, are not allowed in the string."
*/

-- returns Error
SELECT CONVERT(NUMERIC,N'')
/*
CAST & CONVERT :
"SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal."
*/



If conversion to NUMERIC returns NULL, why does the conversion to INT succeed, and why does a blank string convert to 0?

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Hardy21
Hardy21
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 1399
ISNUMERIC is returing 0 so implicit conversation is not converting BLANK to ZERO. Therefore the reason behind this is something else.

Thanks
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8317 Visits: 11543
dogramone (8/26/2010)
This really highlights why I don't like SQL Server's implicit data type conversion.

Good point. Always ensure your types match, or use explicit conversion when they don't.
(Though the empty string also converts to 0 in explicit conversion)

malleswarareddy_m (8/26/2010)
but the same will not return any rows when we use

select * from #temp where code ='0'

this will return nothing.When we use 0 in quotes it will not return any no of rows.Implicits conversion happens from varchar to int.but it will not happen from int to varchar.We want to convert explicitly.

select * from #temp where code =convert(int,'0')

this will return three rows even we put zero in sigle quotes.

You are confusing some things. The first query doesn't do any conversion - both the column (code) and the constant ('0') are already of the character data type, so no conversion at all is required. And because the UPDATE has changed all the '0' strings to '' (empty) strings, no rows match.
In the second query, you are making it even more complicated. With the quotes, the constant is character; without them, it is integer. The explicit convert will convert it to integer (a no-op if you leave out the quotes). Then SQL Server will convert the contents of the code column to int for the comparison, again converting the empty string to the value 0. So the end effect will not be any different from
select * from #temp where code = 0;

You are right, though, that implicit conversion will not happen from integer to character. But for a different reason. Implicit conversions always follow the rules of data type precedence, and integer has a higher precedence. The only way to force conversion from integer to character is by using explicit conversion:
select * from #temp where code = convert(nvarchar(2), 0);
This will convert the integer value 0 to a character data type ('0'). The code column is also character, so no further conversion is required and the comparison is done. This is equivalent to using
select * from #temp where code = N''0';
No rows will be returned.

Nakul Vachhrajani (8/27/2010)
If conversion to NUMERIC returns NULL, why does the conversion to INT succeed, and why does a blank string convert to 0?

Answer to the first question: Because the rules for conversion to numeric are not the same as the rules for conversion to integer. And for conversion to float or money, yet other rules are used.
Speculative answer to the second question: The conversion algorithm must have some logic to deal with leading zeroes, as I expect both '00003' and '3' to convert to the same integer value. This same logic should also be used to consider '0000' and '0' as equal. It could be that the algorithm is too agressive, in allowing the last zero to be left out as well?
Or it could be by design. In my eyes, there are only two logical outcomes when converting an empty string to a numericall value: error or 0.

hardik.doshi (8/27/2010)
ISNUMERIC is returing 0 so implicit conversation is not converting BLANK to ZERO. Therefore the reason behind this is something else.

I consider this a bug in ISNUMERIC. According to Books Online, ISNUMERIC returns 1 if the "expression can be converted to at least one of the numeric types". Since the empty string can be converted to not one but several numeric types (int, money, and real), the return vallue should have been 1.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search