September 29, 2017 at 5:44 am
We have a fees table, which we shall now share with other departments, meaning they should only be able to query the fees.
Until recently I would simply write
CREATE VIEW fees_view
AS
SELECT fees_Id, fees_Description
FROM myFees WITH (NOLOCK) ;
but one of my latest enquiries in this forum about using NOLOCK led to a lengthy discussion and I would like to ask now what is the best practice
to write a view, which will query relatively fixed table (we add / remove fee types rarely). Although fees is a very minor table, it seems I will need to
allow access to many other tables we have and I don't want people mingling with our content nor affecting our product's performance.
As always, any advise will be much appreciated.
September 29, 2017 at 8:24 am
BOR15K - Friday, September 29, 2017 5:44 AMWe have a fees table, which we shall now share with other departments, meaning they should only be able to query the fees.Until recently I would simply write
CREATE VIEW fees_view
AS
SELECT fees_Id, fees_Description
FROM myFees WITH (NOLOCK) ;but one of my latest enquiries in this forum about using NOLOCK led to a lengthy discussion and I would like to ask now what is the best practice
to write a view, which will query relatively fixed table (we add / remove fee types rarely). Although fees is a very minor table, it seems I will need to
allow access to many other tables we have and I don't want people mingling with our content nor affecting our product's performance.As always, any advise will be much appreciated.
My thought when I put a database together is never to expose the underlying tables (when possible) to the users and using views is one way to achieve that, the other is using synonyms.
Having read your post a few times it sounds like your users will be performing SELECTs on the data and seeing as locks aren't imposed or escalated during a SELECT, the NOLOCK option is redundant. I would therefore do the following:
--Create a synonym for the table
create synonym syn_vw_myFees for myFees;
--Create the view using the synonym
create view vw_fees
with schemabinding --because I want to prevent changes to the underlying tables
as
select fees_id, fees_description
from syn_vw_myFees;
As far as best practices is concerned I don't really know very many. Views help (don't eliminate) in hiding the structure of the database through obfuscation which is not really reliable.
The aspect that catches some out is the subject of isolation but unless it has been changed from READ_COMMITTED it shouldn't present any problems here.
The view itself is only going to be as good or reliable as the code that defines it.
September 29, 2017 at 8:36 am
kevaburg - Friday, September 29, 2017 8:24 AMand seeing as locks aren't imposed or escalated during a SELECT.
Err.... What?
Nolock is bad practice because it can cause incorrect results (duplicate rows, missed rows). Better options are use of one of the snapshot isolation levels, and/or query optimisation so that locks are held for minimal time.
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
September 29, 2017 at 8:39 am
So you want people to be able to query your database, but you don't want there to be any performance penalty? I'm afraid there's no such thing as a free lunch. If you can tolerate occasional wrong results, then by all means use NOLOCK. If not, there are possible other ways of lessening the impact on performance - maybe create a covering index on fees_Id, fees_Description? Don't forget to take into consideration the cost of maintaining the index in the case that the myFees table happens to be highly transactional. Another option is to create a read-only copy of your data with log shipping, replication or some similar technology, and open that up for querying. Again, you have to allow for the cost of maintaining that copy.
John
September 29, 2017 at 8:53 am
GilaMonster - Friday, September 29, 2017 8:36 AMkevaburg - Friday, September 29, 2017 8:24 AMand seeing as locks aren't imposed or escalated during a SELECT.Err.... What?
Nolock is bad practice because it can cause incorrect results (duplicate rows, missed rows). Better options are use of one of the snapshot isolation levels, and/or query optimisation so that locks are held for minimal time.
Fair enough...just read up on it and realised my mistake. That said, what a daft name for a hint when it can be so easily misinterpreted.....
September 29, 2017 at 9:07 am
NOLOCK hints on a query are generally a bad idea, regardless of whether the query is inside a view. Other than the nolock hint, there doesn't appear to be anything wrong with your view or your reasoning behind creating a view. Also, if you only want users to query the view(s) and not the tables directly, then don't add them to the db_owner or db_datareader roles. Simple add them as a user of the database and then only grant them select permission on the views.
For example:
USE [MyDB];
CREATE USER [MyDomain\UserGroup] FOR LOGIN [MyDomain\UserGroup];
GRANT SELECT ON View1 TO [MyDomain\UserGroup];
GRANT SELECT ON View2 TO [MyDomain\UserGroup];
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 29, 2017 at 12:29 pm
I like executing all my SELECT queries using the TABLOCKX hint... I'm selfish like that... 😀
September 29, 2017 at 2:49 pm
kevaburg - Friday, September 29, 2017 8:53 AMFair enough...just read up on it and realised my mistake. That said, what a daft name for a hint when it can be so easily misinterpreted.....
It's a reasonably accurate name for the hint, using it causes the query to take no shared locks against that table.
The side effects, the missing rows and duplicate rows, are mentioned in the documentation on the hint and the readuncommitted isolation level (which nolock is a synonym of)
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
September 30, 2017 at 3:50 am
GilaMonster - Friday, September 29, 2017 2:49 PMIt's a reasonably accurate name for the hint, using it causes the query to take no shared locks against that table.The side effects, the missing rows and duplicate rows, are mentioned in the documentation on the hint and the readuncommitted isolation level (which nolock is a synonym of)
But in tables like list of countries, cities or in my case - fee types, which almost never changed, using NOLOCK should not create any side effect , no?
Moreover, my understanding was that if those tables are heavily used, like our fee types one, it will be almost always cached in memory with a minimum access to the disk.
September 30, 2017 at 4:53 am
BOR15K - Saturday, September 30, 2017 3:50 AMBut in tables like list of countries, cities or in my case - fee types, which almost never changed, using NOLOCK should not create any side effect , no?
And no benefit. If the table's almost never changed, then there's no point in the nolock hint, as there are no data modifications for the select to block.
Extra typing, no gain, and setting a bad example (because people will see that and use it in places where they can and will get the side effects)
Moreover, my understanding was that if those tables are heavily used, like our fee types one, it will be almost always cached in memory with a minimum access to the disk.
They are, correct. It has no baring on the discussion here though
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
September 30, 2017 at 7:11 am
GilaMonster - Saturday, September 30, 2017 4:53 AMBOR15K - Saturday, September 30, 2017 3:50 AMBut in tables like list of countries, cities or in my case - fee types, which almost never changed, using NOLOCK should not create any side effect , no?
And no benefit. If the table's almost never changed, then there's no point in the nolock hint, as there are no data modifications for the select to block.
Extra typing, no gain, and setting a bad example (because people will see that and use it in places where they can and will get the side effects)
Moreover, my understanding was that if those tables are heavily used, like our fee types one, it will be almost always cached in memory with a minimum access to the disk.
They are, correct. It has no baring on the discussion here though
I understood this part. Thank you. I think my next task will be to allow access to fees themselves. it is slightly bigger table (several thousands) and from time to time will be updated (monthly / quarterly basis).
Would you advise to apply a same simple view approach: SELECT myColumns FROM myFeesTable or in this case I should be doing something different?
September 30, 2017 at 9:00 am
Why are you bothering with a view in the first place? What are you gaining over just giving the other system read access to the tables?
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
September 30, 2017 at 12:43 pm
GilaMonster - Saturday, September 30, 2017 9:00 AMWhy are you bothering with a view in the first place? What are you gaining over just giving the other system read access to the tables?
Good question, yet I cannot discuss it in an open forum.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply