Read Only Tables
-
Please, please, please buy a book if you want good copy. Poor grammar and typos missed by the spellchecker have nothing to do with reducing the utility of useful articles.
Nice job, Steve... lot's of good folks have posted some great ideas despite your "obvious" poor grammar and typos.
--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Wow, didn't think my grammar was that bad. I ran this through Word and didn't see anything, but admittedly, I didn't get a copy editor through it.
I appreciate the criticism and I'd like to know where my poor grammar and typos are to fix them.
-
Steve,
I was being sarcastic with the person who made the suggestion of a copy editor... that's why I quoted "obvious"... You did great, as usual... in the spirit of this conversation, I'll say "you done real good"
--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Another nice article Steve, you have a knack of picking interesting topics that provoke interest.
I worry when it seems that triggers are seen as the obvious way to resolve problems. Having experienced triggers on oltp databases the quickest way to cripple performance is to use a trigger!
I like the concept of non updateable views, but ultimately stored procs and carefully defined permissions will be the best way forward - I still cannot understand why anyone wants to grant table rights - with SOX, identity theft, data protection legislation there can just never be any justification for assigning table rights - granting datareader / datawriter is akin to inviting your users to help themselves to your data - would you be happy with your bank / credit card / financial data stored on a system this way ???
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Another way to make a table ReadOnly - it's create table function.
Of course, it is reasonable when that table must have a small part of data.
E.g - a small dictionary.
-
Our DBA's took the easy way... everyone is part of a "role" and we grant privs, by table, by role.
The only problem with that is that read only tables don't change... why have the extra overhead of backing them up? I definitely like the idea of moving such tables to their own database. The use of synonyms (or pass through views that act like synonyms in 2k) alleviates the problems that some associate with the 3 part naming convention.
I'm not so much in favor of triggers to deny writability... seems like a lot of unneccessary overhead.
--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Good points Jeff. But I'm curious, what is the real overhead of an instead of trigger? I'm sure there is one, but I wouldn't expect it to be as bad as an after trigger. I would even expect the MS team to be smart enough to figure out wether or not the inserted and deleted tables where actually needed in an instead of trigger and not even build them when not required. In that case I would think that the overhead would be pretty much the same as a single call to a UFD.
Anyone up to test this theory?
-
I suppose that there wouldn't be so much overhead associated with an INSTEAD OF trigger... but, ya gotta be a bit careful how you write them (from what I understand) in that you should only write the columns that have a change or you may fire AFTER triggers unnecessarily and that's where the overhead would come in. Even if the AFTER triggers are correctly setup to only process changes (instead of just detecting a column that had be inserted/updated), the triggers still fire. Not sure how much overhead that causes...
Besides, it seems easier to simply avoid the triggers for this. Of course, they can be a pretty good audit tool... find out who's trying to change things and which machine they're doing it from and all... trigger would guarantee that they couldn't change it a read-only table without something getting logged. But, then comes back the idea of roles... read-only tables should only be allowed to change during a change control by the DBA's. So, if you don't have "SA" privs attached to your role, you don't get to change it.
--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yes, I liked your writing style, but really you can only use RAISEERROR, otherwise you're going to confuse the heck out of your users like you mentioned.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply