Write query with which you can achieve paging from SQL side.

 Posted by Bhakti on 12/3/2009 | Category: Sql Server Interview questions | Views: 2188

With below provided query you can get desired result. You need to pass parameter of startrowindex and maximum rows. Here in below example they are set to 0 and 200 respectively.

declare @StartRowIndex as int
set @StartRowIndex = 0
declare @MaximumRows as int
set @MaximumRows = 200
SELECT *, ROW_NUMBER() OVER (Order By Sort_Column) AS RowRank -- Add your desired column name which you want ordered here in place of "Sort_Column"
SELECT distinct Sort_Column , column1 , column2 -- Desired list of the column names you want to retrieve
FROM [dbo].[table_name] WITH(NOLOCK)
--* Optional section if you have some joins then place them here *--
--inner join vTanks on vTanks.ClientId = Facilities.ClientId
--and vTanks.FacilityId = Facilities.Facilityid
) primarySelect
WHERE Id = 294 AND Archive <> 1 -- specify your conditions here
) As joinTable
WHERE (RowRank > @StartRowIndex AND RowRank <= (@StartRowIndex + @MaximumRows))
ORDER BY table_name

Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response