Difference Between Truncate and Delete Command in SQL Server

I have seen a lot of times, people get confused to differentiate the Truncate and Delete commands in SQL Server. If you are a DB developer or a DBA, then this is a mandated requirement to know exactly what is being performed when Truncate on a table and delete on a table. Let’s check the difference between Truncate and Delete in SQL Server.

TRUNCATE 

TRUNCATE is a DDL command, which is used to delete all the rows of a table without being to use a WHERE clause. Most of them come up with an assumption that a truncate cannot be rolled back once the TRUNCATE command is executed. But, the assumption is very untrue! The TRUNCATE command can be rolled back with the lopping into a begin transaction and rollback commands. 

Further, in truncation, the pages are completely deallocated and commit to the transaction log as deallocation. TRUNCATE can be only executed if login is given with an ALTER permission for a database. Additionally, as the rows are completely removed from the table, there is less utilization in the transaction log, hence performance is high.

Before we proceed with the truncation of the table, let’s create a dummy table, insert some values on CusName and CusID, and test on it.

Create table dummy
(
CusID int not null,
CusName nvarchar(100) null
)

Insert into dummy values (1, 'Dale'), (2, 'James')


T-SQL command to truncate a table.

begin tran
Truncate table dummy

select * from dummy


rollback 



The above command will completely remove two rows with two unique records from the dummy table.

DELETE

DELETE is a DML command, which is used to either remove all the rows from a table or a specific row from a table using the WHERE clause. As the rows are deleted from the table one by one, every single delete from the table is maintained and recorded in the transaction log. Since such records are maintained, there is high utilization of transaction log, due to which the performances are low.

Further, there is a need to DELETE permission for login in SQL Server to carryout the DELETE command. When there is a rollback possibility for Truncate, there is also a similar way to rollback DELETE records of a table by looping to Begin Transaction.

T-SQL command to delete a table.

Let’s use the dummy table to delete the CusID record.

begin tran
DELETE from dummy where CusID=1

select * from dummy



The above delete command will just delete the row selected in the WHERE clause with CusID=1.

 rollback 

Understanding the Importance of Database Schema in SQL Server

A schema is a container for objects such as tables, views, stored procedures, functions, etc., to facilitate management and ownership of a database object. In Microsoft SQL Server, the default schema is dbo, wherein any object created will be referenced to a dbo schema. There can be more than one schema created for a database in SQL Server. When a database is created, by default we will get to see the schemas as shown in the screenshot.


Let’s take a look at the example for differentiating the default schema and the created schema.

CASE 1: Creation of default dbo schema

Say, if we are creating a database with the name SQLArena and create an object say SQLArena_Test, then the object will reference to a dbo schema by default.

Create database [SQLArena]
Go
Create table SQLArena_Test(id int null)

From the created table, the id is the value which will be referenced to the dbo schema by default

CASE 2: Create a new database schema (Schema_Name: Trash)

Say, if we are creating a new database schema in already created database – SQLArena, then we will have to create the table with the prefix as the <Schema_Name> along with the object name to reference to the respective schema created. We can create the same table SQLArena_Test with a new database schema as below.

Create table <Schema_Name>.SQLArena_Test(id int null)

Example:
Create table Trash.SQLArena_Test(id int null)

From the above two cases, don’t be confused while fetching the column of the same table. Now, while fetching for the ‘id’ column in SQLArena database, by default the ‘dbo’ schema will be fetched rather than the ‘Trash’ schema.

GUI to guide you through creating a new schema.

1. Expand the respective database from the object explorer.
2. Expand “Security” under the selected database.


3. Now, we will see an option to expand from “Schema”.
4. Here, all the default schemas will be intact. One such is the dbo.


5.  Right-click on the schema and select “New Schema…”.