Writing good queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Writing good queries

Author

Topic

mohithmv
Starting Member

8 Posts

Posted - 2005-06-17 : 04:52:54

Hi All,

Which is the best among the two queries given below? and reasons for the same.

select count(*) from emp

OR

select count(empid) from emp

Is there any "Best practices while writing sql queries" online?
because i searched the web but i couldn't narrow down properly.

Regards,
Mohith

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-17 : 05:31:26

Both are same

quote:
Is there any "Best practices while writing sql queries" online?

Look for index, joins in BOL
Search in http://msdn.microsoft.com/ for more info

Madhivanan

Failing to plan is Planning to fail

amitja
Starting Member

7 Posts

Posted - 2005-06-17 : 05:38:48

Surely second query is preferred. I am not very sure of the reason but my best guess would be the
type of lock first query will put on the table. This could be important for a table with millions of rows and lot of activity.

Using second query has a limitation. rather there is a catch. If there are Nulls in the field being used to count, then those rows will be omitted and the count may not be the same as the one with count(*).

Cheers,
Amit

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-06-17 : 05:40:34

I understood that select count(1) from emp was more efficient.

steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-17 : 06:46:50

select count(*) from emp -- as i understand this is preffered because sql server
-- optimizer uses the best index for this and is faster

select count(empid) from emp -- counts only non null values of empid

Go with the flow & have fun! Else fight the flow

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-06-17 : 08:57:39

There has been some earlier discussion of this including [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46002[/url] and some others that I can't find at the moment

steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources