471,572 Members | 867 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,572 software developers and data experts.

view with auto_increment field

cmm
I am trying to create a view in MSSQL that has a column which is an
auto increment (or unique id) field but it doesn't work the way I want
it. Does anybody have an idea how to do this? The example below gives
me only 0 in the id field.

CREATE VIEW dbo.tree_x_categories
AS
SELECT @@ROWCOUNTas id, *
FROM <dbname>.dbo.tree_x_categories
GO

What I want is the fields from the table tree_x_categories plus an
additional field that has a unique identifier.

Many thnx,

Chris

Dec 27 '05 #1
3 14913
Hi

I am not sure what you are trying to attain as using something that is not
persisted will give you different results each time you query it e.g.

CREATE VIEW MYOBJECTS AS
SELECT NEWID() AS guid, * FROM SYSOBJECTS

SELECT * FROM MYOBJECTS

SELECT * FROM MYOBJECTS

If you want to rank the items then you can use a subquery e.g.

CREATE VIEW MyRanking AS

SELECT (SELECT COUNT(*) FROM SYSOBJECTS S WHERE S.ID <= O.ID) AS Rank, O.*
FROM SYSOBJECTS O

This would require the table you are using to have a method of uniquely
identifying each row.

John

"cmm" <cm*****@hotmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
I am trying to create a view in MSSQL that has a column which is an
auto increment (or unique id) field but it doesn't work the way I want
it. Does anybody have an idea how to do this? The example below gives
me only 0 in the id field.

CREATE VIEW dbo.tree_x_categories
AS
SELECT @@ROWCOUNTas id, *
FROM <dbname>.dbo.tree_x_categories
GO

What I want is the fields from the table tree_x_categories plus an
additional field that has a unique identifier.

Many thnx,

Chris

Dec 27 '05 #2
@@ROWCOUNT is inappropriate here. Is it that you want a sequential number
or one that is truly unique?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada to*@cips.ca
www.pinpub.com

"cmm" <cm*****@hotmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
I am trying to create a view in MSSQL that has a column which is an
auto increment (or unique id) field but it doesn't work the way I want
it. Does anybody have an idea how to do this? The example below gives
me only 0 in the id field.

CREATE VIEW dbo.tree_x_categories
AS
SELECT @@ROWCOUNTas id, *
FROM <dbname>.dbo.tree_x_categories
GO

What I want is the fields from the table tree_x_categories plus an
additional field that has a unique identifier.

Many thnx,

Chris
Dec 27 '05 #3
cmm (cm*****@hotmail.com) writes:
I am trying to create a view in MSSQL that has a column which is an
auto increment (or unique id) field but it doesn't work the way I want
it. Does anybody have an idea how to do this? The example below gives
me only 0 in the id field.

CREATE VIEW dbo.tree_x_categories
AS
SELECT @@ROWCOUNTas id, *
FROM <dbname>.dbo.tree_x_categories
GO

What I want is the fields from the table tree_x_categories plus an
additional field that has a unique identifier.


@@rowcount returns the numbers of rows affected by the previous statement.
It's kind of odd thing to put into a view.

If you are on SQL 2005, you can use the row_number() function as in
this example:

SELECT id = ROW_NUMBER() OVER (Order BY CustomerID), *
FROM Northwind..Customers
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 27 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Pjotr Wedersteers | last post: by
2 posts views Thread by hjyn | last post: by
1 post views Thread by Jonathan | last post: by
reply views Thread by Diego | last post: by
2 posts views Thread by Jasmine | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.