Feb 26
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.
- CREATE
- ALTER
- DROP
- TRUNCATE
- RENAME
- ENABLE/DISABLE TRIGGER
- 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.
- SELECT
- UPDATE
- INSERT
- 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
