Alternative to row_number in natively compiled stored procedure

Question

Alternative to row_number in natively compiled stored procedure

MullerN

SSC Enthusiast

Points: 181
More actions
February 15, 2023 at 5:16 pm

#4150095

I have a memory optimized table with identity column. The primary key is a combination of a project ID and the identity column. The problem I have is that the identity column is close to reaching it's limit. The table gets deleted periodically, but the identity keeps increasing. I could switch to bigint but I don't want to do that.
My true solution is to use row_number() over (order by XXX) for each project as the number increases and remove the identity column. However, window functions (over clause) are not supported in natively compiled stored procedures.
Is there an alternative, or code I could use in natively compiled stored procedures that would achieve my goal?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

It is possible and straightforward to reset the IDENTITY property of a column after deleting rows in its table. Would that solve your problem?

This reply was modified 1 year, 10 months ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Thom A SSC Guru Points: 99133 More actions · Answer 2

If you are deleting all the data why not TRUNCATE the table instead? That would also reset the IDENTITY.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

MullerN SSC Enthusiast Points: 181 More actions · Answer 3

Reseeding of memory optimized tables are not supported; that is, DBCC CHECKIDENT ('mytable', RESEED, 0) is not supported for memory optimized table whether done inside or outside of a natively compiled stored procedure.

I thought of creating a memory optimized table type with an identity column. That will always reseed to 1 at the start of the procedure. At the end of the procedure I can store the data in the permanent memory optimized table. However, I'm concerned about the performance having to create the memory optimized table from the table type every time the procedure is called.

MullerN SSC Enthusiast Points: 181 More actions · Answer 4

Truncate statement is not supported for memory optimized tables.

Thom A SSC Guru Points: 99133 More actions · Answer 5

I suppose the question, then, is why do you keep deleting all the data and then inserting it (again?)? What is the need to delete all the rows and then provide new ones?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Ken McKelvey SSCoach Points: 18856 More actions · Answer 6

MullerN wrote:

I have a memory optimized table with identity column.

Can you use a sequence instead. A sequence can be CYCLEd.

MullerN SSC Enthusiast Points: 181 More actions · Answer 7

Unfortunately, sequences are also not allowed. That was actually my very first option.

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 8

You are at the edge of a cliff. I strongly recommend you turn around and go another way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MullerN SSC Enthusiast Points: 181 More actions · Answer 9

I found an alternative to my specific problem. I used the identity to also sort the data and had another column that I could use to sort the data. I removed the identity column, have other uniqueness in the table where I could create the primary key and the sorted on the column I had.

However, I did not find a solution to the problem I originally posted. Thanks, everyone for the replies.

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 10

MullerN wrote:

I found an alternative to my specific problem. I used the identity to also sort the data and had another column that I could use to sort the data. I removed the identity column, have other uniqueness in the table where I could create the primary key and the sorted on the column I had.
However, I did not find a solution to the problem I originally posted. Thanks, everyone for the replies.

Thanks for the feedback on what you've done. I do have a question, though... why was "Natively Compiled" stored procedures so important in this case? And, not asking to be challenging or a smart-guy about it. I've not used such a thing before and we have an issue at one of the companies I do some work for suggested that particular methodology. I've read the propaganda about it but there's absolutely nothing better than getting the opinion of someone that has actually used the feature.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MullerN SSC Enthusiast Points: 181 More actions · Answer 11

Jeff, when done right, natively compiled stored procedure achieve incredible performance gains. They are compiled software (DLLs), much like C++ code. The problem is that there are many limitations and you may have to loop one row at a time in some instances. But even then, it is still faster than interpreted stored procedures.

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 12

MullerN wrote:

Jeff, when done right, natively compiled stored procedure achieve incredible performance gains. They are compiled software (DLLs), much like C++ code. The problem is that there are many limitations and you may have to loop one row at a time in some instances. But even then, it is still faster than interpreted stored procedures.

Thank you for taking the time to provide some feedback on my question. And, yes, I totally understood that that's the claim. But, did you actually realize such a performance gain in this particular case?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)