Creating Altering and Deleting Tables in SQL server

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 details. 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 delete and truncate command in SQL Server?
What is SQL?
  1. It is a non-procedural language which 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 common language interface facility it means that a sequel is a language which can communicate with any type of databases 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 as 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 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 details.

Create Command in SQL Server:
  1. This command is used to create a new database object in a database such as tables, views, functions, etc.
  2. All database objects are saved with an extension of “dbo.<object name>”.

Syntax: CREATE TABLE <TABLE NAME> (<COLUMN NAME1> <DATA TYPE> [SIZE].. <COLUMN NAME N><DATA TYPE> [SIZE])

Example:

CREATE TABLE student 
( 
    studid INT, 
    sname  VARCHAR(max), 
    salary DECIMAL(6, 2) 
) 
Rules for creating a table in SQL Server:
  1. Table name should be unique under a database.
  2. Column name should be unique within the table definition.
  3. Table name should not start with numeric and special characters except (-) 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 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:
  1. It is used to change or modify the structure of a table.
  2. By using alter command we can perform the following operations on an existing table such as
  3. Alter command can increase/decrease the width of a column.
  4. It can change the data type of a column.
  5. It can change the NOT NULL to NULL or NULL to NOT NULL.
  6. Alter command can add a new column.
  7. Alter command can drop an existing column.
  8. We can add a new constraint.
  9. It can drop an existing constraint on a table.
  10. Disable or re-enable check constraint of a table.
  11. Changing a column name in the table.
Example: let’s create the following table to understand all these concepts
CREATE TABLE student 
( 
    sno   INT, 
    sname VARCHAR(50) 
) 

The above operations we can perform by using alter subcommands. Such as

ALTER-ALTER COLUMN

This command is used to change a data type from 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]

Examples: Change the width of a column sname on student table

ALTER TABLE student ALTER COLUMN sname VARCHAR(100) 

When we increase the width of a column we don’t have 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.

Example: Changing the data type of an existing column.

ALTER TABLE student ALTER COLUMN sname NVARCHAR(100) 

Example: Changing the column NULL to NOT NULL.

ALTER TABLE student ALTER COLUMN sno INT NOT NULL 

Example: Changing NOT NULL to NULL.

ALTER TABLE student ALTER COLUMN sno INT NULL

Alter Add Command in SQL Server:

It is used for adding a new column to an existing table.

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

Example1: ALTER TABLE student ADD class INT 

Example2: ALTER TABLE student ADD grade INT NOT NULL 

Alter Drop Command in SQL Server:

This command is used to drop an existing column of a table.

Syntax: ALTER TABLE <TABLENAME> DROP COLUMN <COLUMNNAME>

Example: ALTER TABLE STUDENT DROP COLUMN FEES

SP_RENAME Command in SQL Server:

SP stands for the stored procedure and this stored procedure is used to change a column from an old column name to a new column name.

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

Example: SP_RENAME ‘STUDENT.SNAME’, ‘STUDENTNAME’

This command is also used to change a table name from the old table name to a new table name.

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

Example: SP_RENAME ‘STUDENT’,’STUDENT DETAILS’

Truncate Command in SQL Server:

It is used to delete all rows from the table. By using this command we cannot delete a specific row from the table because the truncate command will not support “where” clause. The truncate command will delete rows but not the structure of the table.

Syntax: TRUNCATE TABLE <TABLENAME>

Example: TRUNCATE TABLE STUDENT //all rows are deleted

Example: TRUNCATE TABLE STUDENT WHERE STID = 103 //Not Allowed

Drop Command in SQL Server:

This command is used to drop the entire table structure from the database.

Syntax: DROP TABLE <OBJECT NAME>

Example: DROP TABLE STUDENT

When a table is dropped all the dependent constraint on 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 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 WHERE clause
It is a temporary deletion It is a permanent deletion
Delete supports rollback transaction 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 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.

Leave a Reply

Your email address will not be published. Required fields are marked *