Using timestamp columns in a memory optimized table

Question

I am researching how to convert some physical tables to memory-optimized tables.
But these tables have timestamp column. This data type is not supported in MO. How to replace it? Now these columns used in app to compare and check if another app changed the row.

You can use a datetime stamp which you can compare against instead when checking for concurrent changes, of course this requires all places to update it, but it's possible to use trigger for that in worst case. On the other hand, i'm a bit wary when people randomly convert tables to memory tables, what's your use case? — siggemannen
– siggemannen, Commented Jul 10, 2024 at 11:14
I am researching ways to optimize performance, now I think to convert some of tables to MO. At least I can use MP instead of temp tables and table variables. — ZedZip
– ZedZip, Commented Jul 10, 2024 at 13:17
@siggemannen the datetime has lass precise in comparison with ts, i.e. now I have N records with the same datetime values but different ts — ZedZip
– ZedZip, Commented Jul 10, 2024 at 13:20
You can use datetime2 perhaps, but yeah, if you have that much activity going on, then perhaps you have bigger problems than timestamps — siggemannen
– siggemannen, Commented Jul 10, 2024 at 13:33
I just want to jump in here in that I strongly believe that timestamp as used here, is the deprecated (and unfortunately named) synonym for rowversion. If so, ongoing discussion on the merits of datetime et al are largely irrelevant. — Ben Thul
– Ben Thul, Commented Jul 10, 2024 at 15:17

Aardvark · Accepted Answer · 2024-07-10 10:55:26Z

0

I am not quite sure why you would want rowversion in a MO table. Have you run the Memory Optimization Advisor?

You could try using a trigger to update a column with a sequence or NEWID().

answered Jul 10, 2024 at 10:55

Aardvark

3662 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ZedZip Over a year ago

Because the timestamp columns already are in tables and used in apps. Advisor said me ts is not supported

Aardvark Over a year ago

Then why do you want to make this a MO table? Are you sure of the use case?

ZedZip Over a year ago

I am searching ways to improve performance of our database. It works on SQL 2022, has not so large size but bad performance on some queries

Collectives™ on Stack Overflow

Using timestamp columns in a memory optimized table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related