SQL Server Deletes data

Delete Data using SSMs

1, connect the database, select Data table-Right click, select All rows (or select the first 200 rows).

2, select the data row in the Data window (note Click on the leftmost column to select the entire data row)-"Right click on the left side-" Select Delete-"Click OK in the pop-up box.

3. The example results are as follows:

To delete data from a single-table row with a T-SQL script

Syntax: Delete from database name. dbo. Table name where condition;

Example: Delete from Testss.dbo.test1 where id= ' 12 ';

Delete single-table multi-row data

Syntax: Delete from database name. dbo. Table name where condition or delete top (n) from database name. dbo. Table name where condition;

Example:

Delete from Testss.dbo.test1 where id>= ' and id<= ' 15 ';

Delete from Testss.dbo.test1 where id between ' + ' 17 ';

Delete from Testss.dbo.test1 where ID in (' 18 ', ' 19 ');

Delete Top (2) from Testss.dbo.test1 where id>= ' 20 ';

Delete all data in a table

Syntax: Delete from database name. dbo. Table name;

Example: Delete from Testss.dbo.test1;

Clears all data in the table

Syntax: TRUNCATE TABLE database name. dbo. Table name;

Example: Truncate TABLE testss.dbo.test1;

Truncate and delete Summary

Efficiency: TRUNCATE is faster than delete and uses less system and transaction log resources.
Scope of application: Truncate can only be table,delete to table and view.
Log operations: Delete statement deletes one row at a time and records an entry for each row deleted in the transaction log, so you can roll back the delete operation
The difference between the two:

1, truncate on various tables whether large or small are very fast, if there is rollback command Delete will be revoked, and truncate will not be revoked.
2. Truncate is a DDL language, and as with all other DDL languages, he will be implicitly committed and cannot use the rollback command with truncate.
3. Truncate will reset the high-level line and all indexes, and the table after truncate operation is much faster than the table after the delete operation when full browsing is performed on the entire table and index.
4. Truncate cannot trigger any delete triggers.
5. When the table is truncate emptied, the table and table indexes are reset to the initial size, and delete cannot.
6. Cannot empty the parent table.

Summary

Delete does not require a column name and wildcard character, it deletes the entire row instead of the column, deletes the specified column, uses the UPDATE statement, and deletes the row from the table, or even deletes all rows in the table, rather than deleting the table itself. Deleting data is risky and should be backed up before deletion.

SQL Server Deletes data