1-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse
2
3TRUNCATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
4[;]
5
1DELETE -
2-DML COMMAND
3-Delete Rows from the table one by one
4-We can use where clause with Delete to delete single row
5-Delete is slower than truncate
6-ROLLBACK is possible with DELETE
7
8DROP-
9-DDL COMMAND
10-Delete the entire structure or schema
11-We can't use where clause with drop
12-Drop is slower than DELETE & TRUNCATE
13-ROLLBACK IS NOT POSSIBLE WITH DROP
14
15TRUNCATE-
16-DDL COMMAND
17-Truncate deletes rows at a one goal
18-We can't use where clause with Truncate
19-Truncate faster than both DELETE & DROP
20-Rollback is not possible with Truncate
1-- Syntax for SQL Server and Azure SQL Database
2
3TRUNCATE TABLE
4 { database_name.schema_name.table_name | schema_name.table_name | table_name }
5 [ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }
6 [ , ...n ] ) ) ]
7[ ; ]
8
9<range> ::=
10<partition_number_expression> TO <partition_number_expression>
11
1-- Truncate number with cast
2UPDATE my_table
3SET my_column = CAST(my_column AS INT)
4WHERE ...;
5-- or convert
6UPDATE my_table
7SET my_column = CONVERT(INT, my_column)
8WHERE ...;