SQL increment integer column even if null

Question

I have this SQL:

update entity_table set views = views + 1 where id = {id of entity}

the views column is nullable. So this only works if the column has a value which is not null.

How can I make this statement set the value to 1 if it is null and increment otherwise?

Thanks.

No sorry the rest of the statement includes where id = {id of entity} — Richard, Aug 07 '13 at 07:21

score 22 · Accepted Answer · answered Aug 06 '13 at 16:29

22

UPDATE entity_table
SET    views = Coalesce(views, 0) + 1

answered Aug 06 '13 at 16:29

gvee

16,732
35
50

Why do people insist on using the proprietary `IsNull()` over the standard, more flexible and powerful `Coalesce()`? – gvee Aug 06 '13 at 16:33
:- Isnull is comparatively faster than Coalesce – Rahul Tripathi Aug 06 '13 at 16:35
@RahulTripathi can you provide a link to some evidence of this please? **EDIT:** found this by Adam Machanic... http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx – gvee Aug 06 '13 at 16:36
Yes sure Sir:- http://weblogs.sqlteam.com/mladenp/articles/2937.aspx and http://stackoverflow.com/questions/2287642/which-is-quicker-coalesce-or-isnull – Rahul Tripathi Aug 06 '13 at 16:37
1

Final line from Adam Machanics article " Hardly worth the functionality and standards compliance sacrifice, at least in the scenarios I use these functions for" which gets the massive +1 from me. – gvee Aug 06 '13 at 16:38
2

OMG, do you people really think the proprietary ISNULL is really going to outperform COALESCE in this case? The links provided show mixed results, and I don't think there's any way to make a blanket statement that one is faster than the other in all scenarios. [Performance should not be your primary reason to decide which one to use anyway](http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/), unless you have a very specific case where you've found that your choice isn't as fast as the other... – Aaron Bertrand Aug 06 '13 at 17:01
Isnull is conceptually easier to understand when you are trying to fix nulls. Coalesce seems like an unrelated function. (Just answering the "why do people insist question") – Jeff Davis Apr 10 '17 at 21:51

Rahul Tripathi · Answer 2 · 2013-08-06T17:26:03.280

5

You can use Isnull also in place of Coalesce as Isnull is comparatively faster than Coalesce

UPDATE entity_table
SET    views = isnull(views, 0) +1

Check out this link for understanding the performance difference between the two:- http://weblogs.sqlteam.com/mladenp/articles/2937.aspx

edited Aug 06 '13 at 17:26

answered Aug 06 '13 at 16:31

Rahul Tripathi

168,305
31
280
331

Sir actually I had written my findings in the other answers Comments. If you ask I will add them in my answer. – Rahul Tripathi Aug 06 '13 at 17:23
Please be careful about blanket statements - ISNULL isn't always faster than COALESCE, and the link you pointed out even demonstrated that in that specific case, ISNULL was ***slower*** than COALESCE. – Aaron Bertrand Aug 06 '13 at 17:32
Ooh yes...I fully agree..It said that there is a 10 to 12% difference. Although I got your point. Thanks a lot!! – Rahul Tripathi Aug 06 '13 at 17:33
1

...the SQLTeam link showed ISNULL slower, Adam's test (which didn't access any data) was the other way. So 10 to 12% difference in *either* direction, depending on the test, means that "ISNULL is comparatively faster than COALESCE"? That's what I mean about blanket statement - generalizing something to make it sound like it's always true when it isn't. – Aaron Bertrand Aug 06 '13 at 17:37

score 2 · Answer 3 · answered Aug 06 '13 at 17:39

2

I prefer the other two answers, but this may be of some use to you as well in other situations.

update entity_table
set views = CASE
                WHEN views IS NULL THEN 1
                ELSE views + 1
            END

answered Aug 06 '13 at 17:39

UnhandledExcepSean

12,504
2
35
51

I don't understand, this answer executes much faster than accepted one but is not accepted as the best answer... – 71GA Jul 29 '17 at 22:27

SQL increment integer column even if null

3 Answers3