XML Feeds
Take a look at this code which I found a while back in a stored proc
tsql
- 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.
tsql
- 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!
tsql
- 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
tsql
- 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
tsql
- 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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.