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 ?