Does anyone use views in this way?

  • Hi,

    I know there are several cases where we as database developers need reference tables to help us with our querying (e.g., Tally tables, Calendar tables, etc.). Occasionally, I find myself needing other types of reference tables, sometimes ones that may not be able to be dynamically generated. But let's ignore that last point for a second. The fact is, I need to store my reference tables somewhere.

    The easiest option, I think, is to create the reference tables within the database where I'm currently working.

    But let's say this is a third-party database that you can't or aren't supposed to create tables in. Another option might be to create a separate DBAUtils or Common database to hold these tables that contain static data.

    Let's say that you don't want to create a new database just for these one or two tables because then the queries become dependent on two databases when all of the raw data I need lives in one of the databases.

    The third-party database allows for views and stored procedures, so I'm wondering--does anyone ever use views to actually store data itself rather than the query to get the data? For example, if I needed to know some chemistry symbols and names for my query, rather than use any of the techniques above, I could store the data I need right within the view and never worry about base tables:


    CREATE VIEW ChemReference AS
    SELECT 'm' AS Symbol, 'meter' AS [Name], 'length' AS Quantity UNION ALL
    SELECT 'g', 'gram', 'mass' UNION ALL
    SELECT 'Pa', 'pascal', 'pressure' UNION ALL
    SELECT 'K', 'kelvin', 'temperature' UNION ALL
    SELECT 'mol', 'mole', 'amount of substance' UNION ALL
    SELECT 'J', 'joule', 'energy, work, quantity of heat' UNION ALL
    SELECT 's', 'second', 'time' UNION ALL
    SELECT 'min', 'minute', 'time' UNION ALL
    SELECT 'h', 'hour', 'time' UNION ALL
    SELECT 'd', 'day', 'time' UNION ALL
    SELECT 'y', 'year', 'time' UNION ALL
    SELECT 'L', 'liter', 'volume' UNION ALL
    SELECT 'ppm', 'parts per million', 'concentration' UNION ALL
    SELECT 'M', 'molarity', 'solution concentration' UNION ALL
    SELECT 'u', 'atomic mass unit', 'atomic mass'

    Then use the view in my query. I know I wouldn't be able index the view because of the UNION ALLs, but it would be one way to get around the rules or not creating a table within the database and not introducing a dependency on another database. And maybe for few enough records like this, the index wouldn't provide much improvement anyway. 

    Thoughts on a static stored view?

    Mike

    Mike Scalise, PMP
    https://www.michaelscalise.com

  • My preference would be to put the additional stuff into another database & live with the dependency.

    I would, however, make sure that this was a one-way dependency (source DB references common DB, but not vice versa).

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • If you want to store data in a vendor's database, do it.  My recommendation would be to create your own schema in their database and put all your objects in there.  And no foreign keys to their objects or you'll break their upgrades.

    An alternative is to put everything in a script and create your objects, then test, then delete all your objects each time you want to test.  Or just create two procedures in the vendor's database to CreateMyObjects and DropMyObjects for easy use.

    Another alternative is to create synonyms in the vendor's database that point to your database objects.

  • Thanks, everyone, for the suggestions. It's always good to see the myriad ways to solve problems in SQL Server.

    Mike Scalise, PMP
    https://www.michaelscalise.com

  • I have used views for that, but only for a very limited number of values, since it can't be effectively indexed.

    Unique indexes are potentially extremely valuable, even for small lookup tables, because it can prevent SQL from having to loop thru every lookup row for every "calling"/joining row.  In other words, keep in mind that the number of rows requiring the lookup can still be quite large even if the lookup table itself is quite small.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • I agree with Bill Talada... it's your server... you should be able to drop a table in wherever you need to.  If it end up on a 3rd party database, then do as Bill suggests and create your own schema on that database for a table with the understanding that you may have to rebuild the table after a vendor upgrade.  Do don't always do an in-place upgrade... sometimes they'll squirrel off the data, drop the database, create a new one, and then inport the data they squirreled away.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Have a word with the vendor and ask if they have a preferred method by which you can add objects for yourself. In my experience some vendors will have a reserved schema or prefix specifically designed for this purpose and these are excluded from any updates that they may issue which involves dropping objects.
    I have generally found vendors to be fairly helpful when it comes to this kind of thing.

    ...

  • +1 for the use your own schema

    Another option if you only need the reference data inside a single sproc would be to use #temp_tables or @in_memory tables.  #tables can be indexed and both will die when the session ends (it makes sense to wrap prefix your #temp creations with a
    begin try
        drop table  #temp
    end try
    begin catch
    end catch

    or
    or if exists (..) syntax

    as it stops create/insert errors in development when the table already exists

    The disadvantage is that the logic and the data are hidden inside the procedure so cannot be maintained without editing the code which may go against dev-ops principles.

  • I've used views like this, but don't like it. I'd +1 for the schema in the 3rd party db with no linkage/dependency on their objects.

  • Steve Jones - SSC Editor - Monday, November 27, 2017 10:42 AM

    I've used views like this, but don't like it. I'd +1 for the schema in the 3rd party db with no linkage/dependency on their objects.

    My biggest reservation with choosing this option is the fact that you are mixing in-house code with external code and therefore CI/CD processes which perform deployments or create drift reports, for example, become all the more complicated to build and maintain.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Phil Parkin - Monday, November 27, 2017 11:03 AM

    My biggest reservation with choosing this option is the fact that you are mixing in-house code with external code and therefore CI/CD processes which perform deployments or create drift reports, for example, become all the more complicated to build and maintain.

    Maybe. It's not terribly different from using a table and tracking the data as part of CI/CD. I could easily write tests to detect what values I want.

    The bigger issue for me is that if there are issues, someone might change the code of the view rather than make a data change, which might not be caught as easily.

Viewing 11 posts - 1 through 11 (of 11 total)

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