SEO

#1 (**permalink**) 02-29-2008, 02:19 PM

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

#2 (**permalink**) 02-29-2008, 02:19 PM

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" <cmourik@hotmail.com> wrote in message
news:1135691191.311510.143250@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
>

#3 (**permalink**) 02-29-2008, 02:19 PM

@@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 tom@cips.ca
www.pinpub.com

"cmm" <cmourik@hotmail.com> wrote in message
news:1135691191.311510.143250@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

#4 (**permalink**) 02-29-2008, 02:20 PM

cmm (cmourik@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, esquel@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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

view with auto_increment field