---
description: In this tutorial, you will learn How to Create a Table, Alter Table, Drop (Delete) Table in SQL Server using T-SQL with Examples.
title: SQL Server CREATE, ALTER, DROP Table [T-SQL Examples]
image: https://www.guru99.com/images/sql-server-table-create-alter-drop.png
---

 

[Skip to content](#main) 

## What is a Table?

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

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa1.png)

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](https://www.guru99.com/ms-sql-server-tutorial.html)

### 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.

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa2.png)

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

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa3.png)

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

[ ](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa4.png)[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa5.png)

**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.

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa6.png)

**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.

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa7.png)

### 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.** 

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa8.png)

**Result:** New table created with the name as **COURSE\_NAMES** with existing 1 column and four records from Older Table.

SELECT * FROM COURSE_NAMES;

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa9.png)

### 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.**

### RELATED ARTICLES

* [SSRS Tutorial: What is SQL Server Reporting Services? ](https://www.guru99.com/ssrs-tutorial.html "SSRS Tutorial: What is SQL Server Reporting Services?")
* [SQL Server Data Types with Examples ](https://www.guru99.com/sql-server-datatype.html "SQL Server Data Types with Examples")
* [SQL Variables: SQL Server Declare, Set and Select Variable ](https://www.guru99.com/sql-server-variable.html "SQL Variables: SQL Server Declare, Set and Select Variable")
* [JOINS in SQL Server: Tutorial with Examples ](https://www.guru99.com/sql-server-joins.html "JOINS in SQL Server: Tutorial with Examples")

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa10.png)

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

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa11.png)

**Step 3)** Press ‘CTRL+S’ to Save the table. Enter Name as **‘Course\_Title’**

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa12.png)

**Result:** Click on ‘Refresh’ and we can seeNew table exists with the name as **Course\_Title** with one column named as **Course\_Name**.

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa13.png)

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa14.png)

## 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.**

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa15.png)

**Result:** New column exists with the name as **Course\_Duration** within the **Course\_Title Table**.

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa16.png)

### Using Table designer

We can also Alter Table from UI using Table Designer.

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

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa17.png)

**Step 2)** Add Column Name as Course\_Start\_Date and select Datatype. 

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa18.png)

**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.

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa19.png)

## 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](https://www.guru99.com/sql-server-management-studio.html).

2\. **T-SQL:** Delete Table.

### Using SQL Server Management Studio

**Step 1)** Right Click on existing Table>Delete. Table Name ‘Course\_Title’

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa20.png)

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

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa21.png)

**Result: ‘Course\_Title’** table got deleted from the Table list.

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa22.png)

### 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.

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa23.png)

**Result: ‘Course\_Name’** table got deleted from the Table list.

[](https://www.guru99.com/images/1/030819%5F0814%5FSQLServerTa24.png)

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.

#### Summarize this post with:

ChatGPT Perplexity Grok Google AI 

**Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** 

##### Sign up for the newsletter

Subscribe for Free 

You have successfully subscribed.  
Please check your inbox. 

![AI-Newsletter]() Chosen by over **350,000+** professionals 

[Scroll to top ](#wrapper)Scroll to top 

× 

Toggle Menu Close 

Search for: 

Search

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/sql-server-table-create-alter-drop.png","url":"https://www.guru99.com/images/sql-server-table-create-alter-drop.png","width":"450","height":"309","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/sql-server-table-create-alter-drop.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/sql-server","name":"SQL Server"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/sql-server-table-create-alter-drop.html","name":"SQL Server CREATE, ALTER, DROP Table [T-SQL Examples]"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/sql-server-table-create-alter-drop.html#webpage","url":"https://www.guru99.com/sql-server-table-create-alter-drop.html","name":"SQL Server CREATE, ALTER, DROP Table [T-SQL Examples]","dateModified":"2024-06-28T15:48:51+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/sql-server-table-create-alter-drop.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/sql-server-table-create-alter-drop.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/fiona","name":"Fiona Brown","description":"I'm Fiona brown, a Full Stack Developer with over a decade of experience, sharing practical guides on robust and scalable application development.","url":"https://www.guru99.com/author/fiona","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/fiona-brown-author.png","url":"https://www.guru99.com/images/fiona-brown-author.png","caption":"Fiona Brown","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"image":{"@id":"https://www.guru99.com/images/sql-server-table-create-alter-drop.png"},"headline":"SQL Server CREATE, ALTER, DROP Table [T-SQL Examples]","description":"In this tutorial, you will learn How to Create a Table, Alter Table, Drop (Delete) Table in SQL Server using T-SQL with Examples.","keywords":"sqlserver, sql","@type":"Article","author":{"@id":"https://www.guru99.com/author/fiona","name":"Fiona Brown"},"dateModified":"2024-06-28T15:48:51+05:30","copyrightYear":"2024","name":"SQL Server CREATE, ALTER, DROP Table [T-SQL Examples]","articleSection":"SQL Server","subjectOf":[{"@type":"HowTo","name":"How to Create a Table in SQL Server with T-SQL","description":"Following is a step by step process on How to Create a Table in SQL Server with T-SQL","step":[{"@type":"HowToStep","name":"Step 1) Run the query by clicking on Execute","text":"Before running the query, no table exists:","image":"https://cdn.guru99.com/images/1/030819_0814_SQLServerTa3.png","url":"https://www.guru99.com/sql-server-table-create-alter-drop.html#step1"},{"@type":"HowToStep","name":"Step 2) Insert some data","text":"Till now, the Table only exists, but there is no data in the table created yet.","image":"https://cdn.guru99.com/images/1/030819_0814_SQLServerTa6.png","url":"https://www.guru99.com/sql-server-table-create-alter-drop.html#step2"},{"@type":"HowToStep","name":"Step 3) Use the SELECT statement to view Data","text":"To view data in SQL Server,Copy following syntax:","image":"https://cdn.guru99.com/images/1/030819_0814_SQLServerTa7.png","url":"https://www.guru99.com/sql-server-table-create-alter-drop.html#step3"}]}],"@id":"https://www.guru99.com/sql-server-table-create-alter-drop.html#schema-49485","isPartOf":{"@id":"https://www.guru99.com/sql-server-table-create-alter-drop.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/sql-server-table-create-alter-drop.html#webpage"}}]}
```