Views based on Multiple tables causing Locks?

  • Hi, I have recently started at a new company and the way they have some data set up is as follows:
    DatabaseArchive.Table_1
    DatabaseArchive.Table_2
    DatabaseArchive.Table_3
    ...
    DatabaseArchive.Table_100
    DatabaseArchive.Table_101
    DatabaseArchive.Table_102

    Then in a separate Database the following:
    DatabaseDW.Table_103
    DatabaseDW.Table_104
    DatabaseDW.Table_105.

    In the DW database there are two views:
    View1 comprised of union of all tables in both databases :
    SELECT * FROM DatabaseArchive.Table_1
    Union 
    SELECT * FROM DatabaseArchive.Table_2
    ..
    Union 
    SELECT * FROM DatabaseDW.Table_104
    Union 
    SELECT * FROM DatabaseDW.Table_105.

    View 2 is just the tables in the DW database:
    SELECT * FROM DatabaseDW.Table_104
    Union 
    SELECT * FROM DatabaseDW.Table_104
    Union 
    SELECT * FROM DatabaseDW.Table_105.

    My question is that if I do any work on any table in the Archive DB say DatabaseArchive.Table_3 will it lead to any locks on the DW tables (say DatabaseDW.Table_104 )due to the fact there are 2 views which use the same tables and thus intersect?

  • No, the locks don't cascade up to the views and back down to the other tables referenced by the views.  If you do something to a table, you'll get locks on that table only.  (There are exceptions to that, for example when it comes to triggers and foreign key constraints, but I don't think that's what you're asking.)

    John

  • Btw, you might want to seriously consider using "UNION ALL" there rather than UNION.  I think you are going to force a massive sort of all the rows by using "UNION".

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Sorry it is UNION ALL, that was me just being lazy in typing up the question.  Thanks for the help.

  • ash.blair - Friday, February 2, 2018 8:39 AM

    Sorry it is UNION ALL, that was me just being lazy in typing up the question.  Thanks for the help.

    I hope our experts had answered your queries, besides if you're facing performance issues while retrieving the data from the tables OR while querying views,you could go for Indexed views. Please make sure the underlying tables are normalized , as you mentioned these are archive tables.

  • subramaniam.chandrasekar - Monday, February 5, 2018 1:10 AM

    ash.blair - Friday, February 2, 2018 8:39 AM

    Sorry it is UNION ALL, that was me just being lazy in typing up the question.  Thanks for the help.

     besides if you're facing performance issues while retrieving the data from the tables OR while querying views,you could go for Indexed views.

    UNION/UNION ALL are not permitted in indexed views, and even if it were, since these views are just combining a bunch of tables, an indexed view would likely not be helpful, and would take up a lot of space.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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