T-SQL STATEMENTS — DML and DDL

What is data definition language (DDL) ?

A data definition language (DDL) is a computer language used to create and modify the structure of database objects in a database. These database objects include views, schemas, tables, indexes, etc. This term is also known as data description language in some contexts, as it describes the fields and records in a database table.

  1. CREATE
  2. ALTER
  3. DROP
  4. TRUNCATE
  5. RENAME
  6. ENABLE/DISABLE TRIGGER
  7. COLLATIONS

1. CREATE

1.1 The following SQL statement creates a database called new_database

create database new_database

1.2 The following SQL statement creates a table called new_table

CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

2. ALTER

The alter table statement is used to add, delete, or modify columns in an existing table. The alter table statement is also used to add and drop various constraints on an existing table.

2.1 ALTER -DROP COLUMN

ALTER TABLE new_table DROP COLUMN City

2.2 ALTER -MODIFY COLUMN

To change the data type of a column in a table, use the following syntax:

ALTER TABLE new_table ALTER COLUMN Adress nchar(30)

3. DROP

3.1 The following SQL statement drops the existing database ‘new_database’

DROP DATABASE new_database

3.2 The following SQL statement drops the existing table ‘new_table’

DROP TABLE new_database

4. TRUNCATE

The truncate table statement is used to delete the data inside a table, but not the table itself.

truncate table new_table

select * from new_table

5. RENAME

The sp_rename is a system stored procedure that is used to RENAME the user-created objects in a database.

5.1 To change table name use following syntax.

sp_rename ‘new_table’, ‘new_named_table’

5.2 To change a column name use following syntax.

sp_rename ‘dbo.new_named_table.Address’, ‘new_Address’, ‘COLUMN’

select * from new_named_table

6. ENABLE/DISABLE TRIGGER

The following statement creates a trigger that is fired whenever a new row is inserted into the table. For the demonstration purpose, the trigger just returns a simple message.

CREATE TRIGGER simple_trigger_message

ON new_named_table AFTER INSERT

AS BEGIN

PRINT ‘A new member has been inserted’;

END

6.1 Disable trigger

To disable ‘simple_trigger_message’ trigger, use the following disable trigger statement

DISABLE trigger simple_trigger_message ON new_named_table

To disable all triggers on the current database, you use the following statement

DISABLE TRIGGER ALL ON new_database

7. COLLATIONS

7.1 To find the SQL Server instance level collation use, the T-SQL shown below

select SERVERPROPERTY(‘collation’) as servercollation

7.2 To find the current database collation, use the T-SQL shown below

SELECT name, collation_name DbCollation FROM sys.databases

What is data manipulation language (DML) ?

A data manipulation language (DML) is a family of computer languages including commands permitting users to manipulate data in a database. This manipulation involves inserting data into database tables, retrieving existing data, deleting data from existing tables and modifying existing data.

  1. SELECT
  2. UPDATE
  3. INSERT
  4. DELETE

1. SELECT

The select statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

SELECT * FROM items

SELECT pro_com, pro_name FROM items

2. UPDATE

The update statement is used to modify the existing records in a table.

update items set pro_com = 100, pro_price = 110 where pro_id > 106

SELECT * FROM items

3. INSERT

3.1 Specify both the column names and the values to be inserted

INSERT INTO items (pro_id, pro_name) values (1000, 1001)

SELECT * FROM items

3.2 If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query.

insert into items values (500,505,510,515)

SELECT * FROM items

4. DELETE

The delete statement is used to delete existing records in a table.

delete from items where pro_id > 110

select * from items

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store