Alternate rows ...

Sometime back in a user group a guy enquired "how to fetch alternate rows from a SQL Server table"?

As all of us know there isn't any direct method of doing it in SQL Server. So let me explain couple of work arounds for this.

Using Table Variables

Declare @tmpTable table
(
 [RowNum] int identity,
 [au_id] varchar(50) NOT NULL ,
 [au_lname] [varchar] (40),
 [au_fname] [varchar] (20),
 [phone] [char] (12)
)

-- Filling the row number column of the table variable
Insert into @tmpTable select au_id,au_lname, au_fname, phone from authors

-- Fetching the alternate records from the table.
Select * from @tmpTable where RowNum % 2 <> 0

Using Temp Tables

-- Filling the row number column of the temp table
Select IDENTITY(int, 1,1) RowNum, au_id,au_lname, au_fname, phone INTO #tmpTable from authors

-- Fetching the alternate records from the table.
Select * from #tmpTable where RowNum % 2 <> 0

Obviously we could solve this using many other methods like using cursors, using user defined functions etc., Just think about performance hit before choosing a method.