Take a look at this code which I found a while back in a stored proc

declare @id int,@xtype char(1),@uid int,@info int,@status int

set  @id =(select id from sysobjects where name = 'sysobjects')
set @xtype  =(select xtype from sysobjects where name = 'sysobjects')
set @uid   =(select uid from sysobjects where name = 'sysobjects')
set @info  =(select info from sysobjects where name = 'sysobjects')
set @status =(select status from sysobjects where name = 'sysobjects')

select @id ,@xtype ,@uid ,@info ,@status 
go

Do you see what is wrong? It uses five select statements to accomplish something which can be done in one. I would do something like this instead.

declare @id int,@xtype char(1),@uid int,@info int,@status int

select @id =id
,@xtype =xtype
,@uid =uid
,@info =info
,@status =status 
from sysobjects where name = 'sysobjects'

select @id ,@xtype ,@uid ,@info ,@status

Let’s take a look at another example.

What we want to do is display a row of counts for 4 xtypes from the sysobjects table, here is an example

s	u	p	c
19	143	74	6

Have you ever seen code like this that does that? I have!

select count(*) as [s],
(select count(*) from  sysobjects where xtype = 'u') as [u],
(select count(*) from  sysobjects where xtype = 'p') as [p],
(select count(*) from  sysobjects where xtype = 'c') as [c] 
from  sysobjects 
where xtype = 's'

That code will do a select 4 times against the table

A better way would be to do this

select  sum(case xtype when 's' then 1 else 0 end) as [s],
sum(case xtype when 'u' then 1 else 0 end) as [u],
sum(case xtype when 'p' then 1 else 0 end) as [p],
sum(case xtype when 'c' then 1 else 0 end) as [c] 
from sysobjects 
where xtype in('s','u','p','c')

In SQL server 2005/2008 you can use the PIVOT operator, here is what the query would look like

SELECT s, u, p, c
FROM
(SELECT xtype
FROM sysobjects
WHERE xtype IN('s','u','p','c')) AS pivTemp
PIVOT
(   count(xtype) 
    FOR xtype IN(s, u, p, c)
) AS pivTable

If you can think of any other examples feel free to leave a comment