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
...
Is there a simple workaround for the OVER/PARTITION clause, other than separating the queries and then doing GROUP BY ?
Also, has anyone done large-scale conversion of SPs to Natively-Compiled and any roadblocks you encountered ?