• Home
  • MAD
    • IOS Series
    • Android Series
    • Flutter Series
    • Xamarin Series
  • Concept Series
    • Software Design
    • Software Arch
    • GIT & Github
    • System Design
    • Cloud
    • Database Integration
    • Push Notification
    • API Integration
    • Cocoa PODS
  • DSA
  • Interview
  • Tips&Tricks
  • YT
  • Home
  • MAD
    • IOS Series
    • Android Series
    • Flutter Series
    • Xamarin Series
  • Concept Series
    • Software Design
    • Software Arch
    • GIT & Github
    • System Design
    • Cloud
    • Database Integration
    • Push Notification
    • API Integration
    • Cocoa PODS
  • DSA
  • Interview
  • Tips&Tricks
  • YT
  • #News
  • #APPS
  • #Events
    • #WWDC
    • #I/O
    • #Ignite
  • #Let’s Talk

MyCodeTips mycodetips-newlogocopy1

  • Home
  • MAD
    • IOS Series
    • Android Series
    • Flutter Series
    • Xamarin Series
  • Concept Series
    • Software Design
    • Software Arch
    • GIT & Github
    • System Design
    • Cloud
    • Database Integration
    • Push Notification
    • API Integration
    • Cocoa PODS
  • DSA
  • Interview
  • Tips&Tricks
  • YT
Database

OFFSET and FETCH NEXT ROWS in SQL Server 2012?

OFFSET and FETCH NEXT ROWS in SQL Server 2012?

OFFSET provides a starting row from which SQL Server needs to start fetching rows and FETCH provides the number of rows we want to fetch from the result set (or in a query). OFFSET and FETCH can be used only with an order by clause. Offset row count: It is the number of rows to skip.

Using this feature of SQL Server 2012 one can easily implement SQL Server Paging while displaying results to the client. We will take a look at a simple example and then also how you could construct a stored procedure to implement SQL paging.

OFFSET FETCH feature

OFFSET FETCH is a feature added to the ORDER BY clause beginning with the SQL Server 2012 edition. It can be used to extract a specific number of rows starting from a specific index.

Let’s go through a simple example that demonstrates how to use these features of SQL Server 2012. You can see below that the TSQL looks the same as what you write today except after the ORDER BY clause we have the OFFSET and FETCH commands. One thing to note is that you have to use an ORDER BY to use this feature.

SQL Server 2012

WITH CTE_SoldProduct
AS
(
SELECT SUM(Quantity) QNT,ProductID FROM dbo.TBL_Sales
GROUP BY ProductID
)

SELECT
P.NAME
,P.Price
,SP.QNT
FROM CTE_SoldProduct SP
INNER JOIN dbo.TBL_Product p ON SP.ProductID = p.ID
ORDER BY SP.QNT DESC
offset 1 rows FETCH NEXT 2 ROWS ONLY;

SQL Server 2008

WITH CTE_SoldProduct
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY SUM(Quantity) DESC) AS num, SUM(Quantity) QNT,ProductID FROM dbo.TBL_Sales
GROUP BY ProductID
)

SELECT
P.NAME
,P.Price
,SP.QNT
FROM CTE_SoldProduct SP
INNER JOIN dbo.TBL_Product p ON SP.ProductID = p.ID
WHERE num >=2

One of the main purposes of using the OFFSET FETCH feature is to load data in chunks. Let’s imagine we have an application that executes a SQL query and needs to show the results on several pages where each page contains only 10 results (similar to the Google search engine).

One of the easiest solutions is to use the OFFSET FETCH feature to load data in chunks to prevent memory outage errors. In this section, we provide a step-by-step guide on implementing this logic within an SSIS package.

First, we must create a new Integration Services package, then declare four variables as follows:

  • RowCount (Int32): Stores the total number of rows in the source table
  • IncrementValue (Int32): Stores the number of rows we need to specify in the OFFSET clause (similar to @PageSize * @PageNumber in the example above)
  • RowsInChunk (Int32): Specifies the number of rows in each chunk of data (Similar to @PageSize in the example above)
  • SourceQuery (String): Stores the source SQL command used to fetch data

After declaring the variables, we assign a default value for the RowsInChunk variable;

Happy Coding 🙂

Liked it? Take a second to support Ranjan on Patreon!
become a patron button
  • Click to share on Reddit (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • More
  • Click to share on Pocket (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
Written by Ranjan - 1537 Views
Tags | SQL
AUTHOR
Ranjan

Namaste, My name is Ranjan, I am a graduate from NIT Rourkela. This website is basically about of what i learnt from my years of experience as a software engineer on software development specifically on mobile application development, design patterns/architectures, its changing scenarios, security, troubleshooting, tools, tips&tricks and many more.

You Might Also Like

mycodetips-newlogo2

Interesting Observation of CONCAT_NULL_YIELDS_NULL and CONCAT in SQL Server 2012 onwards

May 25, 2013
mycodetips-newlogo2

How to update specific column in entire database using SQL

June 11, 2013
1386376017 database

Tips for SQL Query Optimization

December 5, 2013
Next Post
Previous Post

Support us

mycodetips
mycodetips

Follow us @ LinkedIn 2850+

Subscribe for updates

Join 8,213 other subscribers

Latest Posts

  • YT-Featured-solidprinciples
    SOLID Principles of Software Design
  • IOS 16 Features
    Latest features in IOS 16
  • r-language
    How can R language be used for data analysis?
  • wordpress-coding-blog
    Guide To WordPress Coding Standards
  • YT-Featured-Algorithm
    What is Algorithm?
  • Frameworks of IOS
    Frameworks of IOS – Part ( I )
  • NSFileManager or NSPathUtilities
    NSFileManager or NSPathUtilities in Objective-C
  • Passing data between view controllers in Objective-C
    Passing data between view controllers in Objective-C
  • structures-classes-enum
    Structures and Classes in swift !
  • control-system-swift
    Control Flow in Swift
whiteboard

Whiteboard(PRO)

whiteboard

Whiteboard(lite)

alphabets

Kids Alphabet

techlynk

Techlynk

techbyte

Do2Day

techbyte

Techbyte

  • #about
  • #myapps
  • #contact
  • #privacy
  • #Advertise
  • #myQuestions

Android Android Studio API APP Programming Apps blogging CSS DATABASE dsa Features HTML HTML5 installation Interview Questions IOS iPhone javascript Mac objective-c OS Programming quicktips SDK SEO SQL swift Tips & Tricks Tools UI Web Wordpress Xcode

  • SOLID Principles of Software Design
  • Latest features in IOS 16
  • How can R language be used for data analysis?
  • Guide To WordPress Coding Standards
  • What is Algorithm?

©mycodetips.com