SQL Server SELECT with READONLY clause

Question

I wanted to read some data from a table in the read-only mode.

Having worked on DB2 earlier, I got confused and tried to use the FOR READONLY clause with my SELECT statement :P

After tinkering with it, I realized the following statement works:

SELECT * FROM dbo.Users READONLY

It took less than half the time to run the query with the READONLY clause than without it.

So I decided to google for some documentation about the same, but I couldn't find any documentation of using READONLY with the SELECT clause.

Our DBA has asked us not to use the READONLY clause if there is no documentation, as it can get obsolete in the newer updates/ versions.

I'd appreciate if someone can point to some useful documentation and let me know if this can be safely used in SQL Server stored procs.

I am using SQL Server version 11.0.3000.0

Answer 1

Old question, but it deserves a new answer in case someone thinks they've stumbled on a magic go-faster switch.

This has the same semantics as SELECT * FROM dbo.Users PLEASE_BUFF_MY_PYLONS, and SELECT * FROM Users AS U. It assigns the alias READONLY to the table Users and has no effect whatsoever on execution plans or query execution time on its own, and anyone stating otherwise better have some damn good evidence to back it up (as in, an execution plan that does not show READONLY is just an alias). READONLY is a T-SQL keyword, but only in the context of passing table variables to stored procedures. In the context of this query, it's just another identifier.

There are various reasons why a query can be slower in one case, faster in another (cached execution plans are one big reason); aliasing your table to the name READONLY is not one of them.

While there's no such thing as "readonly" mode for a query, there is such a thing as reading from a table without blocking writers by using snapshot isolation. This is not a magic go-faster switch either, but it is useful in many OLTP workloads.