Menu
I have been exploring the practical uses of the %%physloc%% pseudo-column as a row-identifier. Unfortunately, I have been having trouble finding official documentation on %%physloc%%. I need to know whether this value can ever change.
I have done a bit of testing and it seems pretty static. Even if I create a backup of my database and restore on a different server, the %%physloc%% of each row stays the same.
Can anyone please explain whether this is reliable and/or provide links to documentation? Much appreciated.
710
The Virtual %%physloc%% gives us the physical row location in a binary format. Using the builtin SQL function fn_physlocFormatter, we can format this binary to string and this string in file_id:page_id:slot_id format.
In Oracle, there is a pseudo column for each row called ROWID which reveals the physical address of a row in the database file. This column can be used to identify a single row even when a row doesn't have a key.
It can change for any reason that moves rows: Rebuilds, shrink, page splits, reorganize, etc.
SQL Server does not provide a row ID.
120
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
