SQL #4: DDL

2 minute read

This is the fourth in a series of SQL notes I made during the Kubrick Data Engineering training course. The others are:
#1: Database Design
#2: SQL Design
#3: DQL
#5: DML
#6: Sorting
#7: Joining
#8: Temporary Tables
#9: Programmable Objects
#10: Indexing
#11: Operation & Optimization

All the posts in this series are my personal note taking and may be updated as the course progresses.

Data Definition Language includes the keywords CREATE, DROP, and ALTER. These keywords can be applied to many different SQL objects, a few are databases, schemas, tables, triggers, functions, constraints, and users. Objects in SQL are referred to as securables.

CREATE

CREATE is used to create securables like the following example.

CREATE SCHEMA schema_1

To create a table just state it

CREATE TABLE schema_1.product
(
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(200),
Sku VARCHAR(10) NOT NULL,
Price MONEY
)

CREATE is followed by the type of object to create and the name of the object. Inside the brackets are the column names and their respective data types. NOT NULL specifies that the field values are not allowed to be NULL. IDENTITY(x,y) is a function which automatically generates unique numbers, starting at x and incrementing by y. PRIMARY KEY specifies which column should be used as a primary key, this automatically creates a clustered index on the table, which allows for faster queries.

DECLARE

DECLARE is used to create variables, these can be local (within a function) or global. Variables are displayed using @, must have a defined data type and can be selected just like a column.

DECLARE @dt INT = 20190123
SELECT
	 @dt / 10000 yr
	,@dt / 100 % 100 mnth
	,@dt % 100 dy

DROP

DROP is the opposite of CREATE and will delete an object from storage. A common usage of DROP is to add an IF statement preceding a CREATE statement which checks for the existence of the object and if it does exist it will DROP. This allows for a fresh creation each time the script is run.

IF OBJECT_ID(N'product') IS NOT NULL
BEGIN
	DROP TABLE product
END

ALTER

ALTER is used to make changes to an object which already exists. You can add a new column just using the ADD keyword.

ALTER TABLE product
ADD Colour VARCHAR(20)

This could be used to add a FOREIGN KEY to a table.

ALTER TABLE basket
ADD CONSTRAINT FK_ProductID FOREIGN KEY (ProductID)
REFERENCES product(ID)

An ALTER can also be nested to change the type of an existing column for example.

ALTER TABLE product
ALTER Colour VARCHAR(30) NOT NULL

Or to DROP a column.

ALTER TABLE product
DROP COLUMN Colour

The stored procedure sp_rename can be used to rename tables or columns.

sp_rename ‘product.colour’, ‘color’, ‘COLUMN’
sp_rename ‘product’, ‘prdct’

Updated: