SQL Server Performance Forum – Threads Archive

IDENTITY column in VIEW

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

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.

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

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

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)

Yes That will reduce lock problem
Madhivanan Failing to plan is Planning to fail

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)

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

No, I was just wondering, that it must be pretty late in your place [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

what time is there in germany??????

11:42 AM. So, just about time to go to lunch [<img src=’/community/emoticons/emotion-1.gif’ alt=’

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

]]>