Solved

How to create a unique ID for a view on a table I can't alter

Posted on 2009-07-10
5
209 Views
Last Modified: 2012-05-07
Hello,

I am working with a database that I cannot alter. I'm using a view that I created in another database on the same server to query data from it. Within the view one of the  tables has two fields that could act as a composite key, but no constraint is defined in the actual table.

I need to have a unique ID available for the application that I am using the view with. It is my understanding that within the view I am unable to assign a constraint.

Does anybody know of a way in which I can create a unique ID to use within the application that doesn't require making a change to the database I'm not allowed to alter?
0
Comment
Question by:nbotts
  • 3
  • 2
5 Comments
 
LVL 17

Expert Comment

by:pssandhu
ID: 24827403
You can do something like this:
CREATE VIEW MyView AS
Select newID() as uniqueID, * From MyTable
Please note the UniqueID will always keep changing with evert time you run the select statement.
P.
0
 

Author Comment

by:nbotts
ID: 24827432
So wait, if let's say the two fields that would make up the unique ID are fieldA and fieldB.

Would it be:

CREATE VIEW MyView AS

Select newID(fieldA, fieldB) as uniqueID, * From MyTable
0
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
ID: 24827464
Nope. NEWID() is a function that generates and random key. Noting goes in between the brackets. The syntax is as I posted.
Here is an example you can test:  
Select NewID() as ID
Run this command over and over and each time you will get a randomly generated key.
P.
0
 

Author Closing Comment

by:nbotts
ID: 31602294
Thank you, that worked and is a bit of an eye opener to me.

Just to add to the general knowledge base, while that did create a unique ID exactly as I wanted, it didn't end up working because my app said that it had to be numeric as well. With the help of this example though I was able to find this syntax that did the job:

ROW_NUMBER() OVER (ORDER BY dbo.table.id) AS uniqueID

I'm sure if I had added the numeric requirement you would have led me to a similar statement.

Greatly appreciated, Nathan
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24827754
No problem. I am glad I could help. And yes, that is good solution you are ended up with if you wanted all numeric keys. In SQL 2005 they added this new functionality (Row_Number and Over()) and frankly this is a life saver in so manu cases.
Cheers!!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Testing connection to sql 7 59
Truncate vs Delete 63 105
How can I exclude some wording in a like statement? 39 75
MS SQLK Server multi-part identifier cannot be bound 5 35
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question