Updating table from a view in sql
In SQL Server 2012 SP2, trace flag 2453 is introduced. When this trace flag is enabled it can cause automatic recompiles to take account of changed cardinality as discussed further very shortly.No column statistics Having a more accurate table cardinality doesn't mean the estimated row count will be any more accurate however (unless doing an operation on all rows in the table).Consider a help desk application that allows support staff to create support tickets, where each ticket in the system exists as a record in the Tickets table. To ease data entry, let's say that administrative users can create "ticket templates," which pre-define the values for a number of ticket data points that are stored in a table named Ticket Templates.
If the number of modifications since the plan was compiled exceeds the recompilation threshold (RT) then the plan will be recompiled and statistics updated. From Plan Caching in SQL Server 2008 when many rows are involved as SQL Server has better information to work with.
UPDATE My Table SET Column1 = (SELECT Some Column FROM Some Other Table WHERE Such And Such = This And That), Column2 = (SELECT Some Other Column FROM Some Other Table WHERE Such And Such = This And That), ... FROM My Table INNER JOIN Some Other Table ON My Table. This And That WHERE Primary Key Column = Some Value Bear in mind that updating data in one table with data from another table may be a sign of bad database design.
WHERE Primary Key Column = Some Value UPDATE My Table SET Column1 = Some Other Table. I've seen developers use these approaches to duplicate data across multiple tables so that it's "easier" or "quicker" to write a query to read the data, but such actions are (usually) folly.
Recompiles As well as the modification based recompiles described above ) Locking It has been stated that table variables do not participate in locking. Running the below outputs to the SSMS messages tab the details of locks taken and released for an insert statement.
SET NOCOUNT ON; CREATE TABLE #T( [ID] [int] IDENTITY NOT NULL, [Filler] [char](8000) NULL, PRIMARY KEY CLUSTERED ([ID] DESC)) DECLARE @T TABLE ( [ID] [int] IDENTITY NOT NULL, [Filler] [char](8000) NULL, PRIMARY KEY CLUSTERED ([ID] DESC)) DECLARE @I INT = 0 WHILE (@I Process 58 acquiring Sch-S lock on OBJECT: 2:-1325894110:0 (class bit0 ref1) result: OK --*-- Process 58 acquiring IS lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK Process 58 acquiring S lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK Process 58 releasing lock on OBJECT: 2:-1293893996:0 --BEGIN TRAN; CREATE TABLE #T (X INT, Y CHAR(4000) NULL); INSERT INTO #T (X) VALUES(1) SELECT CASE resource_type WHEN 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id, 2) WHEN 'ALLOCATION_UNIT' THEN (SELECT OBJECT_NAME(object_id, 2) FROM allocation_units a JOIN partitions p ON a.container_id = p.hobt_id WHERE a.allocation_unit_id = resource_associated_entity_id) WHEN 'DATABASE' THEN DB_NAME(resource_database_id) ELSE (SELECT OBJECT_NAME(object_id, 2) FROM partitions WHERE partition_id = resource_associated_entity_id) END AS object_name, * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID DROP TABLE #T -- ROLLBACK 26 rows are returned showing that locks are held both on the object itself and on system table rows to allow for rollback and prevent other transactions from reading uncommitted data.
NB1: Table variables do not have statistics but can still incur a "Statistics Changed" recompile event under trace flag 2453 (does not apply for "trivial" plans) This appears to occur under the same recompile thresholds as shown for temp tables above with an additional one that if the first time they are executed when non empty.