Disclaimer The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.
I really need to read up on new features when a major release comes out. Just a few weeks ago I learned of a great "new" SQL 2005 function... ROW_NUMBER(). Just in time since SQL 2008 is already out.
For me, this function means a lot less temp tables. I would typically create a temp table with an ID INT IDENTITY(1,1) column to create an DisplayOrder, BatchID, etc. used to group or join on later. Books Online describes the function as "Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition." The syntax is simple, and looks like:
ROW_NUMBER() OVER (ORDER BY ID DESC)
For this example, the data I want to bring back with a DisplayOrder column looks like:
Without ROW_NUMBER(), using a table variable with an identity column:
DECLARE @Subs TABLE (DisplayOrder INT IDENTITY(1,1), [Address] VARCHAR(100), Operation VARCHAR(50), [Contract] VARCHAR(50)) INSERT INTO @Subs ([Address], Operation, [Contract])SELECT [Address] , Operation , [Contract]FROM PersistentSubscribersWHERE Operation = 'OnEvent2'ORDER BY ID DESC SELECT * FROM @Subs
DECLARE @Subs TABLE (DisplayOrder INT IDENTITY(1,1), [Address] VARCHAR(100), Operation VARCHAR(50), [Contract] VARCHAR(50))
INSERT INTO @Subs ([Address], Operation, [Contract])SELECT [Address] , Operation , [Contract]FROM PersistentSubscribersWHERE Operation = 'OnEvent2'ORDER BY ID DESC
SELECT * FROM @Subs
With ROW_NUMBER(), look how beautiful:
SELECT DisplayOrder = ROW_NUMBER() OVER (ORDER BY ID DESC) , [Address] , Operation , [Contract]FROM PersistentSubscribersWHERE Operation = 'OnEvent2'
The results from both methods looks like:
Remember Me
a@href@title, strike