Performance - 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

SQL Server Development (2000)

Performance

Author

Topic

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-16 : 11:57:32

Consider

select sum(case when StatusId = 9 then 1 else 0 end
) as NumberAccepted from something

Versus

select count(case when StatusId = 9 then 1 else null end
) as NumberAccepted from something

Which method is more efficient and why(and also, how can you determine this)?

StatusID is an int.

An infinite universe is the ultimate cartesian product.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-16 : 12:57:01

Have you compared the run times and execution plans?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-16 : 15:57:18

Yes Tara, it appears to be identical but then again I'm hitting some fairly small tables. I guess I should go find some bigger tables to play with.

An infinite universe is the ultimate cartesian product.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-16 : 16:01:50

My guess is that they'll be identical even on larger tables. There isn't a performance benefit that I know of to running one or the other.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources