Re: IsNull bad?
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 02/03/05
- Next message: Paul: "RE: Import in table identity column"
- Previous message: mitra: "adding UNIQUE Constraint to existing column"
- In reply to: kurt sune: "Re: IsNull bad?"
- Next in thread: Jacco Schalkwijk: "Re: IsNull bad?"
- Reply: Jacco Schalkwijk: "Re: IsNull bad?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 3 Feb 2005 21:04:29 +0100
Below are quotes from BOL for ISNULL:
ISNULL ( check_expression , replacement_value )
Return Types
Returns the same type as check_expression.
Above clearly explains why ISNULL returns the datatype as the first parameter.
However, I was wrong about COALESCE. It seems that datatype precedence is indeed in play here:
SELECT COALESCE(NULL, 1, '1x3') --Returns 1
SELECT COALESCE(NULL, 'sfg', 1) --Datatype conversion error
So it seems like SQL Server walks the arguments from left to right and if implicit datatype
conversion is OK when the expression which isn't NULL is reached, SQL Server stop the processing and
we get no error. If implicit conversion isn't OK when a value is reached, an error is returned.
Perhaps it would be better if SQL Server would evaluate all expressions to determine resultant
datatype, but that would come with a cost...
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ http://www.sqlug.se/ "kurt sune" <apa@apa.com> wrote in message news:%23cp6nheCFHA.2384@TK2MSFTNGP14.phx.gbl... > According to BOL is this a datatype: > Fixed-length (char) or variable-length (varchar) character data types. > > The length is not part of the "datatype". > > So: > I expected it to return the same datatype, that is varchar. > I expected it to return the value, not a truncated value. > > > /k > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in > message news:OtGZvOeCFHA.444@TK2MSFTNGP09.phx.gbl... >> What exactly do you find surprising? The datatype returned? According to > BOL, the datatype returned >> is the one of the first parameter, in the case of the script, varchar(20). > Coalesce is different as >> it returns the first expression that is not NULL, and the datatype is the > same as that expression. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> http://www.sqlug.se/ >> >> >> "kurt sune" <apa@apa.com> wrote in message > news:%23Vl4gBeCFHA.1936@TK2MSFTNGP14.phx.gbl... >> > Test this script. Can anybody explain why Microsoft designed it lika > that? >> > >> > drop table dbo.TestTable >> > go >> > create table dbo.TestTable ( >> > id integer not null >> > ,c1 varchar(40) null >> > ) >> > go >> > insert into testtable (id, c1 ) values(1, null) >> > insert into testtable (id, c1 ) values(2, replicate('a' , 39) + 'b') >> > go >> > >> > select * from testtable >> > go >> > >> > declare @password varchar(20) >> > set @password = null >> > >> > select >> > id >> > , isnull(@password, c1) as col1IsNull >> > , coalesce(@password, c1) as col1Coalesce >> > from testtable >> > >> > /k >> > >> > >> >> > >
- Next message: Paul: "RE: Import in table identity column"
- Previous message: mitra: "adding UNIQUE Constraint to existing column"
- In reply to: kurt sune: "Re: IsNull bad?"
- Next in thread: Jacco Schalkwijk: "Re: IsNull bad?"
- Reply: Jacco Schalkwijk: "Re: IsNull bad?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
