Wednesday, July 24, 2013

Create, Alter and Drop – Database and Table

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: 
  • 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
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.
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 


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. 


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.