Creating Altering and Deleting Tables in SQL server

Back to: SQL Server Tutorial For Beginners and Professionals

Creating Altering and Deleting Tables in SQL server

In this article, I am going to discuss Creating Altering and Deleting Tables in SQL Server with examples. Along the way, we will also discuss the Data Definition Language in detail. Please read our previous article before proceeding to this article where we discussed the Creating Altering and Deleting Database in SQL Server with examples. As part of this article, we are going to discuss the following important points.

1. What is SQL?
2. Understanding SQL Sub Languages
3. Data Definition Language in SQL Server.
4. Create Command in SQL Server
5. Alter Command in SQL Server
6. Truncate Command in SQL Server.
7. Drop Command in SQL Server.
8. What are the differences between the delete and truncate commands in SQL Server?

What is SQL?

1. It is a non-procedural language that is used to communicate with any database such as Oracle, SQL Server, etc.
2. This Language was developed by the German Scientist Mr. E.F.Codd in 1968
3. ANSI (American National Standard Institute) approved this concept and in 1972 SQL was released into the market
4. SQL is also called Sequel it stands for Structured English Query Language,
5. The sequel will provide a common language interface facility it means that a sequel is a language that can communicate with any type of database such as SQL Server, Oracle, MySQL, Sybase, BD2, etc.
6. SQL is not a case-sensitive language it means that all the commands of SQL are not case sensitive
7. Every command of SQL should end with a semicolon (;) (It is optional for SQL Server)
8. SQL can be called NLI (Natural Language Interface). It means that all the SQL Commands are almost similar to normal English language

SQL Sub Languages:

SQL contains the following sublanguages

1. DDL (5 commands- create, alter, sp_rename, drop, truncate)
2. DML (3 commands- Insert, Update, Delete).
3. DQL/ DRL (1 command- select).
4. TCL (3 commands- commit, rollback, savepoint)
5. DCL (2 commands- Grant, Revoke).

Data Definition Language (DDL):

1. Data Definition Language (DDL) is used to define database objects such as tables, synonyms, views, procedures, functions, triggers, etc. that means DDL statements are used to alter/modify a database or table structure and schema
2. DDL commands are working on the structure of a table, not on the data of a table.
3. This language contains five commands. Those are (CREATE, ALTER, SP_RENAME, TRUNCATE, DROP)

Let’s discuss each of these Data Definition Language commands in detail.

Create Command in SQL Server:

The CREATE command is used to create a new database object in a database such as tables, views, functions, etc. In SQL Server, all database objects (tables, views, etc) are saved with an extension of “dbo.<object name>”. The syntax to create a database is shown below. 

Example: The following Create command creates a table called Student.

CREATE TABLE student 
( 
    studid INT, 
    sname  VARCHAR(max), 
    salary DECIMAL(6, 2) 
) 

Rules for creating a table in SQL Server:

While creating a database in SQL Server, you need to follow the below rules.

1. The table name should be unique under a database.
2. The column name should be unique within the table definition.
3. A Table name should not start with numeric and special characters except the (-) underscore symbol.
4. Don’t provide space in the table name. If you want to provide space in a table name then you can use the underscore symbol.
5. A table name should contain a minimum of 1 character and a maximum of 128 characters.
6. A table should contain a minimum of 1 column and a maximum of 1024 columns.

Alter Command in SQL Server:

This command is used to change or modify the structure of a table. In SQL Server, using the ALTER command we can perform the following operations on an existing table.

1. Increase/decrease the width of a column.
2. Change the data type of a column.
3. Change the NOT NULL to NULL or NULL to NOT NULL.
4. Used to add a new column to an existing table.
5. Used to drop an existing column.
6. We can add a new constraint.
7. It can drop an existing constraint on a table.
8. Disable or re-enable check constraint of a table.
9. Changing a column name in the table.

Example:

To understand the use of ALTER command in SQL Server, let’s first create the following Student table.

CREATE TABLE Student 
( 
    No   INT, 
    Name VARCHAR(50) 
) 

ALTER-ALTER COLUMN:

This command is used to change a data type from an old data type to a new data type and also to change the size of a data type of a column.

Syntax: ALTER TABLE <TABLENAME> ALTER COLUMN <COLUMNNAME> <NEW DATA TYPE>[NEW SIZE]

Change the width of a column 

Let see how we can change the width of a column using the Alter command. In our student table, the column Name width is VARCHAR(50). Let’s change the width to VARCHAR (100). To do so, we need to use the Alter command as shown below.

ALTER TABLE Student ALTER COLUMN Name VARCHAR(100) 

Note: When you increase the width of a column, you won’t face any problem but while decreasing the width if the table contains data in it we cannot decrease the width less than the max existing characters in the column.

Changing the data type of an existing column.

If you want to change the data type of an existing column, then you can use the ALTER command. For example, currently, the Name column data type is VARCHAR and our requirement is to change the data type from VARCHAR to NVARCHAR. To do so, you need to use the ALTER command as shown below.

ALTER TABLE Student ALTER COLUMN Name NVARCHAR(100) 

Changing the column NULL to NOT NULL.

If you want to change a NULL column to NOT NULL then you can use the ALTER command. When you create a column without NULL or NOT NULL constraint, then by default it is NULL. It means this column can accept NULL values.  Suppose, you want to change the No column from NULL to NOT NULL, then you need to use the ALTER Command as shown below.

ALTER TABLE Student ALTER COLUMN No INT NOT NULL 

Changing NOT NULL to NULL.

If you want to change a NOT NULL column to NULL then also you can use the ALTER command. For example, if you want to change the No column from NOT NULL to NULL, then you can use the ALTER Command as shown below.

ALTER TABLE Student ALTER COLUMN No INT NULL

Adding a new column to an existing table in SQL Server:

If you want to add a new column to an existing table, then you can use the ALTER Command. The syntax to use the command to add a new column is given below. 

ALTER TABLE <TABLENAME> ADD <NEWCOLUMNNAME> <DATA TYPE>[NEW SIZE]

Suppose, you want to add the Branch column to the existing Student table. Then you need to use the ALter command as shown below.

ALTER TABLE Student ADD Branch VARCHAR(20) 

Deleting Column in SQL Server:

If you want to delete an existing column from a table in SQL Server, then you need to use the ALTER Command. The syntax to use the ALTER command to delete or drop a column is given below. 

ALTER TABLE <TABLENAME> DROP COLUMN <COLUMNNAME>

For example, if you want to delete or drop the Brach column from the Student table, then you need to use the ALTER command as shown below.

ALTER TABLE Student DROP COLUMN Branch

SP_RENAME Command in SQL Server:

SP stands for the stored procedure and here SP_RENAME is a system-defined stored procedure. This stored procedure is used to change the column name. The syntax to use this procedure is given below/

SP_RENAME ‘<TABLE NAME>.<OLD COLUMN NAME>’, ‘NEW COLUMN NAME’

Suppose, you want to change the name column from Name to StudentName, then you need to use this stored procedure as shown below.

SP_RENAME ‘Student.Name’, ‘StudentName‘

This SP_RENAME stored procedure can also be used to change a table name from the old table name to a new name. The syntax to change the table name using SP_RENAME stored procedure is given below.

SP_RENAME ‘OLD TABLE NAME’, ‘NEW TABLE NAME’

For example, if you want to change the table name from Student to StudentDetails, then you need to use the SP_RENAME stored procedure as shown below:

SP_RENAME ‘Student‘, ‘StudentDetails’

Truncate Command in SQL Server:

Whenever you want to delete all the records or rows from a table without any condition, then you need to use the Truncate command in SQL Server. So, using this command you cannot delete specific records from the table because the truncate command does not support the “where” clause. The syntax to use the TRUNCATE command is given below.

TRUNCATE TABLE <TABLENAME>

Suppose, you want to delete all the records from the Student table, then you need to use the TRUNCATE command as shown below in SQL Server.

TRUNCATE TABLE Student

Note: The truncate command will delete rows but not the structure of the table.

Drop Command in SQL Server:

If you want to delete the table from the database, then you need to use the DROP command in SQL Server. The syntax to use the DROP command is given below.

DROP TABLE <OBJECT NAME>

Suppose, you want to delete the Student table from the database, then you need to use the DROP command as shown below.

DROP TABLE Student

Note: When a table is dropped all the dependent constraint which are associated with the table also gets dropped. We cannot drop a master table.

What are the differences between Delete and Truncate Command in SQL Server?

Delete	Truncate
It is a DML command.	It is a DDL command
By using the delete command we can delete a specific record from the table.	But it is not possible with truncate command.
Delete supports WHERE clause.	Truncate does not support the WHERE clause
It is a temporary deletion	It is a permanent deletion
Delete supports rollback transactions for restoring the deleted data.	Truncate doesn’t support rollback transaction so that we cannot restore the deleted information
Delete command will not reset identity property.	But it will reset the identity property

In the next article, I am going to discuss the Data Types in SQL Server with examples. Here, in this article, I try to explain creating altering, and deleting tables in SQL Server step by step with some examples. I hope you enjoy this article.