473,390 Members | 1,335 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,390 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 15098
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: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

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.