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

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

ala:
SELECT ROW_NUMBER() OVER (ORDER BY <some column>) AS ROW,
col,col,col,col FROM <table>

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:

Tim wrote:
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

Tim (tim_rogers01 (AT) hotmail (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On 16 Oct, 13:32, Tim <tim_roger... (AT) hotmail (DOT) com> wrote:
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