Results 1 to 15 of 15
  1. #1
    Join Date
    May 2002
    Location
    Joinville - SC - Brasil
    Posts
    3

    Question Unanswered: 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.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's a mythical creature that lives in the land of Oracle...

    No it doesn't exists

    What do you want to do?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    See this link http://www.craigsmullins.com/ssu_0599.htm for information on similar approach.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    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.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Mar 2004
    Location
    Germany
    Posts
    11
    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/supp...NoWebContent=1

  6. #6
    Join Date
    May 2002
    Location
    Joinville - SC - Brasil
    Posts
    3

    Talking

    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.

  7. #7
    Join Date
    Mar 2004
    Location
    Germany
    Posts
    11
    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?

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Mar 2004
    Location
    Germany
    Posts
    11
    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?

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    They're back up....

    and it's...

    Code:
    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....
    Last edited by Brett Kaiser; 03-19-04 at 12:56.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Mar 2004
    Location
    Germany
    Posts
    11
    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--

  13. #13
    Join Date
    Sep 2003
    Location
    Los Angeles
    Posts
    103
    Check this: Intelligent Versus Surrogate Keys

    http://www.bcarter.com/intsurr1.htm

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Mar 2004
    Location
    Germany
    Posts
    11
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •