SQL Server CREATE, ALTER, DROP Table [T-SQL Examples]

What is a Table?

A Table is an object which stores data in Row & Column format. Below Diagram, shows Rows and Column respectively.

Table Rows and Column
Table Rows and Column

How to Create a Table in SQL Server with T-SQL

The first step to store data in the database is to create a Table where data will reside. Post creation of the table, we can keep inserting the rows in the table.

We can Create a table in the following ways:

  1. T-SQL: Create a New Table by defining all columns and its data type.
  2. T-SQL: Create New Table using an existing table
  3. Using Table Designer

Let’s study this in detail:

T-SQL: Create a New Table by defining all columns and its data type.

Below is the Syntax to create table in SQL Server

Syntax

CREATE TABLE tableName
( 
  column_1 datatype [ NULL | NOT NULL ],
  column_2 datatype [ NULL | NOT NULL ],
  ...
);

Here,

  • The parameter tableName denotes the name of the table that you are going to create.
  • The parameters column_1, column_2… denote the columns to be added to the table.
  • A column should be specified as either NULL or NOT NULL. If you don’t specify, SQL Server will take NULL as the default.

Let’s create a basic Table with two columns

Query:

CREATE TABLE COURSE
(
Course_ID Int,
Course_Name Varchar(10)
)

Pre-Requisite: Select the Database where you need to create the table.

Create a Table in SQL Server with T-SQL

Step 1) Run the query by clicking on Execute.
Before running the query, no table exists:

Create a Table in SQL Server with T-SQL

Result: After running the query, click ‘Refresh’ Button the new table exists as a Course.

Create a Table in SQL Server with T-SQL

Create a Table in SQL Server with T-SQL

Step 2) Insert some data

Till now, the Table only exists, but there is no data in the table created yet.

Transact-SQL has the INSERT statement that can be used to insert data into a table. With this statement, we can insert either one or more columns into a table.

Syntax

INSERT INTO tableName
(column_1, column_2, ... )
VALUES
(expression_1, expression_2, ... ),
(expression_1, expression_2, ... ),
...;

The above shows the basic syntax of the command when using the VALUES keyword to insert data into a table.

Let’s insert four rows using Insert command.

Insert into COURSE values (1,'SQL');
Insert into COURSE values (2,'Python');
Insert into COURSE values (3,'SQL');
Insert into COURSE values (4,'C');

Below snapshot shows that now insertion of four rows is successful.

Create a Table in SQL Server with T-SQL

Step 3) Use the SELECT statement to view Data
To view data in SQL Server, Copy following syntax:

SELECT expression
FROM tableName
[WHERE condition];

Example Query:

SELECT * FROM COURSE;

Result: Table got created, and there are four records in tables. Note that we can create records with a duplicate value in any of the columns as there are no constraints.

Create a Table in SQL Server with T-SQL

T-SQL: Create a New Table using an existing table.

Now say we want another table like COURSE table created above. However, we need only one column of Course_Name and not Course_ID.

Syntax

SELECT (Column 1, …) INTO <New Table name> FROM <Old Table name>;

Example Query:

SELECT COURSE_NAME INTO COURSE_NAMES FROM COURSE;

Step 1) Run the query by clicking on Execute.

Create a New Table using an Existing Table

Result: New table created with the name as COURSE_NAMES with existing 1 column and four records from Older Table.

SELECT * FROM COURSE_NAMES;

Create a New Table using an Existing Table

Using Table Designer

We can also create Table from Studio Management IDE using Table Designer.

Step 1) Right Click on Table>New>Click on Table.

Create a Table in SQL Server Using Table Designer

Step 2) Enter column names, data types. Select whether to allow nulls or not.

Create a Table in SQL Server Using Table Designer

Step 3) Press ‘CTRL+S’ to Save the table. Enter Name as ‘Course_Title’

Create a Table in SQL Server Using Table Designer

Result: Click on ‘Refresh’ and we can seeNew table exists with the name as Course_Title with one column named as Course_Name.

Create a Table in SQL Server Using Table Designer

Create a Table in SQL Server Using Table Designer

Alter Table in SQL Server

There are two ways to Alter Table in SQL server.

1. T-SQL: Alter Table by adding new columns.

2. Using Table designer

T-SQL: Alter Table by inserting columns

Below is the syntax of Alter command in SQL:

Syntax:

 Alter TABLE <Table name> ADD Column1 datatype, Column2 datatype;

Query:

ALTER TABLE dbo.Course_Title ADD Course_Duration VARCHAR(20);

Step 1) Run the Query by Clicking on Execute.

Alter Table by Inserting Columns

Result: New column exists with the name as Course_Duration within the Course_Title Table.

Alter Table by Inserting Columns

Using Table designer

We can also Alter Table from UI using Table Designer.

Step 1) Right Click on the existing Table>Design.

Alter Table Using Table Designer

Step 2) Add Column Name as Course_Start_Date and select Datatype.

Alter Table Using Table Designer

Step 3) Press ‘CTRL+S’ to Save the newly added column.

Result: New Column is created with the name as Course_Start_Date in Course_Title table.

Alter Table Using Table Designer

Delete Table in SQL Server

We delete the table when it is not required anymore.

There are two ways to Delete Table in SQL server.

1. Using SQL Server Management Studio.

2. T-SQL: Delete Table.

Using SQL Server Management Studio

Step 1) Right Click on existing Table>Delete. Table Name ‘Course_Title’

Delete Table Using SQL Server Management Studio

Step 2) On ‘Delete Object’ window, Click OK.

Delete Table Using SQL Server Management Studio

Result: ‘Course_Title’ table got deleted from the Table list.

Delete Table Using SQL Server Management Studio

T-SQL:Delete Table

Below is the Syntax to Drop table

Syntax

DROP TABLE <tableName>;

The parameter tableName is the name of the table that is to be deleted.

Query:

DROP TABLE COURSE_NAMES;

Step 1) Run the Query by Clicking on Execute.

Delete Table

Result: ‘Course_Name’ table got deleted from the Table list.

Delete Table

Alternatively, you can also use the DELETE TABLE command. But, it will delete only rows (data). Table structure will remain intact. The command is intended to truncate the table so it can be used later.

Syntax

DELETE TABLE <Table name>;

Interesting Facts!

  • We can also store big files like .xml in a column as BLOB, CLOB datatype.
  • Delete can roll back, but Drop cannot be rollback.

Summary

  • Delete table only deletes all the rows, but the table structure still exists. We can again insert new rows.
  • The drop will permanently delete the Table structure, and hence we cannot insert new rows.