THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Thanksgiving SQL Teaser COUNT

Here is a small simple Thanksgiving teaser. What do you think will the result be of the select count query?

USE tempdb

GO

CREATE TABLE Customer (CustomerID INT PRIMARY KEY)

INSERT Customer VALUES (1)

INSERT Customer VALUES (2)

INSERT Customer VALUES (3)

INSERT Customer VALUES (4)

 

SELECT COUNT(*) Customer

Published Wednesday, November 21, 2007 10:58 AM by Denis Gobo

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Dan K said:

Hrm...take a close look at what is actually being counted :-)

November 21, 2007 10:14 AM
 

Stephen Mandeville said:

IS there a missing a FROM Clause?

November 21, 2007 10:50 AM
 

Hugo Kornelis said:

>>IS there a missing a FROM Clause?<<

No, there is a missing AS keyword. But since that keyword is (unfortunately, I might add) optional, the query will run without errors.

November 21, 2007 10:57 AM
 

Jamie Thomson said:

cheeky :)

November 21, 2007 11:24 AM
 

Linchi Shea said:

COUNT(*) without a group of values (e.g. a table or a subquery resultset) doesn't seem to be defined. Isn't it just an implementation accident whatever value a particular version of SQL Server or Oracle or DB2 may return if it happens to return anything at all?

November 21, 2007 12:37 PM
 

Adam Machanic said:

I just tried it on three SQL Server instances: 2000, 2005, and 2008, and returned the same answer on all three.  None, I have to admit, were the answer I originally expected when I read the question.  Really strange that SQL Server does that...

I expected this:

SELECT COUNT(*)

to give the same answer as:

SELECT COUNT(*)

WHERE 1=0

November 21, 2007 2:12 PM
 

Hugo Kornelis said:

Hi Linchi,

It is defined, at least in T-SQL, because T-SQL defines a SELECT without a FROM clause to be equivalent to Oracle's FROM DUAL - it returns a single row. That is why SELECT CURRENT_TIMESTAMP works, and why you can use SELECT to assign values to variables.

Since a SELECT without FROM yields a single row, a SELECT COUNT(*) without from has to return the value 1.

Best, Hugo

November 21, 2007 2:13 PM
 

Hugo Kornelis said:

Hey Adam,

Do you expect

SELECT CURRENT_TIMESTAMP

WHERE 1 = 0

to return the same as

SELECT CURRENT_TIMESTAMP

If not, then why would you expect anything different if you replace CURRENT_TIMESTAMP with an aggregate function?

Best, Hugo

November 21, 2007 2:16 PM
 

Linchi Shea said:

Hugo;

Where is it in BOL that defines a SELECT without a FROM to be equivalent to returning a single row? I'm having trouble finding it.

November 21, 2007 2:41 PM
 

Hugo Kornelis said:

Hey Linchi,

Good question. It seems that, even though we all know exactly what a statement such as

SELECT 1, 2, (3 + 4), getdate(), 'This is neat';

will return - but it appears to be almost completely undocumented.

The only thing I found was the last paragraph at this page: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8c1de218-7f2c-424a-a24c-c831737ee59c.htm

This documents that you can leave out the FROM clause if the SELECT list only selects data from variables and functions, but it fails to specify that you will get one row of data returned.

Hmmmm. A very strange omission in Books Online!

Best, Hugo

November 21, 2007 4:26 PM
 

Madhivanan said:

November 22, 2007 1:57 AM
 

Vadivel said:

Good one :)

November 23, 2007 1:33 PM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement