Adding a surrogate key to a view

Question

Adding a surrogate key to a view

Guru1997

SSC Enthusiast

Points: 179
More actions
July 12, 2021 at 1:57 pm

#3906995

Hi there
I need to add a surrogate key/unique identifier column to an exisiting view.
I have tried using "Select ROW_NUMBER() OVER (ORDER BY CustomerId) as SurrogateKey From ..." as well as "Select CONCAT(CustomerId, ActivityId, CreateDate) As SurrogateKey From ..." as an alternative but both of these result in 152 rows being added to my view.
What mistakes am I making with my code?
Thanks so much in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

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

ScottPletcher SSC Guru Points: 101117 More actions · Answer 1

Don't know, since we can't see the code. Just adding ROW_NUMBER() to a query should not increase the number of rows.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 2

I don't understand how adding a column can add rows.

Can you show us the code?

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 3

If you want us to help you with your code, you probably ought to show it to us. Just offhand, my guess would be that something is wrong with your view. The base tables, which we know nothing about, should have keys. These keys should be exposed in some way in your view. Your use of a row number. Sounds like all you're doing is turning a relational table into a sequential file list because you don't know how to construct a key. Would you like to try again?

Please post DDL and follow ANSI/ISO standards when asking for help.

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 4

As other wrote without the code there is no way to help you.

Also I agree with Jcelko212 that the keys should be taken from the table and not be computed in the view, but I assume that you just misused the term key and you meant row number (which is not used as a key).

Adding a running number can add records if before the column addition there were duplications that were shown only once but after the column addition they stopped being duplications. Here is an example:

select distinct object_id from sys.columns order by object_id
select distinct row_number() over (order by object_id) as RowNum, object_id from sys.columns order by object_id

Adi

This reply was modified 3 years, 5 months ago by Adi Cohn.

ScottPletcher SSC Guru Points: 101117 More actions · Answer 5

Adi Cohn wrote:

Adding a running number can add records if before the column addition there were duplications that were shown only once but after the column addition they stopped being duplications. Here is an example:
select distinct object_id from sys.columns order by object_id
select distinct row_number() over (order by object_id) as RowNum, object_id from sys.columns order by object_id
Adi

In that case, you need to wait to add the row number until after the DISTINCT has been applied:

select *, row_number() over( order by object_id ) as row_num from ( select distinct object_id from sys.columns ) as query1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".