Can anybody help? Is there an undocumented unique record key in SQL Server?

Can anybody help? Is there an undocumented unique record key in SQL Server?

am 16.10.2007 14:32:05 von Tim

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

Re: Can anybody help? Is there an undocumented unique record keyin SQL Server?

am 16.10.2007 16:22:55 von AJ

Are you on SQL Server 2005? Have a look at the ROW_NUMBER() function.

ala:
SELECT ROW_NUMBER() OVER (ORDER BY ) AS ROW,
col,col,col,col FROM



Combine this w/ a group by and potentially a CTE, and you /might/
get what you're looking for. Plus its ANSI to boot.

PS - I'm from an Informix background also. I happen to think its a
very useful database engine.

hth

Allen Jantzen

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
>

Re: Can anybody help? Is there an undocumented unique record key in SQL Server?

am 16.10.2007 21:10:34 von Gert-Jan Strik

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

Re: Can anybody help? Is there an undocumented unique record key in SQL Server?

am 16.10.2007 23:12:01 von Erland Sommarskog

Tim (tim_rogers01@hotmail.com) writes:
> 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.

No, there is no such internal key exposed. Gert-Jan described the keys
that SQL Server uses internally, but you cannot access rhe RID (in heaps)
or the uniquifier (in clustered indexes) in queries.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Can anybody help? Is there an undocumented unique record key in SQL Server?

am 16.10.2007 23:20:20 von dportas

On 16 Oct, 13:32, 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

Depending on requirements there are a few ways you can de-duplicate
data. Google in microsoft.public.sqlserver.programming.

Despite what you say, it seems that an "undocumented feature" of your
application was not the cause of the problem. The cause was that you
have a table without a key. Adding a key constraint should be your
priority. IDENTITY won't help because it won't stop your application
inserting duplicate data.

--
David Portas