Solved

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

Posted on 2009-07-10
5
207 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sql query 12 65
insert query with value having 's 2 35
Isolation level in SQL server 3 43
Passing value to a stored procedure 8 66
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…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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

12 Experts available now in Live!

Get 1:1 Help Now