Posted by Erland Sommarskog on 12/28/05 00:45

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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]