Getting the actual row ID.

Hello everybody...
I'm trying to get the Row ID of a result set. Is there a way to do it?
Something like this i think:
select @.@.rowid, column1, column2
from table
Best regards,
Dalcom.It's a mythical creature that lives in the land of Oracle...

No it doesn't exists

What do you want to do?|||See this link http://www.craigsmullins.com/ssu_0599.htm for information on similar approach.|||I just see information about Identity columns. I don't think, that this is helping to get a record counter.

I would try to create a procedure, which is performing the query by a cursor, and to add a record number within a loop.|||Why a cursor? If the only requirement is to produce some nice looking gapless sequence, some solutions are provided here http://support.microsoft.com:80/support/kb/articles/q186/1/33.asp&NoWebContent=1|||Ok.

I'm just trying to get some information about the record on table.
But, if I can't do it, i'll try other thing :)

Thank you a lot!

dalcom.|||Originally posted by dalcom
Ok.

I'm just trying to get some information about the record on table.
But, if I can't do it, i'll try other thing :)

Thank you a lot!

dalcom.

What informations?|||The identity property is a very powerful and useful, yet under-utilized feature of Microsoft SQL Server.

Give me a break...under-utilized...

Surrogate keys...ho|||Originally posted by Brett Kaiser
Give me a break...under-utilized...

Surrogate keys...ho

Has someone deleted his posts or where do you read this, Brett?|||Originally posted by Frank Kalis
Has someone deleted his posts or where do you read this, Brett?

No, it's in here..

http://www.craigsmullins.com/ssu_0599.htm

Let's take a poll...

Are IDENTITY Columns under utilized?

Well by me they are...

You know what though, it all depends on how that's quantified I think (and If I did a lot more of that, the better off I'd be...MOO)|||They're back up...

and it's...

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ooops...wrong thread...|||Okay, sit back and relax. Get a cup of coffee and enjoy this answer from the one and only Joe Celko to such a question on the MS newsgroups. I really like it.

>> I'm trying to change an existing field [sic] to an
IDENTITY field [sic], but I'm getting syntax errors. It's got to be
simple, but somewhere I'm missing something. <<

What you are missing is the basic concepts of the relational model.
Columns are not fields; rows are not records; tables are not files. An
IDENTITY property cannot be a key by definition. A key is a subset of
attributes that uniquely define an entity in your data model.

The IDENTITY column is a holdover from the early programming language which were very close to the hardware. For example, the fields in a COBOL or FORTRAN program were assumed to be physically located in main storage in the order they were declared in the program.

The early SQLs were based on existing file systems. The data was kept
in physically contiguous disk pages, in physically contiguous rows, made
up of physically contiguous columns. In short, just like a deck of
punch cards or a magnetic tape.

But physically contiguous storage is only one way of building a
relational database and it is not always the best one. But aside from
that, the whole idea of a relational database is that user is not
supposed to know how things are stored at all, much less write code that
depends on the particular physical representation in a particular
release of a particular product.

One of the biggest errors is the IDENTITY column (actually property) in
the Sybase family (SQL Server and Sybase). People actually program with this "feature" and even use it as the primary key for the table! Now, let's go into painful details as to why this thing is bad.

The practical considerations are that IDENTITY is proprietary and
non-portable, so you know that you will have maintenance problems when you change releases or port your system to other products.

But let's look at the logical problems. First try to create a table
with two columns and try to make them both IDENTITY. If you cannot
declare more than one column to be of a certain datatype, then that
thing is not a datatype at all, by definition. It is a property which
belongs to the PHYSICAL table, not the data in the table.

Next, create a table with one column and make it an IDENTITY. Now try
to insert, update and delete different numbers from it. If you cannot
insert, update and delete rows from a table, then it is not a table by
definition.

Finally create a simple table with one IDENTITY and a few other columns.
Use a few statements like

INSERT INTO Foobar (a, b, c) VALUES ('a1', 'b1', 'c1');
INSERT INTO Foobar (a, b, c) VALUES ('a2', 'b2', 'c2');
INSERT INTO Foobar (a, b, c) VALUES ('a3', 'b3', 'c3');

to put a few rows into the table and notice that the IDENTITY equentially numbered them in the order they were presented. If you delete a row, the gap in the sequence is not filled in and the sequence continues from the highest number that has ever been used in that column in that particular table.

But now use a statement with a query expression in it, like this:

INSERT INTO Foobar (a, b, c)
SELECT x, y, z
FROM Floob;

Since a query result is a table, and a table is a set which has no
ordering, what should the IDENTITY numbers be? The entire, whole,
completed set is presented to Foobar all at once, not a row at a time.
There are (n!) ways to number (n) rows, so which one do you pick? The
answer has been to use whatever the physical order of the result set
happened to be. That non-relational phrase "physical order" again.

But it is actually worse than that. If the same query is executed
again, but with new statistics or after an index has been dropped or
added, the new execution plan could bring the result set back in a
different physical order. Can you explain from a logical model why the
same rows in the second query get different IDENTITY numbers? In the
relational model, they should be treated the same if all the values of
all the attributes are identical.

Using IDENTITY as a primary key is a sign that there is no data model,
only an imitation of a sequential file system. Since this number exists
only as a result of the state of particular piece of hardware at a
particular time, how do you verify that an entity has such a number in
the reality you are modeling?

To quote from Dr. Codd: "..Database users may cause the system to
generate or delete a surrogate, but they have no control over its value,
nor is its value ever displayed to them ..."(Dr. Codd in ACM TODS, pp
409-410) and Codd, E. (1979), Extending the database relational model to
capture more meaning. ACM Transactions on Database Systems, 4(4). pp.
397-434. This means that a surogate ought ot act like an index; created
by the user, managed by the system and NEVER seen by a user. That means
never used in queries.

Codd also wrote the following:

"There are three difficulties in employing user-controlled keys as
permanent surrogates for entities.

(1) The actual values of user-controlled keys are determined by users
and must therefore be subject to change by them (e.g. if two companies
merge, the two employee databases might be combined with the result that
some or all of the serial numbers might be changed.).

(2) Two relations may have user-controlled keys defined on distinct
domains (e.g. one uses social security, while the other uses employee
serial numbers) and yet the entities denoted are the same.

(3) It may be necessary to carry information about an entity either
before it has been assigned a user-controlled key value or after it has
ceased tohave one (e.g. and applicant for a job and a retiree).

These difficulties have the important consequence that an equi-join on
common key values may not yield the same result as a join on common
entities. A solution - proposed in part [4] and more fully in [14] - is
to introduce entity domains which contain system-assigned surrogates.
Database users may cause the system to generate or delete a surrogate,
but they have no control over its value, nor is its value ever displayed
to them...." (Codd in ACM TODS, pp 409-410).

References

Codd, E. (1979), Extending the database relational model to capture more
meaning. ACM Transactions on Database Systems, 4(4). pp. 397-434

The most common use that a Newbie makes of IDENTITY is to use it as a
record number (under the error that a record nubmer is a key!), so that
he does not have to think about keys, DRI, check digits, proper data
types, international standards and all that hard stuff.

While this was meant as an abstract example, I also fear that you have
not read ISO-11179 because of the silly, redundant, dangerous prefixes
on your code.

--CELKO--|||Check this: Intelligent Versus Surrogate Keys

http://www.bcarter.com/intsurr1.htm|||did you all notice how swiftly dalcom tried to withdraw from this discussion?

Frank, when you wish to make a reference to something joe celko wrote, please provide the URL rather than such a large excerpt -- for one thing, it's less scrolling, and for another, people can then bookmark celko's writing|||Originally posted by r937
Frank, when you wish to make a reference to something joe celko wrote, please provide the URL rather than such a large excerpt -- for one thing, it's less scrolling, and for another, people can then bookmark celko's writing

I will consider this, but didn't I mention it's a post on the MS newsgroups?