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, 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, 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, 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, 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, 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

3362 silver badges8 bronze badges

Because the timestamp columns already are in tables and used in apps. Advisor said me ts is not supported
– ZedZip
Commented Jul 10, 2024 at 13:18
Then why do you want to make this a MO table? Are you sure of the use case?
– Aardvark
Commented Jul 10, 2024 at 13:41
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
– ZedZip
Commented Jul 12, 2024 at 7:12

Add a comment |

Collectives™ on Stack Overflow

Using timestamp columns in a memory optimized table

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related