473,320 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 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 15090
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Pjotr Wedersteers | last post by:
I have a table with quiz questions. Each question has a unique ID, based on an auto_increment field. Now I've discovered that with deleting rows from the table the deleted ID's are not reissued....
2
by: hjyn | last post by:
Hi, how can I add char to auto_increment? For example, the auto_increment is for integer, I want to add a character at the front of that number. Ex: >create table abs (UserID INT(3) NOT NULL...
1
by: Jonathan | last post by:
I have belatedly discovered an odd problem. I created a new memo field called "IntellectualAccessNotes" in a replicated table . I put it into a form and people have been entering data into it and...
0
by: Diego | last post by:
Hi all, I want to show in a grid view a list of Items with the possibility of modify them, I don't want to show the item code because is just an autoincremental integer, The Grid View is linked to...
2
by: Jasmine | last post by:
Hi, how can I set up the javascript for the button field in the grid view, so that when the user click the button, a pop up window will be opened, I mean where could I add the "window.open "url"...
2
by: NowItsWhatever | last post by:
In query DESIGN view, how do I automatically "fit" the columns in the table/field grid to the lengths of the table and field names (including any functions applied to the fields). I am not talking...
2
by: millisami | last post by:
I have a table with a primary varchar key and some others columns. I want to add a new column at the begining of the table which is Numeric auto_increment. When I add this new column via phpMyAdmin,...
3
by: ozzii | last post by:
Hello, Can autonumber be reused if the record to which it was assigned is deleted? I have a database table in which autonumber is used to uniquely identify records. However this table is appended...
2
by: kevinfbutt | last post by:
Hi All! I have an SQL Server View where my field in the view is an integer field and works well. When I link to it from Access, the field is defined as a text field in Access. Does anyone...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.