February 18, 2016 at 8:49 am
Ok... I am committing two "don't do it" things here... but this is what I am trying to accomplish:
1. We have an application that archives data to customer databases, then "normalizes" the data and partitions the data into views, i.e.
CREATE VIEW dbo.VIEW_AllDates
AS
SELECT * FROM dbo.Jan15
UNION ALL
SELECT * FROM dbo.Feb15
UNION ALL
SELECT * FROM dbo.Mar15
(each table will contain anywhere from 900k to 2 million records in each month)
2. Typically (I teach my kids that "NEVER" and "ALWAYS" are things that do not exist), this particular view is not queried and the underlying tables (dbo.MMMYY) are not modified (no DDL statement), just ever-increasing inserts. There should not be any ROLLBACKs on this data (unless the server reboots) or updates, etc.... just inserts (and a periodic DELETE about every 6 months to a year to cleanup older data).
3. We now have a customer that wishes direct access to this data... something we have never done before.
• Trying to update the application process to create a separate custom view would take about 5-6 hours of development time, which the customer does not
wish to pay for.
4. Due to the way the application works and the atypical but possible query on the VIEW, we wish to provide access to the data using a "NOLOCK"/"READUNCOMMITTED" to prevent any run-away queries or possible issues with customer extracts impacting the application archiving or other report queries and blocking.
So... the thought is to create a new view,
CREATE VIEW dbo.VIEW_AllDates_Cust
AS
SELECT * FROM dbo.VIEW_AllDates WITH (NoLock)
The only possible issue I can see is the potential to get a "Could not continue scan with NOLOCK due to data movement." error message?
Does anyone have any other idea(s) on how to accomplish this, or any other potential "gottcha" issues with this?
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
February 18, 2016 at 12:59 pm
DB_Newbie2007 (2/18/2016)
(...)3. We now have a customer that wishes
(...)
which the customer does not wish to pay for.
At this point, I would tell the customer to make up their mind. If they don't want to pay for it, then they don't want it. Period.
That being said - I guess you are mostly right. Never is not a word in my vocubalary either, so I will never tell people to never nest views or to never use nolock. Youa re aware of this, you are aware of the risks - good for you. Go for it.
(Depending on the exact indexes and execution plans, there may be a slight theoretic chance of getting duplicated or omitted rows when the table is read if a page split hits the index being scanned - but I think that the chance of this happening is extremely small).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply