Create, Alter and Drop – Database and Table
We are going to learn, how we can create, alter and drop a database and table. We would also explore how to add columns to an existing table, change column definition and delete column from the table.
Below is the abstract of this chapter:
{CREATE | ALTER | DROP} DATABASE – Used to create / alter / delete a database respectively.
{CREATE | ALTER | DROP} TABLE – Used to add / modify / remove table in a database respectively.
1 {CREATE | ALTER | DROP} DATABASE STATEMENT
1.1 CREATE DATABASE
We can use below command to create a database on a SQL Server instance. To create a database, we must be logged in to the server and have appropriate permissions (CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE).
Syntax: CREATE DATABASE <database-name>
where <database-name> is the name of the database to be created and must follow below conventions:
1.3 DROP DATABASE
To delete a database permanently from the server, follow below syntax;
Syntax: DROP DATABASE <database-name>
Below is the command to drop database “DummyDatabase”;
DROP DATABASE DummyDatabase;
2.1 CREATE TABLE
We have learnt how to create, alter and drop a database, now its time to learn how we can add tables in this database. To create a table in a database, we can follow below syntax;
Syntax: CREATE TABLE <schema-name>.<table-name> (<column-definition-1>,<column-definition-2>,………<column-definition-n>)
where <schema-name> is the name of schema in which table will be created and <table-name> is the name of the table needs to be created. Mind the dot(.) between schema name and table name.
To create a table named “Student” with columns “Student_Id” and “Name”, use below command;
USE DummyDatabase
CREATE TABLE dbo.Student (Student_Id INT NOT NULL PRIMARY KEY, Name VARCHAR(256) )
GO
Schema:
2.2 ALTER TABLE
Alter table statement can be used to modify the existing table definition by adding, deleting and modifying columns. We can also use ALTER TABLE statement to add constraints and drop constraints in an existing table.
2.2.1 Add column in existing table
To add a new column in an existing table, below is the syntax;
Syntax: ALTER TABLE <schema-name>.<table-name> ADD <column-name> <data-type>
Example:
USE DummyDatabase
ALTER TABLE dbo.Student ADD Address1 VARCHAR(512)
GO
We have added an “Address1” column in Student table. Data type of Address1 column is VARCHAR with length 512.
2.2.3 Drop column from table
To remove a column from a table, follow below syntax;
Syntax: ALTER TABLE <schema-name>.<table-name> DROP COLUMN <column-name>
To drop a column;
USE DummyDatabase
ALTER TABLE dbo.Student DROP COLUMN Address1
GO
Drop multiple columns at once
To drop multiple columns at once, use comma separated list of columns as;
USE DummyDatabase
ALTER TABLE dbo.Student DROP COLUMN Address1, Name
GO
Note: We can also use ALTER TABLE statement to add and remove constraint which we would cover later in this tutorial.
2.3 DROP TABLE
To drop a table, follow below syntax;
Syntax: DROP TABLE <schema-name>.<table-name>
To drop student table;
USE DummyDatabase
DROP TABLE dbo.Student
GO
Test Your Skills:
We are going to learn, how we can create, alter and drop a database and table. We would also explore how to add columns to an existing table, change column definition and delete column from the table.
Below is the abstract of this chapter:
{CREATE | ALTER | DROP} DATABASE – Used to create / alter / delete a database respectively.
{CREATE | ALTER | DROP} TABLE – Used to add / modify / remove table in a database respectively.
1 {CREATE | ALTER | DROP} DATABASE STATEMENT
1.1 CREATE DATABASE
We can use below command to create a database on a SQL Server instance. To create a database, we must be logged in to the server and have appropriate permissions (CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE).
Syntax: CREATE DATABASE <database-name>
where <database-name> is the name of the database to be created and must follow below conventions:
- It must be unique on SQL Server instance.
- Can be 128 characters long if we provide the log and data file names (should be 128 characters long) explicitly with CREATE DATABASE command. Otherwise we can have 123 characters in database name because default name for log file (created by SQL Server) appends a suffix to the given database name (<database-name>).
- It can be alphanumeric and must follow the SQL Server identifiers naming conventions.
- A maximum of 32767 databases can be created on a single instance of SQL Server.
Using above syntax, we can create a database with all default settings as;
USE master
USE master
CREATE DATABASE DummyDatabase
GO
Note: “USE master” statement is used to set the current database as master database to create a new database.
If required, we can change default settings, either at the time of database creation or later on (using ALTER DATABASE command). For example, to create a database with explicitly defined data and log file names, initial size, auto growth and max size;
USE master
CREATE DATABASE DummyDatabase
ON PRIMARY
( NAME = N'DummyDatabase_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DummyDatabase_Data.mdf' ,SIZE = 20 , MAXSIZE = UNLIMITED, FILEGROWTH = 10 )
LOG ON
( NAME = N'DummyDatabase_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DummyDatabase_Log.ldf' ,SIZE = 10 , MAXSIZE = 100 , FILEGROWTH = 10 )
GO
Above, we have created a database named “DummyDatabase” with data file name “DummyDatabase_Data”, initial size 20 MB, max size unlimited with auto increment of 10 MB and Log file name DummyDatabase_Log”, initial size 10 MB, max size 100 MB and file growth is 10 MB.
GO
Note: “USE master” statement is used to set the current database as master database to create a new database.
If required, we can change default settings, either at the time of database creation or later on (using ALTER DATABASE command). For example, to create a database with explicitly defined data and log file names, initial size, auto growth and max size;
USE master
CREATE DATABASE DummyDatabase
ON PRIMARY
( NAME = N'DummyDatabase_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DummyDatabase_Data.mdf' ,SIZE = 20 , MAXSIZE = UNLIMITED, FILEGROWTH = 10 )
LOG ON
( NAME = N'DummyDatabase_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DummyDatabase_Log.ldf' ,SIZE = 10 , MAXSIZE = 100 , FILEGROWTH = 10 )
GO
Above, we have created a database named “DummyDatabase” with data file name “DummyDatabase_Data”, initial size 20 MB, max size unlimited with auto increment of 10 MB and Log file name DummyDatabase_Log”, initial size 10 MB, max size 100 MB and file growth is 10 MB.
1.2 ALTER DATABASE
We can change the settings of created database using ALTER DATABASE command. For example, to modify the file name (data or log file), follow below syntax;
Syntax: ALTER DATABASE <database-name> MODIFY FILE (NAME = <old file name>, NEWNAME = <new file name>)
USE DummyDatabase
ALTER DATABASE DummyDatabase MODIFY FILE (NAME ='DummyDatabase_Data', NEWNAME = 'DummyDatabase_Data_Test')
GO
To change the name of the database from DummyDatabase to DummyDatabase_Test, we can use below command;
USE master;
ALTER DATABASE DummyDatabase Modify Name = DummyDatabase_Test;
GO
We can change the settings of created database using ALTER DATABASE command. For example, to modify the file name (data or log file), follow below syntax;
Syntax: ALTER DATABASE <database-name> MODIFY FILE (NAME = <old file name>, NEWNAME = <new file name>)
USE DummyDatabase
ALTER DATABASE DummyDatabase MODIFY FILE (NAME ='DummyDatabase_Data', NEWNAME = 'DummyDatabase_Data_Test')
GO
To change the name of the database from DummyDatabase to DummyDatabase_Test, we can use below command;
USE master;
ALTER DATABASE DummyDatabase Modify Name = DummyDatabase_Test;
GO
1.3 DROP DATABASE
To delete a database permanently from the server, follow below syntax;
Syntax: DROP DATABASE <database-name>
Below is the command to drop database “DummyDatabase”;
DROP DATABASE DummyDatabase;
2 {CREATE | ALTER | DROP} TABLE
2.1 CREATE TABLE
We have learnt how to create, alter and drop a database, now its time to learn how we can add tables in this database. To create a table in a database, we can follow below syntax;
Syntax: CREATE TABLE <schema-name>.<table-name> (<column-definition-1>,<column-definition-2>,………<column-definition-n>)
where <schema-name> is the name of schema in which table will be created and <table-name> is the name of the table needs to be created. Mind the dot(.) between schema name and table name.
To create a table named “Student” with columns “Student_Id” and “Name”, use below command;
USE DummyDatabase
CREATE TABLE dbo.Student (Student_Id INT NOT NULL PRIMARY KEY, Name VARCHAR(256) )
GO
Schema:
Schema is a namespace which provides logical grouping of objects for segregation and management. “dbo” is the default schema of a database which is used if schema name is not specified explicitly. However we can omit the <schema-name> in case of “dbo” but it is not recommended. In case you want to create a new Schema, you can follow “CREATE SCHEMA <schema-name>” syntax.
2.2 ALTER TABLE
Alter table statement can be used to modify the existing table definition by adding, deleting and modifying columns. We can also use ALTER TABLE statement to add constraints and drop constraints in an existing table.
2.2.1 Add column in existing table
To add a new column in an existing table, below is the syntax;
Syntax: ALTER TABLE <schema-name>.<table-name> ADD <column-name> <data-type>
Example:
USE DummyDatabase
ALTER TABLE dbo.Student ADD Address1 VARCHAR(512)
GO
We have added an “Address1” column in Student table. Data type of Address1 column is VARCHAR with length 512.
2.2.2 Alter existing column
To alter the definition of an existing column, follow below syntax;
Syntax: ALTER TABLE <schema-name>.<table-name> ALTER COLUMN <column-name> <data-type>
Example:
USE DummyDatabase
ALTER TABLE dbo.Student ALTER COLUMN Address1 VARCHAR(1024)
GO
Above, we have changed the length of Address1 column from 512 to 1024.
To alter the definition of an existing column, follow below syntax;
Syntax: ALTER TABLE <schema-name>.<table-name> ALTER COLUMN <column-name> <data-type>
Example:
USE DummyDatabase
ALTER TABLE dbo.Student ALTER COLUMN Address1 VARCHAR(1024)
GO
Above, we have changed the length of Address1 column from 512 to 1024.
2.2.3 Drop column from table
To remove a column from a table, follow below syntax;
Syntax: ALTER TABLE <schema-name>.<table-name> DROP COLUMN <column-name>
To drop a column;
USE DummyDatabase
ALTER TABLE dbo.Student DROP COLUMN Address1
GO
Drop multiple columns at once
To drop multiple columns at once, use comma separated list of columns as;
USE DummyDatabase
ALTER TABLE dbo.Student DROP COLUMN Address1, Name
GO
Note: We can also use ALTER TABLE statement to add and remove constraint which we would cover later in this tutorial.
2.3 DROP TABLE
To drop a table, follow below syntax;
Syntax: DROP TABLE <schema-name>.<table-name>
To drop student table;
USE DummyDatabase
DROP TABLE dbo.Student
GO
Test Your Skills:
- Create a new database named as “TestDatabase” with 100 MB initial size.
- Rename the log file of created database from “TestDatabase_Log” to “TestDatabase_LogFile”.
- Add a table named “Department” (dbo schema) with DepartmentId – INT, DepartmentName – VARCHAR(100), HODName – VARCHAR(256), EffectiveFrom – DATETIME and IsActive – BIT columns with mentioned data types.
- Add columns CostCentre – VARCHAR(100) and CostCentreHead – VARCHAR(256) to table Department.
- Create a new schema named “stu”.
- Add a table Student (schema is already given in this chapter) in schema “stu”.
- Drop table Student from stu schema.
- Drop table Department from dbo schema.
- Drop database TestDatabase.
