Professional Web Applications Themes

Changing field to IDENTITY in TSQL - Microsoft SQL / MS SQL Server

I've been trying to get the following script to work for a couple of days and I am not sure what I'm doing wrong. Can somebody please correct what I'm doing. I'm trying to change an existing field to an IDENTITY field, but I'm getting syntax errors. It's got to be simple, but somewhere I'm missing something. Thanks in advance, Robert create table tbltest (fldID int, fldname nvarchar(64)) go alter table tbltest alter column fldID int identity (1,1) *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...

  1. #1

    Default Changing field to IDENTITY in TSQL

    I've been trying to get the following script to work for a couple of
    days and I am not sure what I'm doing wrong. Can somebody please
    correct what I'm doing. I'm trying to change an existing field to an
    IDENTITY field, but I'm getting syntax errors. It's got to be simple,
    but somewhere I'm missing something.

    Thanks in advance,

    Robert


    create table tbltest
    (fldID int, fldname nvarchar(64))
    go
    alter table tbltest
    alter column fldID int identity (1,1)



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Robert Guest

  2. #2

    Default Re: Changing field to IDENTITY in TSQL


    You can't add/remove the IDENTITY
    column property using an ALTER TABLE statement.
    Use Enterprise manger to do that. OR you will have to drop the column and
    recreate it with the identity property.

    --
    -Vishal
    "Robert Taylor" <com> wrote in message
    news:phx.gbl... 


    Vishal Guest

  3. #3

    Default Re: Changing field to IDENTITY in TSQL

    >> 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
    sequentially 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--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. Replies: 1
    Last Post: February 25th, 01:16 PM
  2. Recordset doesn't return identity field
    By A_X_L_V in forum ASP Components
    Replies: 7
    Last Post: October 21st, 02:48 PM
  3. Create Identity Field
    By Stephen in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 23rd, 08:51 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139