I am trying to create a temporary table. It will contain approx 25 million rows to start with and will increase in future.
Option1: SELECT col1 into #tempT from table
Option2: create table #tempT (col1 int)
insert into #tempT
select col1 from table
While doing both I ran the following to get locking information.
(CASE WHEN resource_type = 'OBJECT'
WHEN resource_type IN ('DATABASE', 'FILE', 'METADATA') THEN 'N/A'
WHEN resource_type IN ('KEY', 'PAGE', 'RID')
THEN ( SELECT object_name(object_id)
FROM sys.partitions WHERE hobt_id=resource_associated_entity_id )
ELSE 'Undefined' END) AS requested_object_name,
request_mode AS lock_type, request_status,
request_owner_id AS transaction_id
where request_session_id =52
1. I have read that SELECT INTO creates exclusive lock on objects in the system catalog. In fact, creation of any object would do that, howwever when SELECT INTO runs for longer then the catlog locking will be longer and at that point in time if you try to query system catalog then you might run into blocking issues.
When i ran the query to get the locking information I see only schema locks on the table from which i am trying to read the data and possibly the new temp table that was being created. This happened for both SELECT INTO and INSERT INTO.
Can someone help me uunderstand -
1. How INSERT INTO is better as compared to SELECT ITNO when there are huge datasets expected.
2. Would this SELECT INTO also hamper the rebuild index and other db activities if the catlog is exclusively locked.