XML Feeds
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
