How to Generate a common unique column for multiple views

Question

How to Generate a common unique column for multiple views

Naidu PK

SSCrazy

Points: 2481
More actions
August 31, 2018 at 2:36 pm

#376840

I created 3 different views and I need a common column to join these 3 views. There is an AMOUNT column in each view and the amount values are related to 5 columns (not nulls). This is the case with all 3 views. Since there is no common column across the 3 views, I created a column (named as CommonUniqueCol) in each view that is concatenation of 5 columns. These 5 columns are in all 3 views and creating CommonUniqueCol is simple. Joining the data based on CommonUniqueCol is now easy.
However, When I checked in my SQL, I got code review comments to avoid CommonUniqueCol and find some other alternative.
1. Are there other ways to generate CommonUniqueCol for all 3 views ?
2. Does my earlier approach has performance issues on tables with millions of rows?
Thanks,
Naveen.
Every thought is a cause and every condition an effect

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

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

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

Naidu PK - Friday, August 31, 2018 2:36 PM
I created 3 different views and I need a common column to join these 3 views. There is an AMOUNT column in each view and the amount values are related to 5 columns (not nulls). This is the case with all 3 views. Since there is no common column across the 3 views, I created a column (named as CommonUniqueCol) in each view that is concatenation of 5 columns. These 5 columns are in all 3 views and creating CommonUniqueCol is simple. Joining the data based on CommonUniqueCol is now easy.
However, When I checked in my SQL, I got code review comments to avoid CommonUniqueCol and find some other alternative.
1. Are there other ways to generate CommonUniqueCol for all 3 views ?
2. Does my earlier approach has performance issues on tables with millions of rows?

I guess my first question would be.... did the person doing the code review offer any suggestions? Or is this some bloody automated code review software?

Getting back to your problem... If you have 5 columns that you were able to concatenate together to make a "common column", why wouldn't you just do multi-column joins between the views? In that light, there's no need for a calculated common column, which would actually cause all 3 views to fully materialize before even 1 row was returned.

As for using views like this, there are some huge caveats for performance. For example, if someone reads from a view and uses criteria against any calculated column (especially aggregate columns), the whole view must materialized before any return would occur. Views are necessarily also "one big honkin' query" and it's incredibly difficult to enjoy the benefits of such techniques as "Divide'n'Conquer" and physical pre-aggregation, etc, etc.

--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)

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

Naidu PK - Friday, August 31, 2018 2:36 PM

>> I created 3 different views and I need a common column to join these 3 views. <<

That's very nice, but we have no idea what those views look like nor what the base tables from which they were built look like. Why did you fail to post any kind of DDL at all? How do we read your mind?

>> There is an AMOUNT column in each view and the amount values are related to 5 columns (not nulls). <<

What does the amount mean? Is it dollars? soybeans? This is what ISO 11179 calls an attribute property. It is too vague to be used as a column name. In which five columns. Is it related to? If only you had posted DDL, we might be able to figure it out!

>> Since there is no common column across the 3 views, I created a column (named as CommonUniqueCol) in each view that is concatenation of 5 columns. These 5 columns are in all 3 views and creating CommonUniqueCol is simple. <<

You don't create a column. A column is an attribute of the entity modeled by the table. And exist by the nature of the thing you are modeling. But even more incorrect than this is that you don't know what First Normal Form (1NF) is! Since each column represents a distinct, independent attribute, there concatenation has absolutely no meaning in any valid data model, what property is represented by putting shoe size, hat size, eye color, birthdate and religion into one attribute?

The name of an attribute should never mix data and metadata, in violation of the most fundamental concepts of data modeling. You've never in a single book on data modeling, have you? That's why your insane "CommonUniqueCol" is absurd

>> Joining the data based on CommonUniqueCol is now easy. <<

If we had the DDL, and could see what the views look like, then we could fix this.

>> However, When I checked in my SQL, I got code review comments to avoid CommonUniqueCol and find some other alternative. <<

How can we possibly know without the DDL?

>> 1. Are there other ways to generate CommonUniqueCol for all 3 views ? <<

All there's always lots and lots of ways to do something stupid. But why don't you try do something right instead?

>>2. Does my earlier approach has performance issues on tables with millions of rows? <<

Very Probably 🙂

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

Naidu PK SSCrazy Points: 2481 More actions · Answer 3

Jeff Moden - Saturday, September 1, 2018 8:14 PM
Naidu PK - Friday, August 31, 2018 2:36 PM
I created 3 different views and I need a common column to join these 3 views. There is an AMOUNT column in each view and the amount values are related to 5 columns (not nulls). This is the case with all 3 views. Since there is no common column across the 3 views, I created a column (named as CommonUniqueCol) in each view that is concatenation of 5 columns. These 5 columns are in all 3 views and creating CommonUniqueCol is simple. Joining the data based on CommonUniqueCol is now easy.
However, When I checked in my SQL, I got code review comments to avoid CommonUniqueCol and find some other alternative.
1. Are there other ways to generate CommonUniqueCol for all 3 views ?
2. Does my earlier approach has performance issues on tables with millions of rows?
I guess my first question would be.... did the person doing the code review offer any suggestions? Or is this some bloody automated code review software?
Getting back to your problem... If you have 5 columns that you were able to concatenate together to make a "common column", why wouldn't you just do multi-column joins between the views? In that light, there's no need for a calculated common column, which would actually cause all 3 views to fully materialize before even 1 row was returned.
As for using views like this, there are some huge caveats for performance. For example, if someone reads from a view and uses criteria against any calculated column (especially aggregate columns), the whole view must materialized before any return would occur. Views are necessarily also "one big honkin' query" and it's incredibly difficult to enjoy the benefits of such techniques as "Divide'n'Conquer" and physical pre-aggregation, etc, etc.

no code review suggestions offered. But I think joining on the multi-column is good thing to try. will let you know after trying.

Thanks,
Naveen.
Every thought is a cause and every condition an effect

Naidu PK SSCrazy Points: 2481 More actions · Answer 4

jcelko212 32090 - Sunday, September 2, 2018 9:53 AM