January 16, 2009 at 11:41 am
Hello ALL,
If in my database SQL 2005 as value NULL where is an empty cell, and I wish to display it as the figure "0". Because I wish to display the data NULL as zero on my front end (ASP.NET 2.0). I'm new to this SQL, so would you please tell me how? or when I display on the front end, it will automatically display zero instead of NULL value.
Thank You Very Much
J
January 16, 2009 at 11:44 am
Hi,
You can use this kind of query:
Select ISNULL(ColumnNameWithNullValues,0), SomeOtherColumn from SomeTable
Hope it helps,
Cheers,
J-F
Cheers,
J-F
January 16, 2009 at 11:54 am
January 16, 2009 at 12:54 pm
When you're not using it in a WHERE clause or a JOIN, COALESCE
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 16, 2009 at 1:00 pm
Why? Is it quicker to do a coalesce over a IsNull?
And why not in a where clause?
Cheers,
J-F
Cheers,
J-F
January 16, 2009 at 1:08 pm
Jean-François Bergeron (1/16/2009)
Why? Is it quicker to do a coalesce over a IsNull?And why not in a where clause?
Cheers,
J-F
It's just because I find COALESCE easier to read. I don't think you'd see any kind of performance enhancement. You would however, see a performance degredation if you used COALESCE in the WHERE clause.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 16, 2009 at 1:14 pm
Here, done some quick testing:
both take around 15secs to run:
/* Test the difference between isNull and Coalesce on a 1 000 000 records table.*/
IF EXISTS (SELECT 1
FROM sys.tables
WHERE name = 'TestData')
DROP TABLE testData
CREATE TABLE testData (
id INT IDENTITY ( 1 , 1 ),
NullValue INT)
DECLARE @cpt INT
SET @cpt = 0
WHILE @cpt < 1000
BEGIN
INSERT INTO testdata
SELECT TOP 1000 NULL
FROM sys.objects a,
sys.objects b
SET @cpt = @cpt
+ 1
END
GO
DECLARE @timer DATETIME
SELECT @timer = (GetDate())
SELECT isNull(NullValue,0)
FROM testData
PRINT 'IsNull Took '
+ CONVERT(NVARCHAR(10),datediff(ms,@Timer,getdate()))
+ ' ms'
SELECT @timer = (getdate())
SELECT Coalesce(NullValue,0)
FROM testData
PRINT 'Coalesce Took '
+ CONVERT(NVARCHAR(10),datediff(ms,@Timer,getdate()))
+ ' ms'
Cheers,
J-F
Cheers,
J-F
January 16, 2009 at 3:24 pm
IsNull Took 13813 ms
Coalesce Took 14233 ms
on my laptop..
January 17, 2009 at 9:46 am
There's a mini debate of COALESCE vs. ISNULL going on in the concatenating NULL values thread. I believe the consensus is that ISNULL is in fact faster, but COALESCE is more versatile and "ANSI compliant". I like them both, I use them both. The differences is speed will pile up depending on how many rows you're using them against though.
January 19, 2009 at 8:48 am
Another difference between ISNULL and COALESCE is IS NULL converts the data type where as COALESCE doesnt.
Example
DECLARE@X VARCHAR(10),
@Y VARCHAR(5)
SET @X = '123456789'
SELECT ISNULL(@Y,@X) will result 12345
SELECT COALESCE(@Y,@X) will result 123456789
Reddy P
January 19, 2009 at 8:58 am
Depending on what the application requirements, if you have no use for knowing that a given column wasn't even by the user - it might be a whole lot easier to simply force a default value into the column instead of fixing it after the fact....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply