Read-Only (NO LOCK) Tables instead of Database

Question

Read-Only (NO LOCK) Tables instead of Database

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Create a second filegroup and make it the default filegroup. Put all the tables into that filegroup. Set the filegroup readonly. Then you can still alter the procs (they're in Primary), but the tables are on a read-only filegroup and hence won't be locked.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jvanderberg SSCertifiable Points: 6762 More actions · Answer 2

Beautiful. Thanks.

One question. All the examples I'm finding online of "MOVE TO" assume that the PK and clustered index are one in the same. That's fine for all but one table, but my fact table has a PK based on a meaningless identity column, but a clustered index on InvoicedDate. Is there anything special I need to do?

Thanks again,

--J

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

If you want to completely avoid locking, you'll need to move all indexes, clustered and nonclustered to the new filegroup. You can move all with CREATE ... WITH DROP EXISTING, even the ones enforcing primary keys/unique constraints.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jvanderberg SSCertifiable Points: 6762 More actions · Answer 4

Alright. I use a script to drop/recreate that table every day anyways. I'll just modify it to the new filegroup, and let it do all the leg work tonight.

Thanks.

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 5

Gail's suggestion works - and might even be the best option. However, there is no reason why you couldn't create your objects in another database to work with the read only databases.

All you need to do is use 3-part naming and specify the database in the queries.

As for the regular maintenance of the procedures - are those done by other people?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs