This SQL Server interview question can be asked in a twisted format also “What is the use of “OFFSET” and “FETCH” commands?”.
These are new commands in SQL Server 2012. They help to do pagination. See the next question which answers the same in more detail.
There are instances when you want to display large result sets to the end user. The best way to display large result set is to split them i.e. Apply pagination. So developers had their own hacky ways of achieving pagination using “top”, “row_number” command etc. But from SQL Server 2012 onwards we can do pagination by using “OFFSET” and “FETCH’ commands.
For instance let’s says we have the following customer table which has 12 records. We would like to split the records in to 6 and 6.
So doing pagination is a two-step process:-
First mark the start of the row by using “OFFSET” command.
Second specify how many rows you want to fetch by using “FETCH” command.
You can see in the below code snippet we have used “OFFSET” to mark the start of row from “0”position. A very important note order by clause is compulsory for “OFFSET” command.
In the below code snippet we have specified we want to fetch “6” rows.
Now if you run the above SQL you should see 6 rows.
To fetch the next 6 rows just change your “OFFSET” position. You can see in the below code snippet I have modified the offset to 6. That means the row start position will from “6”.
The above code snippet displays the next “6” records , below is how the output looks.
SQL Server interview question References for further reading:-
Do not miss this awesome video on SQL Server interview question :- Find second highest.
This is one more SQL Server interview question asked around referential integrity.
http://dotnetinterviewquestion.wordpress.com/2011/11/07/sql-server-interview-questions-explian-referential-integrity-in-sql-server/
This book is awesome to refere for SQL Server interview :- SQL Server interview question which is published by BPB publication.
