July 12, 2021 at 1:57 pm
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.
July 12, 2021 at 2:07 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
July 12, 2021 at 4:09 pm
I don't understand how adding a column can add rows.
Can you show us the code?
July 19, 2021 at 4:10 pm
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.
July 20, 2021 at 7:11 am
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
July 20, 2021 at 3:06 pm
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_idAdi
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply