February 2, 2018 at 5:11 am
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?
February 2, 2018 at 5:28 am
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
February 2, 2018 at 8:36 am
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) "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".
February 2, 2018 at 8:39 am
Sorry it is UNION ALL, that was me just being lazy in typing up the question. Thanks for the help.
February 5, 2018 at 1:10 am
ash.blair - Friday, February 2, 2018 8:39 AMSorry 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.
February 5, 2018 at 1:52 am
subramaniam.chandrasekar - Monday, February 5, 2018 1:10 AMash.blair - Friday, February 2, 2018 8:39 AMSorry 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply