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.
