Is there a system generated row id in SQL Server 2000?

Hi,
I wanted to perform a select and a delete of a row based on a system generat
ed row id (like what Oracle has). However after researching this issue I've
found that there is no equivalent of Oracle Rowid in SQL Server.
I know I can generate my own row numbers using identity columns and then pro
cess the results but I was curious to know whether or not SQL Server actuall
y had a system generated row id. My thinking was that each row has to have a
row id because otherwise h
ow will b-tree indexes work (especially on non unique columns)?
Documentation doesn't really give out much information on what the Row struc
ture looks like in SQL Server (or at least I haven't been able to find it) n
ot have I found information on exactly what does a row pointer looks like in
a B-Tree index leaf page.
Can anyone suggest a source of information on the above? Also, if there is a
system generated row id in SQL Server, is any way at all of accessing it?hi;
am not a SQL expert here, but i think there is a datatype call IDENTITY that
auto generate an sequencing row number. that cld be what u need.
amnyone can verify ?
"SJ" <anonymous@.discussions.microsoft.com> wrote in message
news:1196BF60-EF0E-4E47-853F-18E79C95279E@.microsoft.com...
> Hi,
> I wanted to perform a select and a delete of a row based on a system
generated row id (like what Oracle has). However after researching this
issue I've found that there is no equivalent of Oracle Rowid in SQL Server.
> I know I can generate my own row numbers using identity columns and then
process the results but I was curious to know whether or not SQL Server
actually had a system generated row id. My thinking was that each row has to
have a row id because otherwise how will b-tree indexes work (especially on
non unique columns)?
> Documentation doesn't really give out much information on what the Row
structure looks like in SQL Server (or at least I haven't been able to find
it) not have I found information on exactly what does a row pointer looks
like in a B-Tree index leaf page.
> Can anyone suggest a source of information on the above? Also, if there is
a system generated row id in SQL Server, is any way at all of accessing it?|||You will have to generate your own rowid... SQL does not expose one ( as
some other DBMS's do.)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SJ" <anonymous@.discussions.microsoft.com> wrote in message
news:1196BF60-EF0E-4E47-853F-18E79C95279E@.microsoft.com...
> Hi,
> I wanted to perform a select and a delete of a row based on a system
generated row id (like what Oracle has). However after researching this
issue I've found that there is no equivalent of Oracle Rowid in SQL Server.
> I know I can generate my own row numbers using identity columns and then
process the results but I was curious to know whether or not SQL Server
actually had a system generated row id. My thinking was that each row has to
have a row id because otherwise how will b-tree indexes work (especially on
non unique columns)?
> Documentation doesn't really give out much information on what the Row
structure looks like in SQL Server (or at least I haven't been able to find
it) not have I found information on exactly what does a row pointer looks
like in a B-Tree index leaf page.
> Can anyone suggest a source of information on the above? Also, if there is
a system generated row id in SQL Server, is any way at all of accessing it?|||Yes I think the keywords here are "sql does not expose one". I have been sus
pecting that although there may be a system generated row id, it wouldn't be
accesible to external users.|||To the best of my knowledge there really isn't one.
I don't worry about internal page strcutures all that much, so I could be
wrong. I'm sure Kalen's book Inside SQL Server 2000 would have a lot of
great info on this topic if you're really interested.
But to the best of my knowledge, internal id's are based on both page number
and row offset (or a row number) on that specific page. I do not believe
there is an internal ID that is unique across a table space. The short
answer is that it doesn't matter since there definitely isn't one exposed.
But I don't think one even exists...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"SJ" <anonymous@.discussions.microsoft.com> wrote in message
news:56E22894-9ACE-4654-B93B-7E375D21A367@.microsoft.com...
> Yes I think the keywords here are "sql does not expose one". I have been
suspecting that although there may be a system generated row id, it wouldn't
be accesible to external users.|||Brian is correct - there is no exposed unique RID for a row.
There are two types of RIDs we use internally, physical and logical.
Physical RIDs are F:P:S, where F is the file ID, P is the page number in the
file and S is the slot number on the page. These are only used for locating
heap rows from non-clustered indexes over heaps (i.e. each row in a
non-clustered index over a heap contains the physical RID of the
corresponding row in the heap itself). Physical RIDs are not exposed,
although you will see them sometimes in DBCC CHECK* error messages.
Logical RIDs are the keys of the index the row belongs too. Each row in a
non-clustered index has a logical RID. Each row in a non-clustered index
over a clustered index also contains the logical RID of the corresponding
row in the clustered index).
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:eSCl$8zEEHA.2404@.TK2MSFTNGP11.phx.gbl...
> To the best of my knowledge there really isn't one.
> I don't worry about internal page strcutures all that much, so I could be
> wrong. I'm sure Kalen's book Inside SQL Server 2000 would have a lot of
> great info on this topic if you're really interested.
> But to the best of my knowledge, internal id's are based on both page
number
> and row offset (or a row number) on that specific page. I do not believe
> there is an internal ID that is unique across a table space. The short
> answer is that it doesn't matter since there definitely isn't one exposed.
> But I don't think one even exists...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "SJ" <anonymous@.discussions.microsoft.com> wrote in message
> news:56E22894-9ACE-4654-B93B-7E375D21A367@.microsoft.com...
> suspecting that although there may be a system generated row id, it
wouldn't
> be accesible to external users.
>|||Thank you all for you responses.|||The only thing I would add is the reason that SQL Server doesn't expose row
ids. Systems that expose row ids require more offline and manual
maintenance than systems that don't expose row ids. That's because systems
that expose row ids can't just move rows around without breaking
applications. Systems that don't expose row ids can dynamically reorganize
data at any time since no user will ever request the data by rowid.
First generation systems such as Rdb and Oracle exposed rowids, second
generation systems such as Tandem and Sybase saw the errors in this and did
not expose them. Of course once exposed it's almost impossible to take away
the feature, so Oracle still has it.
Hal Berenson, SQL Server MVP
VP, Yukon Readiness
Scalability Experts, Inc.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uyL9fz3EEHA.1456@.TK2MSFTNGP09.phx.gbl...
> Brian is correct - there is no exposed unique RID for a row.
> There are two types of RIDs we use internally, physical and logical.
> Physical RIDs are F:P:S, where F is the file ID, P is the page number in
the
> file and S is the slot number on the page. These are only used for
locating
> heap rows from non-clustered indexes over heaps (i.e. each row in a
> non-clustered index over a heap contains the physical RID of the
> corresponding row in the heap itself). Physical RIDs are not exposed,
> although you will see them sometimes in DBCC CHECK* error messages.
> Logical RIDs are the keys of the index the row belongs too. Each row in a
> non-clustered index has a logical RID. Each row in a non-clustered index
> over a clustered index also contains the logical RID of the corresponding
> row in the clustered index).
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brian Moran" <brian@.solidqualitylearning.com> wrote in message
> news:eSCl$8zEEHA.2404@.TK2MSFTNGP11.phx.gbl...
be
> number
exposed.
been
> wouldn't
>