Difference between Select and Select count() in sql server-2005

Question

4.50/5 (2 votes)

See more:

SQL-Server-2005

Hai

I would to know the difference between select * and select count(*)
If I use select * it gives an error

 "Msg 263, Level 16, State 1, Line 1
Must specify table to select from"

but select count(*) does not give any error
Instead It gives 1 as count.

May I know the reason.

Thanks in advance.

Posted 29-May-11 23:01pm

sacraj

Updated 29-May-11 23:04pm

ZeeroC00l

v2

Add a Solution

Comments

ZeeroC00l 30-May-11 5:04am

-- added pre and code tags for readability

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kim Togo · Answer 1 · 2011-05-30T00:14:00

Solution 4

You can take a look at SQL Tutorial[^], it is a nice and simple way to learn more about SQL statements.

Posted 30-May-11 0:14am

Kim Togo

praveen1687 · Answer 2 · 2011-05-29T23:22:00

Solution 3

Count(*) counts all the rows in the result set. Select * returns all columns for the rows in the resultset.

If the count is changing it's probably there are inserts and deletes happening against the destination table.

praveen
chennai

Posted 29-May-11 23:22pm

praveen1687

sivakumar321 · Answer 3 · 2011-05-29T23:20:00

Solution 2

It mean in that table you have only one field and it has only one row.!

so, When you use 'select count(*)' it count no of rows from the table which you used recently.

for 'select * ' - you should mention the table name..!

Posted 29-May-11 23:20pm

sivakumar321

Updated 29-May-11 23:23pm

v2

Comments

sacraj 30-May-11 5:26am

I used select * from sample This table has got 5 columns
after this I executed the query "select * from count(*)" It gives an error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '*'.
What is problem in my query Please correct.

sivakumar321 30-May-11 5:27am

you can't use 'select * from count(*)' - invalid query..

OriginalGriff · Answer 4 · 2011-05-29T23:13:00

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:

"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.