Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-07-10
5
Medium Priority
?
220 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 2000 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
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…

730 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