SQL Server Performance

How to specify a query as read only

Discussion in 'SQL Server 2005 General Developer Questions' started by dhana1706, Sep 4, 2008.

  1. dhana1706 New Member

    Hi all,

    I want to know about ReadOnly SQL Queries.

    And How to create the Readonly Queries?What are the advantages?If anyone know, plz let me know.

    Thanks,
    Dhana
  2. TommCatt New Member

    I'm a bit confused as to what exactly you mean by a "readonly query." Queries by their very nature are readonly.

    Do you mean a view? You can make a view readonly by creating an "Instead Of" trigger for Insert, Update, Delete which consists of nothing more than a return statement.
  3. atulmar New Member

    Do you mean to say that query is going to just read the data?
  4. TommCatt New Member

    Well, YEAH! A query takes the form "Select ... from ..."

    No matter how hard you try, you can't make any changes to the data with just "Select..." You have to use the non-query commands (Insert, Update, Delete)to change the data.

    This makes me think that you mean something else by "query." Could you be referring to the connection itself?
  5. Keith New Member

    Perhaps Dhana is thinking of a query with no locks on the table(s) being used?
    e.g.
    SELECT * FROM Customer_tbl WITH (NOLOCK)
  6. satya Moderator

    We can see the OP (Dhana) hasn;t followed it up after the Q has been posted, so I think it could be a interview or classroom based question.
    Appreciate your ideas on reply though.
  7. rcurrie New Member

    I am wondering the very same question.For further clarification there used to be the ability to set a VIEW as 'ReadOnly' in SQL Server 2000 I believe by going into the VIEW properties and then editing its 'Permission' set, though I'm not sure that works any more...or does it?I guess my underlying question is...is there a way to simply flag a VIEW a 'ReadOnly' via some Property, etc.? Instead of having to create empty TRIGGERS, Constraints, etc.Thanks!
  8. FrankKalis Moderator

    Welcome to the forum!
    By definition is a query "read-only", because it is nothing but a request to the database to retrieve some information from there.
    The only way I could imagine (and provided that the view is updatable anyway) is to revoke all but SELECT permisisions from the view.
  9. Adriaan New Member

    Not revoke update/insert/delete, but deny, I would think.
    Remember that by granting permission on a view, the permission is valid for the underlying table as well, unless the permission is denied on the table itself for the same grantee.
  10. FrankKalis Moderator

    Good catch! [:)]
  11. satya Moderator

    As referred by Frank if you are looking to restrict the permissions on any DB object then you have to control the set by referring to Database fixed roles or specific privileges on that table.
    There is no such topic in SQL that implies a QUERY is READ-ONLY, its the permission you need to set on the user to ensure the access is not compromised.
    [quote user="rcurrie"]I am wondering the very same question. For further clarification there used to be the ability to set a VIEW as 'ReadOnly' in SQL Server 2000 I believe by going into the VIEW properties and then editing its 'Permission' set, though I'm not sure that works any more...or does it? I guess my underlying question is...is there a way to simply flag a VIEW a 'ReadOnly' via some Property, etc.? Instead of having to create empty TRIGGERS, Constraints, etc. Thanks![/quote]
  12. rcurrie New Member

    Thanks for the replies.I was asking about a view objects in general. Originally (in earlier versions of SQL Server) I thought there was simply a flag you could set on the view object itself that would allow affective 'read-only' operations...like simple select queries, but would therefore disallow any attempt to update the data contained or represented by the view object.It seems this has changed slightly so now you manage permissions on a view object by rights management. Which makes sense I guess...I just thought there was a way to say this view object can only be used to "look into" the database, but not update anything.Thanks!
  13. yossi New Member

    Here is an interesting "twist":
    Is there a way to make only certain columns of the view read-only?
  14. atulmar New Member

    INSTEAD OF triggers?
  15. yossi New Member

    That's right. Instead of triggers. Is there SQL keyword specifying that a particular column in a view is read only?
  16. atulmar New Member

    IF UPDATE(<read_only_column>)
    ROLLBACK
  17. yossi New Member

    It won't let me create a trigger on the view!!!!CREATE
    TRIGGER [tg_qry_ValuationCcy] ON dbo.qry_ValuationCcyFOR
    UPDATE AS
    set nocount on
    ----------------------
    -- Allow changes only to ValuationCcy
    ----------------------
    IF not UPDATE (ValuationCcy) ROLLBACK
    This is the error msg that I get back:Msg 208, Level 16, State 4, Procedure tg_qry_ValuationCcy, Line 1
    Invalid object name 'dbo.qry_ValuationCcy'.
  18. atulmar New Member

    CREATE TRIGGER [tg_qry_ValuationCcy] ON dbo.qry_ValuationCcy
    INSTEAD OF UPDATE
    AS
  19. FrankKalis Moderator

    What's wrong in creating the table trigger on the base table(s)?

Share This Page