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

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.

Do you mean to say that query is going to just read the data?

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?

 Perhaps Dhana is thinking of a query with no locks on the table(s) being used?

 e.g.

SELECT * FROM Customer_tbl WITH (NOLOCK)

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.

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!

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.

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.

 Good catch!

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.

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!

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!

Here is an interesting "twist":

Is there a way to make only certain columns of the view read-only?

INSTEAD OF triggers?

 

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