Solved

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

Posted on 2009-07-10
5
208 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Group by correlation 4 55
SQL Trigger or Function that updates table with old values 5 39
SQL Agent Timeout 5 48
Delete from table 6 46
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 …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now