We're looking at converting several normal T-SQL Stored Procedures into Natively Compiled SPs for performance.
The SPs use a ton of #tmp tables - select into, delete from, update from, and several unsupported constructs like SUM/MAX/ROW_NUMBER OVER PARTITION BY, WITH, LIKE etc.
We have figured workarounds for many of these :
Select into - Create table type ahead of time
Delete from - use while loop as cursor
I was able to work around SUM/MAX OVER PARTITION BY.. but don't know how to do it for ROW_NUMBER OVER PARTITION BY. Is there a way ?
Also, has anyone done large-scale conversion of SPs to Natively-Compiled and any roadblocks you encountered ?