Post reply

Adding indexed computed column to vendor system-any negative impacts?

  • January 5, 2018 at 11:31 am

    #342282

    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

    #1974738

    What is the exact problem you're trying to solve?

  • January 5, 2018 at 11:52 am

    #1974742

    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

    #1974872

    Maxer - Friday, January 5, 2018 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.

    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

    #1974927

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