How to do modifications to columns in SQL

First let us create Employee table in our database.

CREATE TABLE [dbo].[Employee]([EmpID] [int] NOT NULL,[FirstName] [nvarchar](250) NOT NULL,[LastName] [nvarchar](250) NULL,[Salary] [money] NULL,[DeptID] [int] NULL)

Modification to existing column in table:

ALTER TABLE [dbo].[Employee]ALTER COLUMN [DeptID] INT NOT NULL

Adding new column into the table:

ALTER TABLE [dbo].[Employee]ADD Phone INT NULL

Removing the column from a table:

ALTER TABLE [dbo].[Employee]DROP COLUMN Phone

Removing the table from database:

DROP TABLE [dbo].[Employee]

Delete and Truncate in SQL

In SQL we have to use DELETE command in order to delete the data.

To completely remove the data from a table we can use TRUNCATE command.

Find below the differences between TRUNCATE and DELETE command

DELETE	TRUNCATE
It's DML command	It's DDL command
It's a row lock. Whenever you fire DELETE command it will lock the rows which satisfies deletion criteria	It will lock table and page but not the row
It removes only the data which satisfies deletion criteria	It removes all the data
WHERE clause is allowed to specify filters	WHERE clause is not allowed
It activates a trigger because the operation are logged individually	It cannot activate a trigger because the operation does not log individual row deletions
Compared to TRUNCATE it is slower because it logs everything	Faster than DELTE as it don't log anything
Rollback is possible	Rollback is not possible. But inside the transaction we can rollback both DELETE and TRUNCATE
It will not reset IDENTITY property	It will reset IDENTITY property

Syntax:

DELETE:

DELETE

FROM

Employee

WHERE FirstName = 'Anil'

It will remove all the rows where FirstName is Anil

TRUNCATE:

TRUNCATE TABLE Employee

It will remove all the data present in Employee table

SQL Server - DDL, DML, DQL, DCL and TCL

Today we are going to learn different categories of languages available in SQL:

We have four categories of languages in SQL:

DDL – Data Definition Language:

It is used to modify the structure of the table

      CREATE, ALTER, DROP, TRUNCATE

There are too many posts where we are seeing whether TRUNCATE is DDL/DML command.

Its DDL command because

a. It internally performs DROP and CREATE of the table

b. If you want to TRUNCATE the table you require ALTER permissions not only Write permissions

c. As it is DDL command, it will reset IDENTITY property of the table

DML – Data Manipulation Language:

It is used to manipulate the data available in table

      INSERT, UPDATE, DELETE

DQL – Data Query Language:

It is used to fetch the data from table

     SELECT

DCL – Data Control Language:

It is used to create roles, permissions, and referential integrity and also to control access to database by securing it

     GRANT, REVOKE

TCL – Transaction Control Language:

It is used to control/manage different transactions performed on a table

     COMMIT, ROLLBACK