How to use Paging in SQL Queries?

Paging is one of the most important tasks when developers developed applications. 
how to use paging in SQL queries or How to select 5-10 records?.
 In SQL queries we can select first 1-10  records, 10-20 records, 20-30 records etc.
  • SQL ROW_NUMBER() Function
  • Paging using Sub Queries
  • Paging using Common Table Expression (CTE)
SQL ROW_NUMBER() Function
SQL ROW_NUMBER() function is a built-in function of SQL Server. 
For SQL paging we need to use this.
We can implement paging in SQL in two ways
Paging using Sub Queries
Here is an example of SQL Sub Queries for paging.
SELECT * 
FROM (
       SELECT *,ROW_NUMBER() OVER(ORDER BY + @sortExpression ) as RowNum  
       FROM [Inquiry] where USERID=CAST(@USERID AS NVARCHAR(10)) 
 
) T
WHERE RowID between 1 and 10
Paging using Common Table Expression (CTE)
Here is an example of SQL Common Table Expression (CTE) for paging.
;WITH CTE as
(
      SELECT *,ROW_NUMBER() OVER(ORDER BY + @sortExpression ) as RowNum  
    FROM [Inquiry] where USERID=CAST(@USERID AS NVARCHAR(10)) 

)
SELECT * from CTE
WHERE RowID between 1 and 10

Comments

Post a Comment

Popular posts from this blog

Stack vs Heap So far we have seen how to declare basic type variables such as int , double , etc, and complex types such as arrays and structs. The way we have been declaring them so far, with a syntax that is like other languages such as MATLAB, Python, etc, puts these variables on the stack in C. The Stack What is the stack? It's a special region of your computer's memory that stores temporary variables created by each function (including the main() function). The stack is a "FILO" (first in, last out) data structure, that is managed and optimized by the CPU quite closely. Every time a function declares a new variable, it is "pushed" onto the stack. Then every time a function exits, all of the variables pushed onto the stack by that function, are freed (that is to say, they are deleted). Once a stack variable is freed, that region of memory becomes available for other stack variables. The advantage of using the stack to store variable...
Read more

Validate Mobile Number with 10 Digits in ASP.Net

Here is the code for validating textbox for 10 digit mobile number. .ASPX File: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br\>     <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server"        ControlToValidate="TextBox1" ErrorMessage="RegularExpressionValidator"      ValidationExpression="[0-9]{10}"></asp:RegularExpressionValidator>
Read more
What is  Abstraction? Abstraction is another good feature of OOPS. Abstraction means to show only the necessary details to the client of the object . Do you know the inner details of the Monitor of your PC? What happen when you switch ON Monitor? Does this matter to you what is happening inside the Monitor? No Right, Important thing for you is weather Monitor is ON or NOT. When you change the gear of your vehicle are you really concern about the inner details of your vehicle engine? No but what matter to you is that Gear must get changed that’s it!! This is abstraction; show only the details which matter to the user.  Let’s say you have a method "CalculateSalary" in your Employee class, which takes EmployeeId as parameter and returns the salary of the employee for the current month as an integer value. Now if someone wants to use that method. He does not need to care about how Employee object calculates the salary? An only thing he needs to be concern is name...
Read more