Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am using a reporting database which for all practical purposes is a read only database. It is created data and dynamic reports are generated and viewed on this database. I was thinking of making this database as read-only.

Some of things I want to ask regarding read-only databases are

  1. Is only the data read only ? Can we still create indexes or views on this database ?
  2. What are the performace benefits of using a read-only database ? like, select queries using shared locks ..
  3. Is there any disadvantage of using a read-only database ?
  4. Any best practicse while using read-only reporting database.

Please let me know, if you need any more information.


share|improve this question
up vote 11 down vote accepted
  1. You cannot make schema changes when the database is read only but you could put all your user tables on a new file group and mark that as read only.
  2. You can expect a modest performance benefit from absence of locking.
  3. On versions of SQL Server prior to 2012 statistics can't be auto created or updated on read only databases.
  4. Before making it read only you might as well remove all logical fragmentation and make page density as high as possible. Any non default FILL_FACTOR settings will not be of benefit in a read only environment. Additionally create/update any statistics anticipated to be of use for SELECT queries if on version < 2012.
share|improve this answer
1 and 4, great ideas. – Mark Storey-Smith Dec 8 '11 at 13:15
  1. The entire database is read only so you cannot add indexes or views.
  2. No locking required so no locks are taken for any query other than a shared database lock. Obviously means that no blocking will occur either.
  3. Not as such no.
  4. None that I can suggest.
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.