Databases
StackOverflow2013
Postsdbo
POWhich T-SQL Skip Take Query Is Better?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhich T-SQL Skip Take Query Is Better?
    primarykey
    data
    text
    <p><strong>12/17/2015 - UPDATE</strong></p> <p>It's been a while since I asked this question and haven't really gotten an answer so I thought I would post the solution that my team ended up using. </p> <p>We originally started by using the dynamic SQL option to get the smart sorting. While functional this felt really dirty. We eventually came up with the following implementation:</p> <pre><code>--Params DECLARE @pageSize INT , @pageIndex INT , @sortBy varchar(30) , @sortDirection varchar(4); SET @pageIndex = 1; SET @pageSize = 10; SET @sortBy = 'PolicyCode'; SET @sortDirection = 'ASC'; --Vars DECLARE @start INT, @end INT; --Page 1-n SET @pageIndex = 1; SET @pageSize = 10; SET @start = (@pageSize * @pageIndex) - (@pageSize - 1); SET @end = (@pageSize * @pageIndex); ;WITH PolicyCTE AS ( SELECT p.PolicyId, p.PolicyTypeId, p.PolicyCode, p.PolicyDesc, p.EffectiveDate, p.ExpirationDate FROM Policy p INNER JOIN PolicyOrg po ON p.PolicyId = po.PolicyId ) , PagedResultsCTE AS ( SELECT [PolicyId] , [PolicyTypeId] , [PolicyCode] , [PolicyDesc] , [EffectiveDate] , [ExpirationDate] , CASE WHEN @sortBy = 'PolicyTypeId' AND @sortDirection = 'ASC' THEN ROW_NUMBER() OVER (ORDER BY [PolicyTypeId] ASC) WHEN @sortBy = 'PolicyTypeId' AND @sortDirection = 'DESC' THEN ROW_NUMBER() OVER (ORDER BY [PolicyTypeId] DESC) WHEN @sortBy = 'PolicyCode' AND @sortDirection = 'ASC' THEN ROW_NUMBER() OVER (ORDER BY [PolicyCode]) WHEN @sortBy = 'PolicyCode' AND @sortDirection = 'DESC' THEN ROW_NUMBER() OVER (ORDER BY [PolicyCode] DESC) WHEN @sortBy = 'PolicyDesc' AND @sortDirection = 'ASC' THEN ROW_NUMBER() OVER (ORDER BY [PolicyDesc]) WHEN @sortBy = 'PolicyDesc' AND @sortDirection = 'DESC' THEN ROW_NUMBER() OVER (ORDER BY [PolicyDesc] DESC) WHEN @sortBy = 'EffectiveDate' AND @sortDirection = 'ASC' THEN ROW_NUMBER() OVER (ORDER BY [EffectiveDate]) WHEN @sortBy = 'EffectiveDate' AND @sortDirection = 'DESC' THEN ROW_NUMBER() OVER (ORDER BY [EffectiveDate] DESC) WHEN @sortBy = 'ExpirationDate' AND @sortDirection = 'ASC' THEN ROW_NUMBER() OVER (ORDER BY [ExpirationDate]) WHEN @sortBy = 'ExpirationDate' AND @sortDirection = 'DESC' THEN ROW_NUMBER() OVER (ORDER BY [ExpirationDate] DESC) END AS RowNumber FROM PolicyCTE ) SELECT PolicyId, PolicyTypeId, PolicyCode, PolicyDesc, EffectiveDate, ExpirationDate FROM PagedResultsCTE WHERE RowNumber BETWEEN @start AND @end ORDER BY RowNumber GO </code></pre> <p>By combining CASE statements and another CTE, we are able to sort by the passed in sorting criteria and also get the correct ROWNUMBER() value. Then, in our final select statement, we can simply order by the rownumber column.</p> <p>I'll avoid marking the question answered for now in case anyone wants to chime in on this solution. Feedback is definitely welcome.</p> <p><strong>ORIGINAL POST</strong></p> <p>Maybe someone with a little more DBA knowledge can shed some light on this for me. The basic premise: Get paginated data to improve page load performance (get records 1-10, 11-20, etc.) using an optimized query within a stored procedure. The tables have thousands of records.</p> <p>I'm using SQL Server 2008 R2 and came up with the following T-SQL for use in a stored procedure:</p> <pre><code>--Params DECLARE @pageSize INT, @pageIndex INT; SET @pageIndex = 1; SET @pageSize = 10; --Vars DECLARE @start INT, @end INT --Page 1-n SET @pageIndex = 1; SET @pageSize = 10; SET @start = (@pageSize * @pageIndex) - (@pageSize - 1); SET @end = (@pageSize * @pageIndex) ;WITH PolicyCTE AS ( SELECT ROW_NUMBER() OVER (ORDER BY p.PolicyCode) AS RowNumber, p.PolicyId, p.PolicyTypeId, p.PolicyCode, p.PolicyDesc, p.EffectiveDate, p.ExpirationDate FROM Policy p INNER JOIN PolicyOrg po ON p.PolicyId = po.PolicyId ) SELECT PolicyId, PolicyTypeId, PolicyCode, PolicyDesc, EffectiveDate, ExpirationDate FROM PolicyCTE WHERE RowNumber BETWEEN @start AND @end ORDER BY PolicyCode GO </code></pre> <p>In my search to find best practices on how to do this, I ran into this <strong>old</strong> MSDN Patterns and Practices article: <a href="http://msdn.microsoft.com/en-us/library/ff650700.aspx" rel="nofollow">How To: Page Records in .NET Applications</a> (applies to SQL 2000 and states the article is out of date). Referencing the <strong>User-Specific Records</strong> section, a somewhat equivalent T-SQL would look like this:</p> <pre><code>DECLARE @pageIndex int, @pageSize int, @sortBy nvarchar(30) SET @pageIndex = 0; SET @pageSize = 10; SET @sortBy = 'PolicyCode'; DECLARE @rowsToRetrieve int, @sortDirFlipped nvarchar(4); IF @pageIndex &lt; 1 SET @pageIndex = 1; IF @pageSize &lt; 1 SET @pageSize = 10; SET @rowsToRetrieve = @pageIndex * @pageSize DECLARE @sqlString nvarchar(1000); SET @sqlString = N' SELECT PolicyId, PolicyTypeId, PolicyCode, PolicyDesc, EffectiveDate, ExpirationDate FROM ( SELECT TOP ' + CAST(@pageSize AS VARCHAR(10)) + ' PolicyId, PolicyTypeId, PolicyCode, PolicyDesc, EffectiveDate, ExpirationDate FROM ( SELECT TOP ' + CAST(@rowsToRetrieve AS VARCHAR(10)) + ' PolicyId, PolicyTypeId, PolicyCode, PolicyDesc, EffectiveDate, ExpirationDate FROM ( SELECT TOP ' + CAST(@rowsToRetrieve AS VARCHAR(10)) + ' p.PolicyId, p.PolicyTypeId, p.PolicyCode, p.PolicyDesc, p.EffectiveDate, p.ExpirationDate FROM Policy p INNER JOIN PolicyOrg po ON p.PolicyId = po.PolicyId ORDER BY p.' + @sortBy + ' ) AS T2 ORDER BY ' + @sortBy + ' DESC ) AS T3 ) AS T4 ORDER BY ' + @sortBy + ' ASC'; EXEC(@sqlString) GO </code></pre> <p>I've run both of these with Execution Plan and Client Statistics on and it seems to me (definitely not an expert in analyzing those results) that the MSDN version performs better. Part of the reason, from what I can gather, is that the MSDN version starts out with a smaller record set. However, I really dislike the fact that the MSDN version uses a dynamic query. My understanding is that they are more vulnerable to SQL injection attacks.</p> <p>Most of the examples I found prefer the form of the first sample. Can someone help explain this to me and site reasons why, besides the ones already listed, the first form would be better than the second?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USfujiiface
    1. USfujiiface
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. CODynamic SQL is vulnerable when exposed outside something like a stored procedure. The MSDN example can be convert if you have a static sort. I would also look at the ROW_NUMBER function.
      singulars
      1. POWhich T-SQL Skip Take Query Is Better?
      1. USChris H
    2. COThe former is definitely better and will be cacheable by the query optimizer, but the dynamic SQL might be a bit smarter about how it does its query. Have you looked into [Offset and Fetch](http://msdn.microsoft.com/en-us/library/gg699618.aspx) in SQL Server? If you're running SQL Server 2012, it'll be much faster than either option.
      singulars
      1. POWhich T-SQL Skip Take Query Is Better?
      1. USAaron Friel
    3. CO@AaronFriel, I have looked into that but unfortunately I am not running SQL Server 2012. I guess it depends on whether I want it to be cacheable vs. smarter.
      singulars
      1. POWhich T-SQL Skip Take Query Is Better?
      1. USfujiiface
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload