Sql Offset

SELECT *
  FROM [fortune].[dbo].[TblStates] order by StateID desc Offset
  2 Rows fetch Next 2 Rows only;

This is important when the data is large and we need few record on click .

Note : offset work only in sql server 2012 and above version on change of offset number the result will change .

WITH ShowMessage(STATEMENT, LENGTH)AS
(
SELECT STATEMENT = CAST('I Am ' AS VARCHAR(300)), LEN('I Am') UNION ALL SELECT
CAST(STATEMENT + 'Sandeep! ' AS VARCHAR(300)) , LEN(STATEMENT)+ 9 FROM ShowMessage WHERE LENGTH < 300
)
SELECT STATEMENT, LENGTH FROM ShowMessage

Above we are keeping the record in tempoarary table that is showmessage  and in the last query we are fetching record from temp table.