Specifying a locking scheme with select into

You can specify a locking scheme when you create a new table, using the select into command. The syntax is:

select [all | distinct] select_list 
          into [[database.]owner.]table_name
           lock {datarows | datapages | allpages}
from ...

If you do not specify a locking scheme with select into, the new table uses the server-wide default locking scheme, as defined by the configuration parameter lock scheme.

This command specifies datarows locking for the table it creates:

select title_id, title, price 
into bus_titles
lock datarows 
from titles
where type = "business"

Temporary tables created with the #tablename form of naming are single-user tables, so lock contention is not an issue. For temporary tables that can be shared among multiple users, that is, tables created with tempdb..tablename, any locking scheme can be used.