469,167 Members | 1,168 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

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 14682
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 zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.