MS SQL 2011 - New Offset Operator
The new SQL Server 2011 (Denali) extends the capabilities of the Order By command with the help of two long-awaited additional commands:
Using this command allows you to skip the specified number of lines before displaying the results of the query. What is meant by this: Suppose we have 100 entries in the table and need to skip the first 10 lines and print lines 11 to 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 expression Offset works the same way. After the data is ordered in some way, you can use the expression Offset.
')
In all subsequent examples on Offset will use a 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 specifying the number of rows: Row or Rows - they are synonymous in this case.
Task 2. Transfer the number of rows to skip as a variable
Task 3. Set the number of lines to skip as an expression
The select expression MAX (number) / 99999999 from master..spt_values returns the number 14.
Task 4. Set the number of rows to skip as a user-defined function
Code for scalar user function
Task 5. Using Offset with Order by inside views (view), functions, subqueries, nested tables, common table expressions (CTE).
For example, use in general terms.
The example below shows the use of Offset and Order by within a nested table.
And another example of the work Offset and Order with views.
1. Since this is an “extension method”, nothing will work without an order by clause.
Report an error
Msg 102, Level 15, State 1, Line 21 Incorrect syntax near '10'.
2. You can not set a negative value for Offset.
SQL Server engine will issue
Msg 10742, Level 15, State 1, Line 22 The offset specified in the OFFSET clause may not be negative.
3. It is impossible to set values ​​other than integer type.
or
Will give us
Msg 10743, Level 15, State 1, Line 24 clause must be an integer.
4. Cannot be used inside an Over () expression.
During the execution of the request will receive a message
Msg 102, Level 15, State 1, Line 22 Incorrect syntax near 'Offset'.
These keywords are used to specify the number of returned rows 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. you need to get lines 11 through 15.
Such a query will return the expected number of rows. Programmers on .Net will immediately recall Take expansion method.
Next, consider the situations where you can apply these keywords.
Task 1. Skip the first 10 entries and get the next 5
The result will be:
Task 2. Set the number of lines to display using a variable
In general and in general, with these keywords you can do everything the same as with Offset. Subqueries, views, functions, etc.
The restrictions on these keywords completely coincide with the restrictions on the Offset.
In previous versions of SQL Server, you could get the same functionality by applying the Row_Number () ranking function. Of course, the code was not so elegant and concise, for example:
Inside the CTE, a service column is generated that simply numbers the lines, after which the lines are filtered by this field. The method is not the fastest as you know.
For these ancient servers there was no ranking function, but even then the discussed functionality could be repeated. Then temporary tables with an auto incremental field were used. Sample script:
In this script, a temporary table is first created where the data from the target table is overwritten. And in the temporary table there is an auto-incremental field, according to which then the necessary rows are requested.
I am sure that all the previous explanations about the use and purpose of Offset and Fetch led you to a clear understanding of why they are needed and where they can be used. Born ideas for optimizing existing code. Next, we consider an example from real practice, when an Offset can be useful. There will also be results of performance measurements on different SQL servers. Tests will be run on a sample of 1 million lines.
To begin, create an invoice table for the following script.
Page by page view is the most common function in record viewing systems from any database. 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 make pagination. On the other hand, this can be done on the server side, then the application will receive only the data that it has requested to display the desired page. With this approach, time is reduced for sending data, post-processing and storing them in memory. Those. There is a significant acceleration of application performance.
For the purpose of the experiment, 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 work of the script.
Lead time:
SQL Server Execution Times:
CPU time = 110 ms, elapsed time = 839 ms.
Input / Output 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.
Lead time:
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 631 ms.
Input / Output 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.
Lead time :
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 626 ms.
Input / Output 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 on the use of processor time (CPU Time) and the execution time (Elapsed Time - the time taken to execute the request). Comparison of measurements is presented below:
The table clearly shows that the new SQL Server runs noticeably faster than previous versions. Naturally, the timing of your machine may vary, but the performance of the new server will always be higher.
New features Denali in some situations can be a substitute for the expression TOP.
For example, take the situation when you need to get the first 10 records sorted in descending order of any parameter.
Approaches on previous versions
Approach possible in SQL Server Denali
As noted in the comments, this is the wrong code and will return the result inverse Top (10).
Transfers from the cycle:
MS SQL Server 2011: Autonomous databases , new Sequence object , Offset operator , error handling , With Result Set construction , new in SSMS .
- Offset
- Fetch First or Fetch Next ( take the first ... or take the following ... )
Offset
Using this command allows you to skip the specified number of lines before displaying the results of the query. What is meant by this: Suppose we have 100 entries in the table and need to skip the first 10 lines and print lines 11 to 100. Now this is easily solved by the following query:
Select * From <SomeTable> Order by <SomeColumn> 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 expression Offset works the same way. After the data is ordered in some way, you can use the expression Offset.
')
Situations in which the expression Offset can be used
In all subsequent examples on Offset will use a 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 specifying the number of rows: Row or Rows - they are synonymous in this case.
Task 2. Transfer the number of rows 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 select expression MAX (number) / 99999999 from master..spt_values returns the number 14.
Task 4. Set the number of rows to skip as a user-defined function
Select * From @tblSample Order by Age Offset (select dbo.fn_test()) Rows Code for scalar user 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 (view), functions, subqueries, nested tables, 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 within 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 Offset and Order with views.
-- 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 clause.
Select * From @tblSample Offset (10) Rows Report an error
Msg 102, Level 15, State 1, Line 21 Incorrect syntax near '10'.
2. You can not set a negative value for Offset.
Select * From @tblSample Order by Age Offset (-10) Rows SQL Server engine will issue
Msg 10742, Level 15, State 1, Line 22 The offset specified in the OFFSET clause may not be negative.
3. It is impossible to set values ​​other than 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 clause must be an integer.
4. Cannot be used inside an 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 will receive a 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 returned rows 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. you need to get lines 11 through 15.
Select * From <SomeTable> Order by <SomeColumn> 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 Take expansion method.
Next, consider the 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:
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 display 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 and in general, with these keywords you can do everything the same as with Offset. Subqueries, views, functions, etc.
When Fetch First / Fetch Next Will Not Work
The restrictions on these keywords completely coincide with the restrictions on the Offset.
Offset and Fetch Next for Sql Server 2005/2008
In previous versions of SQL Server, you could get the same functionality by applying the Row_Number () ranking function. Of course, the code was 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 generated that simply numbers the lines, after which the lines are filtered by this field. The method is not the fastest as you know.
Offset and Fetch Next for Sql Server 2000
For these ancient servers there was no ranking function, but even then the discussed functionality could be repeated. Then temporary tables with an auto incremental field were used. Sample 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. And in the temporary table there is an auto-incremental field, according to which then the necessary rows are requested.
The practical use of Offset and Fetch with measurements of time and resources
I am sure that all the previous explanations about the use and purpose of Offset and Fetch led you to a clear understanding of why they are needed and where they can be used. Born ideas for optimizing existing code. Next, we consider an example from real practice, when an Offset can be useful. There will also be results of performance measurements on different SQL servers. Tests will be run on a sample of 1 million lines.
To begin, create an invoice table for 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 Paging through server-side data
Page by page view is the most common function in record viewing systems from any database. 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 make pagination. On the other hand, this can be done on the server side, then the application will receive only the data that it has requested to display the desired page. With this approach, time is reduced for sending data, post-processing and storing them in memory. Those. There is a significant acceleration of application performance.
For the purpose of the experiment, 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 work of the script.
Lead time:
SQL Server Execution Times:
CPU time = 110 ms, elapsed time = 839 ms.
Input / Output 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) <em> </em> And (@RowSkip+ @RowFetch) GO SET STATISTICS IO OFF; SET STATISTICS TIME OFF; GO Lead time:
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 631 ms.
Input / Output 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 Lead time :
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 626 ms.
Input / Output 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 on the use of processor time (CPU Time) and the execution time (Elapsed Time - the time taken to execute the request). 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 runs noticeably faster than previous versions. Naturally, the timing of your machine may vary, but the performance of the new server will always be higher.
Alternative to TOP
New features Denali in some situations can be a substitute for the expression TOP.
For example, take the situation when you need to get 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 Approach possible 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 the wrong code and will return the result inverse Top (10).
Transfers from the cycle:
MS SQL Server 2011: Autonomous databases , new Sequence object , Offset operator , error handling , With Result Set construction , new in SSMS .
Source: https://habr.com/ru/post/123491/
All Articles