SQL Server Performance

IDENTITY column in VIEW

Discussion in 'General DBA Questions' started by surendrakalekar, Jun 23, 2005.

  1. surendrakalekar New Member

    Is it possible to add IDENTITY column in View?
    or any other way to add auto generated number while creating.


    Surendra Kalekar

  2. ranjitjain New Member

    I feel creating identity column in view is not possible.
    but u can add identity column from source table itself.
    so add that column in the table and then select in view.
  3. surendrakalekar New Member

    quote:Originally posted by ranjitjain

    I feel creating identity column in view is not possible.
    but u can add identity column from source table itself.
    so add that column in the table and then select in view.
    Thanks Rajat
    We have already Identity column in source table. In view Identity column (source table) will be there but we are sorting on different column and filtering rows from the source table. So the Identity column will not be in a proper order which needed and that is why we want to create one additionl Identity column in view.


    Surendra Kalekar

  4. Madhivanan Moderator

    quote:Originally posted by surendrakalekar

    Is it possible to add IDENTITY column in View?
    or any other way to add auto generated number while creating.


    Surendra Kalekar

    One possible way is



    select identity(int,1,1) as IdCol ,* into idview from yourTable

    create view viewname
    as Select * from idview
    Drop table idview



    Madhivanan

    Failing to plan is Planning to fail
  5. FrankKalis Moderator

    When you use this SELECT INTO... in a procedure, you'll have to deal with recompiles. Better create the table explicitely first with an IDENTITY column and then do INSERT INTO ... SELECT.

    This will definitely get a lot easier with the next version and the introduction of ROW_NUMBER(). For now, I would do this at the client, where it actually belongs.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  6. Madhivanan Moderator

    Yes That will reduce lock problem


    Madhivanan

    Failing to plan is Planning to fail
  7. FrankKalis Moderator

  8. Madhivanan Moderator

    quote:Originally posted by FrankKalis

    What time is there right now in India?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

    Now it is 1:40:38
    Any reason Frank?


    Madhivanan

    Failing to plan is Planning to fail
  9. FrankKalis Moderator

    No, I was just wondering, that it must be pretty late in your place [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  10. ranjitjain New Member

    what time is there in germany??????
  11. FrankKalis Moderator

    11:42 AM. So, just about time to go to lunch [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  12. surendrakalekar New Member

    quote:Originally posted by FrankKalis

    When you use this SELECT INTO... in a procedure, you'll have to deal with recompiles. Better create the table explicitely first with an IDENTITY column and then do INSERT INTO ... SELECT.

    This will definitely get a lot easier with the next version and the introduction of ROW_NUMBER(). For now, I would do this at the client, where it actually belongs.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

    Thanks Frank and Madhvian, Select Into.. works well.


    Surendra Kalekar

Share This Page