Solved

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

Posted on 2009-07-10
5
214 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

623 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