SQL SERVER – Solution – Generating Zero Without using Any Numbers in T-SQL

SQL Server MVP and my friend Madhivanan has asked very interesting question on his blog regarding How to Generate Zero without using Any Numbers in T-SQL. He has demonstrated various methods how one can generate Zero. When I posted note regarding how one he has generated Zero without using number in my blog post for Free Online Training, blog readers have come up with few very interesting answers. I really found them very interesting and here I am listing them with due credit.

Special mention to Andery.ca as the answer Andery provided is the one, I myself come up with after very first look and that is why I had left the same as hint in the original article.


try this
select count(cast(null as int))
any false condition
select count(*) where ‘a’=’b’

Varinder Sandhu

It seems every currency symbol that SQL Server supports. Return the same value as zero

i tried some as

select €
 select ¥
 select £


select count(*)-count(*)

Vinay Kumar

Another way for generate zero.

select Ascii(‘Y’)-Ascii(‘Y’)
 select LEN(”)

I like Madhivanan’s answer. and it was awesome.

Reference:Pinal Dave (http://blog.SQLAuthority.com)

17 thoughts on “SQL SERVER – Solution – Generating Zero Without using Any Numbers in T-SQL

  1. Though there are many ways to generate 0, I really like madhivanan’s approach as it tells us that, SQL Server will identify datatype as money, even if it has only currency symbol without any digits


  2. need clarification if it’s possible….

    Can we get the output other than 1 for the below query without using any table….?

    select count(”) / select count(*)


  3. Pingback: SQLAuthority News – Monthly list of Puzzles and Solutions on SQLAuthority.com Journey to SQLAuthority

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #034 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s