Unique ID for a View

Question

Post reply

Unique ID for a View

Bill Maners

Ten Centuries

Points: 1340
More actions
September 27, 2007 at 10:02 am

#118856

Hi,
I have a view which has no unique ID but I need one. The data does not present any data that can be used for a unique id.
Any ideas?
Thanks for the help.
Bill

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 1

You can generate a row number as shown here: http://www.sqlteam.com/article/returning-a-row-number-in-a-query

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Bill Maners Ten Centuries Points: 1340 More actions · Answer 2

Bill Maners

Ten Centuries

Points: 1340

September 27, 2007 at 10:37 am

#736851

Thanks.

I appreciate the help.

Ilkka Vaahtoranta SSC Journeyman Points: 90 More actions · Answer 3

Hello,

Try using the CHECKSUM function. there is no dublicate rows in your table and you use all fields as parameters, you should receive unique codes for each row. Works for me.

Ilkka

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

'should' is the keyword when talking about checksum functions.

You must be aware that CHECKSUM() doesn't guarantee 100% to always generate a different output.

There is a chance to two different inputs may produce the same checksum output.

This needs to be taken into account when using it.

/KEnneth

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

In that article, the part that would work for a view looks like this...

SELECT emp_id, lname, fname, job_id,

(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id AND e2.job_id = 10) AS rownumber

FROM employee e

WHERE job_id = 10

ORDER BY emp_id

Before you get all excited about what a great find that is, consider the following about triangular joins in sub-queries, which is what that code is... the response on that thread that I'm talking about starts with...

"Hi there, Koji,

Got your private message and thought I'd post my reply here so that others can see it, as well..."

http://www.sqlservercentral.com/Forums/Topic359124-338-1.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bill Maners Ten Centuries Points: 1340 More actions · Answer 6

Thank you all for the replies.

Situation resolved.

I was able combine two int fields cast as varchar then cast again as int to use as the unique id.

Bill

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Hold on.... let me get my pen and write this one down in my "Why I don't let developers design views" book 😀

Why do you need a unique ID on rows in a view when you have two perfectly good INTs (ID's I presume) waiting in the wings?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bill Maners Ten Centuries Points: 1340 More actions · Answer 8

Thanks for your followup.

I am using an application: Aprimo Marketing

I contains a feature to store 'touches' for a customer (email, web form, etc.) That feature creates a custom table with columns that store particulars about the touch.

Another approach is to retrieve the 'touch' information from another source (dw, web db, etc.) by defining a view with that information.

When identifying the view, I must select THE primary key.

My first view did not have one, there was was not one available, so I posted. Later because of a suggestion, I used two fields to create one.

Person ID + Email ID for instance, would create UNIQUE ID

Thanks, again,

Bill

nigelrivett SSCertifiable Points: 5472 More actions · Answer 9

Glad you've solved the problem.

Be careful about any code that tries to generate a row number using non-unique data. It probably means that different calls could generate different values for the same row.

If you need a unique value and don't have one it probably means that you should persist the alue in the database or it will cause problems in the future with people making assumptions about it.

Cursors never.
DTS - only when needed and never to control.