Earlier this weekend I have presented at Bangalore User Group on the subject of SQL Server Tips and Tricks. During the presentation I have asked a question to attendees. It was very interesting to see that I have received various different answer to my question. Here is the same puzzle for you and I would like to see what your answer to this question.
Question: SELECT * gives error when executed alone, but SELECT COUNT (*) does not. Why?
Please leave your answer as a comment over here. If you prefer, you can blog post about this on your blog and put a link here. I will publish valid answer with due credit in future blog posts. Remember one should not use SELECT * in the production environment as there are many different disadvantages of the SELECT *. The primary disadvantage is that it retrieves the data most of the time which users are not using normally in the production display. For example, not all the time we need to retrieve last updated time or even GUID if they exists on the table. The purpose of this blog post was for demonstration purpose and just for fun.
Reference: Pinal Dave (https://blog.sqlauthority.com)
76 Comments. Leave new
count(*) is about counting rows, not a particular column. It doesn’t even look to see what columns are available, it’ll just count the rows, which in the case of a missing FROM clause, is 1.
“select *” is designed to return columns, and therefore barfs if there are none available.
Even more odd is this one:
select ‘blah’ where exists (select *)
You might be surprised at the results…
I think that SQL Server consider the COUNT(*) because, we can do “SELECT 1”. But, when we do “SELECT *” the engine try replace * by column names of a table … but what the table ? My opinion is this.
[]’s
Both SELECT * and SELECT COUNT(*) requires table name to be read.
SELECT * gives list of records with all columns from the table.
SELECT COUNT(*) gives the count of resulting rows. Here, SELECT COUNT(*) is also giving an error for table name but returns a single row contains error message. Hence, SELECT COUNT(*) gives 1 as an output.
COUNT() is a scalar syetem defined function as we know any scalar function can be called using SELECT as like SELECT COUNT(*), SELECT GETDATE() etc.
But, SELECT * expects a table or table type userdefined function as we intend to fetch columns from table / table type userdefined function (* here refers to all columns )
(ajay)
COUNT(*) is not a scalar expression! is an aggregate function.
(Rob Farley)
select ‘blah’ where exists (select *)
I surely didn’t expect that result!!! can you explaint it?
It’s because EXISTS (SELECT *…) is a recognised pattern which is also only interested in rows, not values. In fact, you can put just about whatever you want in there (so long as it’s recognisably valid).
In all fairness, it does return a scalar value.
The engine performs a “Constant scan” for Count(*) where as in the case of “SELECT *” the engine is trying to perform either Index/Cluster/Table scans.
COUNT statement just evaluates the number of rows returned by select and it counts the rows with null or error values…I guess that is the case.
The Difference between Select * and Select Count(*) is that Select * always followed by a object and it returns the records from that object where as Select Count(*) is used to do some kind of calculation in tha table/object. As Select Count(*) takes null values in considerartion so it returns 1
When you query ‘select * from sometable’, SQL replaces * with the current schema of that table. With out a source for the schema, SQL throws an error.
so when you query ‘select count(*)’, you are counting the one row. * is just a constant to SQL here. Check out the execution plan. Like the description states – ‘Scan an internal table of constants.’
You could do ‘select COUNT(‘my name is adam and this is my answer’)’ and get the same answer.
“Select *” will return all columns from a table (or a result set), if no table specified, error happens.
“Select count(*)” will return the number of a table (or result set), if no table specified, it will scan an internal table of constants which has 1 row ( I got this from Actual Execution Plan). No sure why about this?
“Select *” looks for columns but no from clause mentioned it gives error
whereas “Select count(*)” just counts the rows including NULL row, it does not look for From clause.
Select * requires column names to be specified for a table….no table name means no column names ..so it will give error…
Select count(*) does nothing but returns the count of rows from temporary resultset which has single cell (like DUAL in oracle) …means only 1 row and thus returns 1
Shekhar check this,
SELECT COUNT(*)
if @@Error0
print ‘Error’
else
print ‘No Error’
SELECT COUNT(*) –it simply return one @@rowcount
Declare @t table (id int)
Select count(cn) from
(Select count(*) cn from @t)t4
Yea I know Kuma. SELECT COUNT(*) always returns 1 as it is counting number of records returned. Pelase read my comments carefully.
https://docs.microsoft.com/en-us/collaborate/connect-redirect
:)
(Koushik)
The Constant Scan may be the answer. As i read, when you have a Constant Scan:
“You’ll see situations where the query has to create a row to hold it’s data before it can access data from tables”
if this is the case it would explain the result ‘1’ to the query Select count(*)
How we call Scalar function
Select [dbo].[fnName]
Tabular function
Select * from [dbo].[fnName]
I think same reason.
https://docs.microsoft.com/en-us/collaborate/connect-redirect
Hello,
The statement is a syntax error, because it can’t be parse by the compiler because the statement “*” is recognize only follow by a table name(or in the case you want to select from a table in multijoin select statement). Instead “COUNT(*)” can be parse because COUNT can recognize * like parameter and when the compiler try to parse the instruction select count(*) everything is ok.
An intersting observation is that you can create a function call schemaName.* but you never can call it, because the compiler don’t find any table called “Schemaname” in your Select statement. (In case that you call your table SchemaName or have an alias will select the rows from the table:
select dbo.*
from dbo.TableName as dbo
When we execute
Select *
SQL CLR engine try to find FROM and table name to select records but not get so return Error.
When we execute
Select Count(*) it return 1
Here Count is an agreegate function
* is a character which user select all column
if CLR engine dont get any column then it count as simple character and return 1 based on agrregate maths criteria.
If we execute
SELECT COUNT(100) or any character like SELECT COUNT(‘100’) it return 1 based on agrregate calculation.
When you say “SELECT *” you are writing a partial command: “SELECT these fields” without saving where they should come from. “*” is a wildcard which expands to “all fields” so you need to specify the table to read from:
Collapse
“SELECT * FROM myTable”
When you say “SELECT COUNT(*)” you are a different command: a SELECT that returns a single item, the COUNT, of the number of items. Because there is no list, the result comes out at 1 -> the count itself.
SELECT COUNT (*)
Here * Specifies that all rows should be counted to return the total number of rows in a table. COUNT(*) takes no parameters and cannot be used with DISTINCT. COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table without getting rid of duplicates. It counts each row separately. This includes rows that contain null values.
Also, COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.
COUNT(ALL {expression}) evaluates expression for each row in a group and returns the number of nonnull values.
COUNT(DISTINCT {expression}) evaluates expression for each row in a group and returns the number of unique, nonnull values.
For return values greater than 2^31-1, COUNT produces an error.
{expression} is an expression of any type except text, image, or ntext. Aggregate functions and subqueries are not permitted.
*Referenced MSDN.*
Regards,
Harsh Baid
.NET Developer
Hidden Brains Infotech Pvt Ltd.
For me its simple Count is a method and it takes an argument, so select count(*) has given output. Select * is throwing sytaxtical error as it need a tablename and the from keyword to compelte the select query.