TSQL: Deleting Data

Syntax:

DELETE <object>;
DELETE <object> WHERE <search_condition>;

Notes:

• WHERE clause contains the search conditions. Can use sub-queries are a predicate.
• You can join multiple tables, with the corresponding rows deleted from the initial FROM clause.

Samples:

delete cpv
from CustomPropertyValue cpv
inner join RelationshipMapping rm on rm.MappingId=cpv.RelationshipMappingId and cpv.CustomPropertyDefinitionId in (4,5)
where SourceId = 233 and RelationshipTypeId = 5


DELETE vs TRUNCATE TABLE

• DELETE allows you to limit the rows being deleted using search conditions. Able to apply finer-tuned permissions.
• TRUNCATE deletes all rows from a table

          - no WHERE clause
          - Faster than DELETE due to a reduced amount of transaction log records generated
          - Reduced lock overhead
          - Cannot be used with tables referenced by foreign keys
          - Cannot be used for tables participating in indexed views, transactional replication, or merge replication.
          - Minimum permission: ALTER permission on the table