Hello everyone,
Does SQL Server 2008 support table level readonly -- i.e. I can mark some table as readonly so that we could improve performance (for example, no transaction log needed for the readonly table)?
thanks in advance, George
+1
A:
You can add the table to a read only filegroup
Note that the Transaction log records UPDATE, INSERT and DELETE only (simply: no pedants please). If you only SELECT then why bother?
gbn
2009-07-10 13:25:17
Thanks, in my store procedure, I will read table 1, update table 2 and may join table 1 with table 2, but no update/delete/insert operation on table 1, in this case set table 1 as readonly has no benefit?
George2
2009-07-10 13:30:17
It prevents "accidental" changes. And allows 100% fill factor (indexes can't be rebuilt on it). Otherwise, not really.
gbn
2009-07-10 13:52:28
1. You mean no performance improvements if I make table as readonly? 2. "100% fill factor (indexes can't be rebuilt on it)" -- means?
George2
2009-07-10 14:09:18
1. yes. No performance benefit. 2. it's a side effect: FIll factor is basically how many rows are squeezed onto a page. 100% is better than say 90%, but is not good for data changes
gbn
2009-07-10 14:16:23
Thanks gbn. I am still a bit confused, you mentioned "And allows 100% fill factor (indexes can't be rebuilt on it). Otherwise, not really." -- do you mean for all read only tables, the fill factor is 100% or we have to set some options in order to make the fill factor 100%? Another confusion is why you mention index rebuild here -- for read only table, both data and index are read only, so why we need to rebuild index?
George2
2009-07-10 17:31:37
You'll have to change your maintenance routine to ignore read only tables, won't you? And yes, you specify fill factor:it's in SQL Server BOL
gbn
2009-07-10 18:37:43
Thanks gbn, 1. you mean default maintenance routine will rebuild index on readonly table?? Could you show me the related MSDN link please? :-) 2. Another confusion is why/what benefit is if we assgin fill factor for a readonly table, could you show me a scenario please?
George2
2009-07-11 05:40:32