January 5, 2018 at 11:31 am
I have this vendor system I have to work with...it is perhaps the most horrible system ever made 😉
Anyway, it uses composite keys that are up to 5 columns wide. (Which...should be a federal offense).
I was wondering about adding a calculated column and persisting it to a computed column into each of the tables and storing the composite key and adding an index to it.
Are there any downsides to that?
Obviously, it COULD break the vendor system if it did a SELECT * from the table ever, but assuming it only referenced its tables explicitly with column names.... no queries against the database would be affected in theory?
Has anyone ever done this? Is this just an AWFUL can of worms I am opening?
I could materialize a view and do it there, but we have so many reporting tools that hit those tables and it would be much easier to just add the column to the tables rather than create views of all the tables?
Thoughts?
Am I just being stupid and I should make a bunch of views?
January 5, 2018 at 11:44 am
What is the exact problem you're trying to solve?
January 5, 2018 at 11:52 am
Replace all composite keys with a single column key for reporting purposes.
We run lots of Ad Hoc reports against the tables and end users having to join on all five keys is causing us endless issues.
I want to join on a single column between tables.
January 8, 2018 at 3:31 am
This was removed by the editor as SPAM
January 8, 2018 at 4:13 am
Maxer - Friday, January 5, 2018 11:52 AMReplace all composite keys with a single column key for reporting purposes.We run lots of Ad Hoc reports against the tables and end users having to join on all five keys is causing us endless issues.
I want to join on a single column between tables.
What issues is it causing? A bit more typing, for sure, but anything beyond that? Sounds like you have end users writing queries - that's often creates its own issues. Anyone who's competent in query-writing won't struggle with composite keys.
John
January 8, 2018 at 9:50 am
Create views to do the joins. Yes, users shouldn't be expected to correctly write 5-column joins. And they never should -- they should use a view that does the join(s) for them instead.
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".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply