May 4, 2011 at 9:11 am
I found an interesting issue that took a bit to debug whilst using dynamic sql to create a cte that I'm using on a website platform.
First the reason I chose the dynamic sql was because of the lengthy custom requirements for both the where clause but also the sorting needed. I am a big fan of the CTE's for paging. My database potentially could release 20k to 30k records depending on the parameters chosen, so it's not terribly large.
Typically any good sql db will create their script first and then transfer it to a variable for processing.
In my tests my script worked flawlessly. But when I tested the dynamically created script my sorted results were flawed. No matter the change in how I would sort the content in the CTE (I'm using the ROWNUMBER() OVER(ORDER BY {X} [ROW_ID] method) the data would not be delivered with the proper sort. If I copied the "manufactured" script as it was being processed and ran it manually then the script would produce the correct results. I tried using the Forceplan, thinking maybe there was an issue maybe with the joins but to no avail. I then had a simple epiphany. Why not order by the exported row id's. This worked.:-)
What you should take from this quick note is I recommend you make a habit as a SQL05/08+ developer to be strict with your coding and never assume the results will come in the correct order. And in this case, order your CTE results so that if you ever have to use dynamic sql, you'll reduce some debug time (at least with sorting).
Thing to remember is that with dynamic sql you lose a certain level of plan caching and can lead to unexpected results.
Oh on a side note, do not try to create a multiple CTE via dynamic sql. The performance hit is never going to be acceptable!
Example
;with MY_CTE as
(
Select rownumber() over(order by item_price desc, item_id asc) [row_id],
item_id,
item_name,
item_price
From Some_Item_TBL
)
select item_id, item_name, item_price
from MY_CTE
where row_id >= @start_row
and row_id <= @end_row
order by row_id
Hope this helps someone. I didn't lose any hair on it but I did have to sleep on it (issue took place in the late afternoon during testing 😉 ).
May 5, 2011 at 7:47 am
Chuck,
I'm not sure I understand what you are saying. I ran this against the AdventureWorks database and the sorting was as I expected. Hardcoded or dynamic. Can you expand on the issue you experienced?
declare @sql nvarchar(max) = '
;with MY_CTE as
(
Select row_number() over(order by UnitPrice desc, PurchaseOrderID asc) [row_id],
PurchaseOrderID,
DueDate,
UnitPrice
From Purchasing.PurchaseOrderDetail
)
select *
from MY_CTE '
exec (@sql)
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 5, 2011 at 8:08 am
Hi Mike,
Using the same structure I left in my example, here's a snippet of my stored procedure below:
declare @parameters nvarchar(500) = N'@id int, @totalcount int output'
declare @sql_string nvarchar(max) = ''
declare @sql_sort nvarchar(500) = ''
declare @startrow int= ((@page-1) * @pagesize) + 1
declare @endrow int= @page*@pagesize
set @sql_sort = case
when @sorttypeid <= 1 then '<some sort here>'
when @sorttypeid = 2 then '<some sort here>'
when @sorttypeid = 3 then '<some sort here>'
else '1'
end
set @sql_string = '
Select @totalcount = count(*) from dbo.AUTOS a, dbo.ITEMS i where a.itemid=i.itemid and i.itemstatusid=1 and a.makeid = @make_id ' + @where_clause + '
;with MY_CTE as
(
select rownumber() over(order by item_price desc, item_id asc) [row_id],
item_id,
item_name,
item_price
From Some_Item_TBL
where item_id = @id
' + @where_clause + '
)
select item_id,
item_name,
item_price
from MY_CTE
where row_id >= ' + cast(@startrow as nvarchar) + ' and row_id <= ' + cast(@endrow as nvarchar) +
'Order by row_id '
--select @sql_string
execute sp_executesql @sql_string, @parameters, @id = @id, @totalcount = @totalcount output
return @totalcount
The big difference between our codes is I'm using sp_executesql instead of raw execute.
You'll laugh but the code worked on one of my machines and then for some reason shortly after initial development the sorts went bad. By enforcing a sort on the row_id field that I created off of the ROWNUMBER() function in the out going select, I'm ensuring my sort order. The OVER(ORDER BY {X}) is producing the correct record set. The problem I was having was that the result set was not being delivered in the correct order with just the the internal sort alone, I had to sort ascending by the row id.
This post was basically a note to offer anyone who may have had this problem, or anyone who may have this problem, a quick idea as to how to simply resolve it.
I hope this clears up any concerns you had.
Take care...
May 5, 2011 at 10:25 am
As you discovered, SQL does not guarentee any result order without an ORDER BY clause. That is true with any query, not just CTEs.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply