Integer column with SQL index, even if null

Question

I have this SQL:

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

The view column is NULL. Thus, this only works if the column has a value that is not null.

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

Thanks.

Answer 1

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

Answer 2

You can use Isnull also instead of Coalesce , since Isnull compares faster than Coalesce

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

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

Answer 3

I prefer the other two answers, but it may be useful to you in other situations.

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