SQL SERVER – Puzzle – SELECT * vs SELECT COUNT(*)

Posted by pinaldave on SQL Authority See other posts from SQL Authority or by pinaldave
Published on Mon, 30 May 2011 01:30:27 +0000 Indexed on 2011/06/20 16:29 UTC
Read the original article Hit count: 358

Filed under:

PostADay

|

Readers Question

|

sql

|

SQL Authority

|

SQL Puzzle

|

SQL Query

|

SQL Server

|

SQL Tips and Tricks

|

T SQL

|

Technology

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 question 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?

Select * - resulting Error

Select * - resulting Error

Select count * - NOT resulting Error

Select count * - NOT resulting Error

Please leave your answer as 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.

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

Filed under: PostADay, Readers Question, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

© SQL Authority or respective owner

Related posts about PostADay

SQL SERVER – How to See Active SQL Server Connections For Database

as seen on SQL Authority - Search for 'SQL Authority'
Another question received via email - “How do I I know which user is connected to my database with how many connection?” Here is the script which will give us answer to the question. SELECT DB_NAME(dbid) AS DBName, COUNT(dbid) AS NumberOfConnections, loginame FROM sys.sysprocesses GROUP… >>> More
SQL SERVER – Wait Stats – Wait Types – Wait Queues – Day 0 of 28

as seen on SQL Authority - Search for 'SQL Authority'
This blog post will have running account of the all the blog post I will be doing in this month related to SQL Server Wait Types and Wait Queues. SQL SERVER – Introduction to Wait Stats and Wait Types – Wait Type – Day 1 of 28 SQL SERVER – Single Wait Time Introduction with Simple Example – Wait Type… >>> More
SQL SERVER – Quick Look at SQL Server Configuration for Performance Indications

as seen on SQL Authority - Search for 'SQL Authority'
Earlier I wrote SQL SERVER – Beginning SQL Server: One Step at a Time – SQL Server Magazine. That was the first article on the series of my real world experience of Performance Tuning experience. I have written second part the same series over here. Read second part over here: Quick Look at SQL Server… >>> More
SQLAuthority News – Best Practices for Data Warehousing with SQL Server 2008 R2

as seen on SQL Authority - Search for 'SQL Authority'
An integral part of any BI system is the data warehouse—a central repository of data that is regularly refreshed from the source systems. The new data is transferred at regular intervals by extract, transform, and load (ETL) processes. This whitepaper talks about what are best practices for Data… >>> More
SQL SERVER – A Quick Note on DB_ID() and DB_NAME() – Get Current Database ID – Get Current Database Name

as seen on SQL Authority - Search for 'SQL Authority'
Quite often a simple things makes experienced DBA to look for simple thing. Here are few things which I used to get confused couple of years ago. Now I know it well and have no issue but recently I see one of the DBA getting confused when looking at the DBID from one of the DMV and not able to related… >>> More

Related posts about Readers Question

Ask the Readers: Share Your Tips for Defeating Viruses and Malware

as seen on How to geek - Search for 'How to geek'
We’ve shared some of our best tips for dealing with malware over the years, and now it’s your turn! Share your favorite tips for protecting against, or getting rid of viruses and other types of malicious software. Unfortunately, if you’re a PC user it’s a given that you have to play defense against… >>> More
test questiontest question test question test question? [closed]

as seen on Stack Overflow - Search for 'Stack Overflow'
TestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestio… >>> More
PHP Browser Game Question - Pretty General Language Suitability and Approach Question

as seen on Game Development - Search for 'Game Development'
I'm developing a browser game, using PHP, but I'm unsure if the way I'm going about doing it is to be encouraged anymore. It's basically one of those MMOs where you level up various buildings and what have you, but, you then commit some abstract fighting entity that the game gives you, to an automated… >>> More
Testing on Device Other Than the Known Brand Question (Local and Imported Phone Question)

as seen on Game Development - Search for 'Game Development'
I have a question. When testing a device by using Eclipse, it's easy to install and add device software with these specific brands commonly used in game testing like Samsung, Google, T-Mobile, and HTC; according to the Android Developers website. What if I'm using other brands that runs on Android… >>> More
question about prime question

as seen on Stack Overflow - Search for 'Stack Overflow'
i know that there are many binary operations to show that something is true for example we can show if number is power of two or something else is there some theory or special binary method to show if number is prime? thanks >>> More