Posted by Gert-Jan Strik on 10/16/07 19:10

Tim wrote:
>
> Hi Folks,
>
> I come from an Informix background, (pauses for the laughter to die
> down), where SQL can access a hidden attribute, (ROWID), that uniquely
> identifies each record in a table at that point in time. It's similar
> to an IDENTITY attribute but it is not visible unless specifically
> selected and the RDBMS actively re-uses the numbers as they become
> vacant through deletion of records.
>
> However it can be very useful if you are trying to unscramble static
> but erroneously duplicate data rows built up due to an 'undocumented
> feature' of the application software. This is the situation I find
> myself in at the moment, so I was wondering if there was anything of a
> similar nature hidden or undocumented in SQL Server?
>
> I realise I could make a copy of the table and add an IDENTITY
> attribute, but I'm playing with some 75,000,000 rows of data that must
> all be salvaged, which complicates matters a little.
>
> Tia, Tim

It depends whether the table has a clustered index.

If there is no clustered index, then actual page and slot is the
physical key. Any nonclustered index would include this key for its
indexed value.

If you have a clustered index, and it is unique, then the clustered key
is the logical key, used to look up the rows in the clustered index
tree. So in that case there is no additional information (hidden or
otherwise).

If you have a clustered index, and it is not unique, then again, the
clustered key is the logical key. If there are duplicates, the
duplicate(s) will have a uniqueifier. The same uniqueifier (if present)
can be found in the data page. The uniqueifier is a value per clustered
key value, not an absolute value like Identity.

HTH,
Gert-Jan

[Back to original message]