Equivalent of Oracle's RowID in SQL Server

SQL Server’s %%physloc%%: The transient equivalent of Oracle's RowID

Making sense of %%physloc%%

%%physloc%% acts as SQL Server's equivalent to Oracle's RowID, showing the physical location of a row in a database file. Yet unlike RowID, %%physloc%% is a binary(8) value that changes if the row's location changes due to operations like page splits. It's comparable to using a post-it note for temporary labels.

Reading the %%physloc%%

The %%physloc%% returns a binary(8) value. To read this:

• Use sys.fn_PhysLocFormatter(%%physloc%%). It converts the binary to a human-readable file:page:slot format.
• Use sys.fn_PhysLocCracker(%%physloc%%). It breaks down the binary into separate columns for file, page, and slot numbers.

These handy tools can help turn the Matrix code of %%physloc%% into understandable language.

Stable row identification: Building the RowID equivalent

Choosing the right identifier

For stable row identification, akin to Oracle's RowID, consider:

• Primary Key: It's the vanilla of identifiers - simple and timeless. A unique identifier for each row, it doesn’t change once set.
• IDENTITY: The auto-generated sequencing of numbers for each new row. It's like having a sticker-gun labelling everything in sight.
• UNIQUEIDENTIFIER: A globally unique identifier (GUID) automatically generated by SQL Server. It's like assigning a unique barcode to every row.

These are constant identifiers, ensuring each row remains uniquely identifiable.

Assigning dynamic row numbers

SQL Server offers:

• ROW_NUMBER(): This function hands out a unique number to every row in the result set, like numbers at a deli counter. Useful for displaying reports or identifying duplicates.

• RANKING FUNCTIONS: The likes of RANK(), DENSE_RANK(), and NTILE() offer a relative ordering of rows. Ideal for analytics or partitioning data into groups or quantiles. It's like sorting contestants in a competition.

These functions come in handy when altering the schema to add a permanent identifier is a “no-no", but dynamic row numbering is needed.

Gotchas and limitations

It's not all apples to apples

While %%physloc%% might seem like a clone of Oracle's RowID, it isn't! Specifically, SQL Server may not go straight to a row using %%physloc%% , this NBA-style dribbling can affect query performance.

Keep a close eye while batching

When dealing with large result sets or batch processing, SQL Server cursors with a FOR UPDATE option offer stability. But remember, excessive use of cursors can be as bad as excessive use of emojis 😉.

Watch your step while deduplicating

When you're deduplicating massive tables, it's like walking a performance tightrope. The method you choose (IDENTITY columns, %%physloc%%, or window functions) can make a big difference in stability and efficiency.