MS SQL 2011 - new Offset statement
- Transfer
The new SQL Server 2011 (Denali) expands the capabilities of the Order By command with two highly anticipated additional commands:
Using this command allows you to skip the specified number of lines before displaying the query results. What is meant by this: Suppose we have 100 entries in a table and we need to skip the first 10 rows and print lines 11 through 100. Now this is easily solved by the following query:
For those comrades who practice .Net, the extension method for Skip collections , which skips the specified number of lines, should be familiar . So the Offset expression works exactly the same. After the data is ordered in some way, the Offset expression can be applied.
In all subsequent examples on Offset, it will use the data set constructed as a result of this script:
Task 1. Skip the first 10 entries and show the rest.
The script will be simple.
Or
The output will be as follows:
It does not matter which word to use after indicating the number of lines: Row or Rows - they are synonyms in this case.
Task 2. Pass the number of lines to skip as a variable
Task 3. Set the number of lines to skip as an expression
The expression select MAX (number) / 99999999 from master..spt_values will return the number 14.
Task 4. Set the number of lines to skip as a user-defined function
Code for scalar user-defined function
Task 5. Using Offset with Order by inside views, functions, subqueries, nested tables, common table expressions (Common Table Expressions - CTE).
For example, use in general terms.
The example below shows the use of Offset and Order by inside a nested table.
And another example of the work of Offset and Order with representations.
1. Since this is an “extension method”, nothing will work without an order by expression.
Report an error
Msg 102, Level 15, State 1, Line 21 Incorrect syntax near '10'.
2. You cannot set a negative value for Offset.
The SQL server engine will
produce Msg 10742, Level 15, State 1, Line 22 The offset specified in a OFFSET clause may not be negative.
3. You cannot specify values other than the integer type.
or
Will give us
Msg 10743, Level 15, State 1, Line 24 The number of rows provided for a OFFSET clause must be an integer.
4. Cannot be used inside Over () expression .
During the execution of the request, we will receive the message
Msg 102, Level 15, State 1, Line 22 Incorrect syntax near 'Offset'.
These keywords are used to specify the number of rows returned after skipping an array of strings using the Offset expression. Imagine that we have 100 lines and we need to skip the first 10 and get the next 5 lines. Those. need to get lines from 11 to 15.
Such a query will return the expected number of rows. Programmers on .Net will immediately recall the Take extension method.
Next, consider situations where you can apply these keywords.
Task 1. Skip the first 10 entries and get the next 5
The result will be like this:
Task 2. Set the number of lines to output using a variable
In general, with these keywords you can do the same thing as with Offset. Subqueries, views, functions, etc.
The restrictions on these keywords are fully consistent with the restrictions on Offset.
In previous versions of SQL Server, you could get the same functionality by using the Row_Number () ranking function. Of course, the code turned out to be not so elegant and concise, for example:
Inside the CTE, a service column is being generated that simply numbers the lines, after which the lines are filtered by this field. The method is not the fastest as you understand.
For these ancient servers there were no ranking functions, but even then the functionality discussed could be repeated. Then, temporary tables with an auto incremental field were used. Example script:
In this script, a temporary table is first created where the data from the target table is overwritten. Moreover, in the temporary table there is an auto-increment field, by which then the necessary rows are requested.
I am sure that all the previous explanation about the use and purpose of Offset and Fetch has led you to a clear understanding of why they are needed and where you can use them. Ideas were born to optimize existing code. Further we will consider an example from real practice when Offset can be useful. The results of performance measurements on different SQL servers will also be presented. Tests will be run on a sample of 1 million rows.
First, create an account table using the following script.
Page browsing is the most common function in systems for viewing records from any databases. Now it is possible to do this both on the client side and on the server side. Paging on the client side means loading the entire table, or a very large part of it into memory, in order to programmatically do pagination. On the other hand, this can be done on the server side, then the application will receive only the data that it requested to display the desired page. With this approach, the time for sending data, post-processing and storing them in memory is reduced. Those. There is a significant acceleration in application performance.
For experimental purposes, we will skip the first 20,000 entries and take the next 50,000.
I think that the previous examples and comments are enough to understand the script.
Runtime:
SQL Server Execution Times:
CPU time = 110 ms, elapsed time = 839 ms.
I / O statistics:
Scan count 1,
logical reads 8037,
physical reads 0,
read-ahead reads 0,
lob logical reads 0,
lob physical reads 0,
lob read-ahead reads 0.
Runtime:
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 631 ms.
I / O statistics:
Scan count 1,
logical reads 530,
physical reads 0,
read-ahead reads 1549,
lob logical reads 0,
lob physical reads 0,
lob read-ahead reads 0.
Time performance :
the SQL Server Execution Times:
the CPU time = 47 ms, elapsed time = 626 ms.
I / O statistics:
Scan count 1,
logical reads 530,
physical reads 0,
read-ahead reads 1549,
lob logical reads 0,
lob physical reads 0,
lob read-ahead reads 0.
The most interesting result is the use of CPU time (CPU Time ) and runtime (Elapsed Time - time required by the request to execute). Comparison of measurements is presented below:
The table clearly shows that the new SQL Server is much faster than previous versions. Naturally, for your machine, time measurements may differ, but the performance of the new server will always be higher.
Denali’s new features in some situations may replace TOP.
For an example we will take a situation when it is necessary to receive the first 10 records sorted in descending order of any parameter.
Approaches on previous versions
Possible approach in SQL Server Denali
As noted in the comments, this is incorrect code and will return the result back to Top (10).
Translations from the cycle:
MS SQL Server 2011: Stand-alone databases , new Sequence object , Offset statement , error handling , With Result Set construct , new in SSMS .
- Offset
- Fetch First or Fetch Next ( take the first ... or take the next ... )
Offset
Using this command allows you to skip the specified number of lines before displaying the query results. What is meant by this: Suppose we have 100 entries in a table and we need to skip the first 10 rows and print lines 11 through 100. Now this is easily solved by the following query:
Select *
From
Order by
Offset 10 Rows
For those comrades who practice .Net, the extension method for Skip collections , which skips the specified number of lines, should be familiar . So the Offset expression works exactly the same. After the data is ordered in some way, the Offset expression can be applied.
Situations in which the Offset expression can be used
In all subsequent examples on Offset, it will use the data set constructed as a result of this script:
-- объявление табличной переменной
Declare @tblSample Table
(
[Person Name] Varchar(50)
,Age int
,Address Varchar(100)
)
-- заполнение данными
Insert into @tblSample
Select
'Person Name' + CAST(Number AS VARCHAR)
, Number
,'Address' + CAST(Number AS VARCHAR)
From master..spt_values
Where Type = 'p'
And Number Between 1 and 50
Task 1. Skip the first 10 entries and show the rest.
The script will be simple.
Select *
From @tblSample
Order by Age
Offset 10 Row
Or
Select *
From @tblSample
Order by Age
Offset (10) Rows
The output will be as follows:
Person Name Age Address
Person Name11 11 Address11
Person Name12 12 Address12
. . . . . . . . . . . . . .
. . . . . .. . . . . . . . .
Person Name49 49 Address49
Person Name50 50 Address50
It does not matter which word to use after indicating the number of lines: Row or Rows - they are synonyms in this case.
Task 2. Pass the number of lines to skip as a variable
-- Объявляем переменную в которой будет содержаться кол-во строк для пропуска
Declare @RowSkip As int
-- Выставляем количество строк для пропуска
Set @RowSkip = 10
-- получаем результат
Select *
From @tblSample
Order by Age
Offset @RowSkip Row
Task 3. Set the number of lines to skip as an expression
-- получить строки с 14 по 50
Select *
From @tblSample
Order by Age
Offset (select MAX(number)/99999999 from master..spt_values) Rows
The expression select MAX (number) / 99999999 from master..spt_values will return the number 14.
Task 4. Set the number of lines to skip as a user-defined function
Select *
From @tblSample
Order by Age
Offset (select dbo.fn_test()) Rows
Code for scalar user-defined function
CREATE FUNCTION fn_test()
RETURNS int
AS
BEGIN
Declare @ResultVar as int
Select @ResultVar = 10
RETURN @ResultVar
END
GO
Task 5. Using Offset with Order by inside views, functions, subqueries, nested tables, common table expressions (Common Table Expressions - CTE).
For example, use in general terms.
;With Cte As
(
Select *
From @tblSample
Order By Age
Offset 10 Rows)
Select *
From Cte
The example below shows the use of Offset and Order by inside a nested table.
Select *
From
(Select *
From @tblSample
Where Age >10
Order By Age
Offset 10 Rows) As PersonDerivedTable
And another example of the work of Offset and Order with representations.
-- Создание view
Create View vwPersonRecord AS
Select * FROM tblSample
GO
-- выборка данных из view
Select *
From vwPersonRecord
Where Age > 10
Order By Age
Offset 10 Rows
When Offset Will Not Work
1. Since this is an “extension method”, nothing will work without an order by expression.
Select *
From @tblSample
Offset (10) Rows
Report an error
Msg 102, Level 15, State 1, Line 21 Incorrect syntax near '10'.
2. You cannot set a negative value for Offset.
Select *
From @tblSample
Order by Age
Offset (-10) Rows
The SQL server engine will
produce Msg 10742, Level 15, State 1, Line 22 The offset specified in a OFFSET clause may not be negative.
3. You cannot specify values other than the integer type.
Select *
From @tblSample
Order by Age
Offset 10.5 Rows
or
Select *
From @tblSample
Order by Age
Offset Null Rows
Will give us
Msg 10743, Level 15, State 1, Line 24 The number of rows provided for a OFFSET clause must be an integer.
4. Cannot be used inside Over () expression .
;With Cte As
(
Select
*,
Rn = Row_Number() Over(Order by Age Offset 10 Rows)
From @tblSample
)
Select * from Cte
During the execution of the request, we will receive the message
Msg 102, Level 15, State 1, Line 22 Incorrect syntax near 'Offset'.
Using Fetch First / Fetch Next
These keywords are used to specify the number of rows returned after skipping an array of strings using the Offset expression. Imagine that we have 100 lines and we need to skip the first 10 and get the next 5 lines. Those. need to get lines from 11 to 15.
Select *
From
Order by
Offset 10 Rows
Fetch Next 5 Rows Only; -- или Fetch First 5 Rows Only
Such a query will return the expected number of rows. Programmers on .Net will immediately recall the Take extension method.
Next, consider situations where you can apply these keywords.
Task 1. Skip the first 10 entries and get the next 5
Select *
From @tblSample
Order by Age
Offset 10 Row
Fetch First 5 Rows Only
The result will be like this:
Person Name Age Address
Person Name11 11 Address11
Person Name12 12 Address12
Person Name13 13 Address13
Person Name14 14 Address14
Person Name15 15 Address15
Task 2. Set the number of lines to output using a variable
-- переменная для указания смещения
Declare @RowSkip As int
-- переменная для указания кол-ва возвращаемых строк
Declare @RowFetch As int
-- кол-во строк для пропуска
Set @RowSkip = 10
-- кол-во строк для возврата
Set @RowFetch = 5
-- вывод строк с 11 по 15
Select *
From @tblSample
Order by Age
Offset @RowSkip Row
Fetch Next @RowFetch Rows Only;
In general, with these keywords you can do the same thing as with Offset. Subqueries, views, functions, etc.
When Fetch First / Fetch Next Will Not Work
The restrictions on these keywords are fully consistent with the restrictions on Offset.
Offset and Fetch Next simulation for Sql Server 2005/2008
In previous versions of SQL Server, you could get the same functionality by using the Row_Number () ranking function. Of course, the code turned out to be not so elegant and concise, for example:
-- Переменная для указания строк смещения
Declare @RowSkip As int
-- Переменная для указания кол-ва строк для возврата
Declare @RowFetch As int
-- Задание переменных
Set @RowSkip = 10
Set @RowFetch = 5
;With Cte As
(
Select
rn=ROW_NUMBER()
Over(Order by (Select 1) /* генерируем служебную колонку */ )
,*
From @tblSample
)
-- забираем записи с 11 по 15
Select
[Person Name]
,Age
,Address
From Cte
-- симуляция поведения Offset и Fetch First/Fetch Next
Where rn Between (@RowSkip+1) -- симуляция Offset
And (@RowSkip+ @RowFetch) -- симуляция Fetch First/Fetch Next Clause
Inside the CTE, a service column is being generated that simply numbers the lines, after which the lines are filtered by this field. The method is not the fastest as you understand.
Offset and Fetch Next simulation for Sql Server 2000
For these ancient servers there were no ranking functions, but even then the functionality discussed could be repeated. Then, temporary tables with an auto incremental field were used. Example script:
Declare @RowSkip As int
Declare @RowFetch As int
Set @RowSkip = 10
Set @RowFetch = 5
--если временная таблица существует, то удалить ее
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
Drop Table #Temp
END
--создание временной таблицы
Create Table #Temp
(
Rn int Identity
,[Person Name] Varchar(50)
,Age int
,Address Varchar(100)
)
-- заполнение временной таблицы
Insert Into #Temp([Person Name],Age,Address)
Select [Person Name],Age,Address
From @tblSample
-- получение строк с 11 по 15
Select
[Person Name]
,Age
,Address
From #Temp
-- симуляция поведения Offset и Fetch First/Fetch Next
Where Rn Between (@RowSkip+1) -- симуляция Offset
And (@RowSkip+ @RowFetch) -- симуляция Fetch First/Fetch Next
In this script, a temporary table is first created where the data from the target table is overwritten. Moreover, in the temporary table there is an auto-increment field, by which then the necessary rows are requested.
Offset and Fetch practice with time and resource measurements
I am sure that all the previous explanation about the use and purpose of Offset and Fetch has led you to a clear understanding of why they are needed and where you can use them. Ideas were born to optimize existing code. Further we will consider an example from real practice when Offset can be useful. The results of performance measurements on different SQL servers will also be presented. Tests will be run on a sample of 1 million rows.
First, create an account table using the following script.
-- удалить таблицу tblSample, если она существует
IF OBJECT_ID('tblSample','U') IS NOT NULL BEGIN
DROP TABLE tblSample
END
GO
-- создать таблицу
Create Table tblSample (
[Person ID] Int Identity
,[Person Name] Varchar(100)
,Age Int
,DOB Datetime
,Address Varchar(100)
)
GO
-- заполнить таблицу миллионом записей
Insert into tblSample
Select
'Person Name' + CAST(N AS VARCHAR)
, N
,DATEADD(D,N, '1900-01-01')
,'Address' + CAST(N AS VARCHAR)
From dbo.tsqlc_Tally
Where N Between 1 and 1000000
-- вывести данные
Select *
From tblSample
Server-side data browsing
Page browsing is the most common function in systems for viewing records from any databases. Now it is possible to do this both on the client side and on the server side. Paging on the client side means loading the entire table, or a very large part of it into memory, in order to programmatically do pagination. On the other hand, this can be done on the server side, then the application will receive only the data that it requested to display the desired page. With this approach, the time for sending data, post-processing and storing them in memory is reduced. Those. There is a significant acceleration in application performance.
For experimental purposes, we will skip the first 20,000 entries and take the next 50,000.
Approach for SQL Server 2000
-- сброс буфера и кэша статистики
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
USE TSQLDB;
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
-- Переменные для пэйджинга
Declare @RowSkip As int
Declare @RowFetch As int
-- Установка значений постраничного просмотра
Set @RowSkip = 20000
Set @RowFetch = 50000
--Удаление временной таблицы, если она есть
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
Drop Table #Temp
END
-- создание временной таблицы
Create Table #Temp
(
Rn int Identity
,[Person ID] int
,[Person Name] Varchar(50)
,Age int
,DOB datetime
,Address Varchar(100)
)
-- Занесение данных во временную таблицу
Insert Into #Temp([Person ID],[Person Name],Age,DOB,Address)
Select [Person ID],[Person Name],Age,DOB,Address
From dbo.tblSample
-- выборка данных с 20 000 по 70 000
Select
[Person ID]
,[Person Name]
,Age
,DOB
,Address
From #Temp
-- симуляция поведения Offset и Fetch First/Fetch Next
Where Rn Between (@RowSkip+1) -- симуляция Offset
And (@RowSkip+ @RowFetch) -- симуляция Fetch First/Fetch Next
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
I think that the previous examples and comments are enough to understand the script.
Runtime:
SQL Server Execution Times:
CPU time = 110 ms, elapsed time = 839 ms.
I / O statistics:
Scan count 1,
logical reads 8037,
physical reads 0,
read-ahead reads 0,
lob logical reads 0,
lob physical reads 0,
lob read-ahead reads 0.
Approach for SQL Server 2005/2008
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
USE TSQLDB;
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
Declare @RowSkip As int
Declare @RowFetch As int
Set @RowSkip = 20000
Set @RowFetch = 50000
;With Cte As
(
Select
rn=ROW_NUMBER()
Over(Order by (Select 1))
,*
From dbo.tblSample
)
Select
[Person ID]
,[Person Name]
,Age
,DOB
,Address
From Cte
Where rn Between (@RowSkip+1)
And (@RowSkip+ @RowFetch)
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
Runtime:
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 631 ms.
I / O statistics:
Scan count 1,
logical reads 530,
physical reads 0,
read-ahead reads 1549,
lob logical reads 0,
lob physical reads 0,
lob read-ahead reads 0.
Approach for SQL Server 2011
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
USE TSQLDB;
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
Declare @RowSkip As int
Declare @RowFetch As int
Set @RowSkip = 20000
Set @RowFetch = 50000
Select *
From dbo.tblSample
Order by (Select 1)
Offset @RowSkip Row
Fetch Next @RowFetch Rows Only;
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
Time performance :
the SQL Server Execution Times:
the CPU time = 47 ms, elapsed time = 626 ms.
I / O statistics:
Scan count 1,
logical reads 530,
physical reads 0,
read-ahead reads 1549,
lob logical reads 0,
lob physical reads 0,
lob read-ahead reads 0.
The most interesting result is the use of CPU time (CPU Time ) and runtime (Elapsed Time - time required by the request to execute). Comparison of measurements is presented below:
Sql Server Version | CPU time | Elapsed time |
2000 | 110ms | 839 ms |
2005/2008 | 78ms | 631 ms |
2011 | 46ms | 626 ms |
The table clearly shows that the new SQL Server is much faster than previous versions. Naturally, for your machine, time measurements may differ, but the performance of the new server will always be higher.
Alternative to TOP expression
Denali’s new features in some situations may replace TOP.
For an example we will take a situation when it is necessary to receive the first 10 records sorted in descending order of any parameter.
Approaches on previous versions
Select Top(10)
[Person ID]
,[Person Name]
,Age
,DOB
,Address
From dbo.tblSample
Order By Age Desc
Possible approach in SQL Server Denali
Select
[Person ID]
,[Person Name]
,Age
,DOB
,Address
From dbo.tblSample
Order By Age Desc
Offset 10 Rows
As noted in the comments, this is incorrect code and will return the result back to Top (10).
Translations from the cycle:
MS SQL Server 2011: Stand-alone databases , new Sequence object , Offset statement , error handling , With Result Set construct , new in SSMS .