March 16, 2012 at 12:52 pm
Sometimes the parser can go weird.
Is there any way to make the snipet below work?
create table test4(
a int null
,b int not null
,c as isnull(a,b*b) primary key
)
GO
Updt: Never mind, I just missed the persisted keyword.
😛
March 16, 2012 at 1:05 pm
jcb (3/16/2012)
Sometimes the parser can go weird.Is there any way to make the snipet below work?
create table test4(
a int null
,b int not null
,c as isnull(a,b*b) primary key
)
GO
Updt: Never mind, I just missed the persisted keyword.
😛
Forget the persisted keyword, you don't need it. The error message saying you do is just nonsense (a bug in the compiler).
You can write
create table test4(
a int null
,b int not null
,c as isnull(isnull(a,b*b),0) primary key
)
GO
The outermost isnull simply has the effect of convincing the parser that the expression given for column c is not nullable - it always delivers b*b because that can't be null (because b is not nullable) even though the compiler thinks it is nullable.
Tom
March 16, 2012 at 1:33 pm
L' Eomot Inversé (3/16/2012)
Forget the persisted keyword, you don't need it. The error message saying you do is just nonsense (a bug in the compiler). The outermost isnull simply has the effect of convincing the parser that the expression given for column c is not nullable - it always delivers b*b because that can't be null (because b is not nullable) even though the compiler thinks it is nullable.
b * b may return NULL if ARITHABORT and ANSI_WARNINGS are OFF for the connection performing the insert/update/merge.
http://msdn.microsoft.com/en-us/library/ms189118.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 16, 2012 at 1:59 pm
SQL Kiwi (2/7/2010)
Jeff Moden (2/6/2010)
For those that believe in the myth of portability, see if you can write a portable trigger. 😉:laugh: Perfect :laugh:
My own view on the whole ISNULL versus COALESCE debate: it is important to know why both exist, what their various nuances are, and to use both appropriately. Neither is perfect, and I use both regularly depending on the requirement.
I could argue that NULL is in fact evil, but I suspect that might start a whole new debate 😀
Paul
(currently in Napier)
+1
I will use each when appropriate.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 16, 2012 at 2:03 pm
Thanks Paul!
Your insight cleared a lot about this behaviour.
But instead of a error the parser can verifify the DB default values and issue a firendly warning message like.
"Ok, now if you set off ARITHABORT and ANSI_WARNINGS it ill rain cr@p!"
March 16, 2012 at 2:08 pm
>>which one better?
Given comparable performance and equivalent results I prefer COALESCE.
I doubt there will ever be a poem called ISNULL.
March 16, 2012 at 6:39 pm
SQL Kiwi (3/16/2012)
L' Eomot Inversé (3/16/2012)
Forget the persisted keyword, you don't need it. The error message saying you do is just nonsense (a bug in the compiler). The outermost isnull simply has the effect of convincing the parser that the expression given for column c is not nullable - it always delivers b*b because that can't be null (because b is not nullable) even though the compiler thinks it is nullable.b * b may return NULL if ARITHABORT and ANSI_WARNINGS are OFF for the connection performing the insert/update/merge.
But in this case I know that ANSI_WARNINGS has to be on any time an insert or update is made to this table, because
BoLSET ANSI_WARNINGS must be ON when you are creating or manipulating indexes on computed columns or indexed views
and any update or or insert will have to act on the clustered index associated with the primary key on the computed column whose defining expression depends on every non-computed column in the table. So B*B can't return null, anything that might make it do that is guaranteed to abort the statement.
Even without this fairly bizarre table definition, note also
SET ANSI_WARNINGS (Transact-SQL)When SET ANSI_DEFAULTS is ON, SET ANSI_WARNINGS is enabled
which means that using T-SQL in anything near ISO-compatible mode ensures that ANSI_WARNINGS is on.
Besides, I regard running with SET ANSI_WARNINGS off as a tongue-lashing offence anyway :-D. But I suppose there are some people who do it :angry:.
Tom
March 17, 2012 at 12:44 am
L' Eomot Inversé (3/16/2012)
But in this case I know that ANSI_WARNINGS has to be on any time an insert or update is made to this table, becauseBoLSET ANSI_WARNINGS must be ON when you are creating or manipulating indexes on computed columns or indexed views
and any update or or insert will have to act on the clustered index associated with the primary key on the computed column whose defining expression depends on every non-computed column in the table. So B*B can't return null, anything that might make it do that is guaranteed to abort the statement.
This is true (at least as long as indexes are the physical mechanism used to enforce a PRIMARY KEY constraint...) 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 19, 2012 at 5:49 am
Something changed in my mind concerned about the parser/compiler.
I runned some tests like below:
use tempdb
GO
SET ARITHABORT Off
SET ANSI_WARNINGS Off
GO
declare @b-2 int
set @b-2 = 2147483647
select @b-2*@b
GO
/* setting off ansi warnings ill cause any atempt to create a persisted computable column to fail */
create table test4(
a int null
,b int not null
,c as b*b --isnull(a,b*b) persisted primary key -- or simply -- isnull(0,b*b) persisted
)
GO
insert into test4 values (null, 2147483647)
GO
/* Ill return null for the computed column */
select * from test4
GO
drop table test4
GO
SET ARITHABORT On
SET ANSI_WARNINGS On
GO
Conclusion, the parser is fearing anyone can set off ansi and arithabort so it's just enforcing things to not let a breach like inserting a null.
However "isnull(0,b*b) persisted" can be considered safe. My guess is the parser is unable to verify when the function ill work properly maybe because a exception can be throw inside it.
August 27, 2012 at 5:46 pm
Another difference is when you use "SELECT ... INTO" statement. A field that is calculated using ISNULL will have a not nullable attribute if the second argument is not nullable. Whereas COALESCE will always have a nullable attribute.
August 28, 2012 at 3:45 pm
GSquared (12/14/2009)
BaldingLoopMan (12/14/2009)
To each their own.Even in cases where the data type precedence won't cause a problem, I find the Coalesce version below much easier to read, understand, and maintain, than the second:
if object_id(N'tempdb..#People') is not nulldrop table #People;
create table #People (
ID int identity primary key,
NamePrefix varchar(100),
NameFirst varchar(100),
NameMiddle varchar(100),
NameLast varchar(100),
NameSuffix varchar(100));
insert into #People (NamePrefix, NameFirst, NameMiddle, NameLast, NameSuffix)
select null, 'Adam', null, 'Abrahms', null union all
select '', 'Bob', null, 'Birch', null union all
select 'Dr', 'Carl', 'C', 'Carlson', 'PhD' union all
select 'Rev', null, null, 'Dodgy', 'Jr';
;with Cleanup (Prefix, First, Middle, Last, Suffix) as
(select IsNull(NullIf(NamePrefix, ''), 'Mr/Ms'), NullIf(NameFirst, ''), NullIf(NameMiddle, ''),
NullIf(NameLast, ''), NullIf(NameSuffix, '')
from #People)
select
coalesce(
Prefix + ' ' + First + ' ' + Middle + ' ' + Last + ', ' + Suffix,
Prefix + ' ' + First + ' ' + Middle + ' ' + Last,
Prefix + ' ' + First + ' ' + Last + ', ' + Suffix,
Prefix + ' ' + First + ' ' + Last,
Prefix + ' ' + Last + ', ' + Suffix,
Prefix + ' ' + Last)
from Cleanup;
I think there's an easier way to do that expression.
You can use the expression below directly in a SELECT, but to keep the definition consistent for all queries, I would add a computed column to the base table:
ALTER TABLE dbo.tablename
ADD full_name AS
ISNULL(Prefix, '') + ISNULL(' ' + First, '') + ISNULL(' ' + Middle) +
ISNULL(' ' + Last, '') + ISNULL(', ' + Suffix, '')
--if you need the NULLIFs, naturally add those in
SELECT
fullname,...
FROM dbo.tablename
--GROUP BY
--fullname --for example
--ORDER BY
--fullname --for example
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 30, 2012 at 11:36 am
bambang (12/11/2009)
Just wondering,I like to use ISNULL(column_name, 0) while my friend like to use COALESCE(column_name, 0).
We both try using ISNULL and COALESCE in our query and there is no different result.
In Books online, it's said that both behave differently. There's also the explanation, but I just don't
understand it completely :p
Is there any article regarding this topic?
And which one you usually use (ISNULL or COALESCE) and why you use it?
For now, I'll keep my habit using ISNULL, so will my friend keep using COALESCE 😀
Thanx for any explanation, link, or you opinion 🙂
1. ISNULL is actually a tiny bit faster than COALESCE but it takes millions of rows to notice.
2. ISNULL is shorter to type than COALESCE.
3. ISNULL converts the second operand to the same datatype as the first. COALESCE selects the datatype of the operand that has the highest data precidence. THAT can make for some real performance problems if you're not careful.
4. If you believe in the myth of truly portable code (and I don't except maybe for basic "CRUD"), COALESCE is ANSI where ISNULL is not.
I agree that it's nearly a personal choice and my personal choice is to use ISNULL where ever I can.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply