adding lock option to Create Table statement in Sql Server 2005

Question

I am busy creating tables in Sql Server from a Sybase database. In the Sybase database when the tables are created an option 'lock allpages' was used, how can I replicate this when creating the tables in Sql Server 2005.

Answer 1

What do you want to achieve with this "lock allpages" option? Is the database you're working on up and running productively? If not, in SQL Server, you can restrict access to the entire database to a single user:

ALTER DATABASE YourDatabaseName SET SINGLE_USER

and that way you're sure no one else if going to come in your way and fiddle around until you're totally done :-)

Set it back to "normal" usage with:

ALTER DATABASE YourDatabaseName SET MULTI_USER

Marc

Answer 2

In SQL Server you cannot specify a lock option for the table in CREATE TABLE. You can at most disable row level and page level locking by adding the WITH ALLOW_ROW_LOCKS = OFF or WITH ALLOW_PAGE_LOCKS_OFF. The equivalent of locking the entire table in SQL Server is to use a lock hint WITH (TABLOCK) when running queries and updates on the table, but that is not recommended.

My recommendation would be to just ignore this option when transferring the tables from Sybase to SQL Server.