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. Let’s start the discussion with the following questions.
What is SQL?
- It is a non-procedural language which is used to communicate with any database such as Oracle, SQL Server etc.
- This Language was developed by the German Scientist Mr. E.F.Codd in 1968
- ANSI (American National Standard Institute) approved this concept and in 1972 SQL was released into the market
- SQL is also called Sequel it stands for Structured English Query Language,
- 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.
- SQL is not a case-sensitive language it means that all the commands of SQL are not case sensitive
- Every command of SQL should end with a semicolon (;) (It is optional for SQL Server)
- 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
- DDL (5 commands- create, alter, sp_rename, drop, truncate)
- DML (3 commands- insert, update, Delete).
- DQL/ DRL (1 command- select).
- TCL (3 commands- commit, rollback, savepoint)
- DCL (2 commands- Grant, Revoke).
Data Definition Language (DDL):
- 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
- DDL commands are working on the structure of a table, not on data of a table.
- 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:
- This command is used to create a new database object in a database such as tables, views, functions, etc.
- All database objects are saved with an extension of “dbo.<object name>”.
CREATE TABLE <TABLE NAME> (<COLUMN NAME1> <DATA TYPE> [SIZE]……… <COLUMN NAME N><DATA TYPE> [SIZE])
CREATE TABLE student ( studid INT, sname VARCHAR(max), salary DECIMAL(6, 2) )
Rules for creating a table in SQL Server:
- Table name should be unique under a database.
- Column name should be unique within the table definition.
- Table name should not start with numeric and special characters except (-) underscore symbol.
- Don’t provide space in the table name. If you want to provide space in a table name then you can use underscore symbol.
- A table name should contain minimum 1 character and maximum 128 characters.
- A table should contain minimum 1 column and a maximum of 1024 columns.
Alter Command in SQL Server:
- It is used to change or modify the structure of a table.
- By using alter command we can perform the following operations on an existing table such as
- Alter command can increase/decrease the width of a column.
- It can change the data type of a column.
- It can change the NOT NULL to NULL or NULL to NOT NULL.
- Alter command can add a new column.
- Alter command can drop an existing column.
- We can add a new constraint.
- It can drop an existing constraint on a table.
- Disable or re-enable check constraint of a table.
- 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
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.
ALTER TABLE <TABLENAME> ALTER COLUMN <COLUMNNAME> <NEW DATA TYPE>[NEW SIZE]
Q1. To 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.
Q2. Changing the data type of an existing column.
ALTER TABLE student ALTER COLUMN sname NVARCHAR(100)
Q3. Changing the column NULL to NOT NULL.
ALTER TABLE student ALTER COLUMN sno INT NOT NULL
Q4. Changing NOT NULL to NULL.
ALTER TABLE student ALTER COLUMN sno INT NULL
It is used for adding a new column to an existing table.
ALTER TABLE <TABLENAME> ADD <NEWCOLUMNNAME> <DATA TYPE>[NEW SIZE]
ALTER TABLE student ADD class INT
ALTER TABLE student ADD grade INT NOT NULL
ALTER – DROP:
This command is used to drop an existing column of a table.
ALTER TABLE <TABLENAME> DROP COLUMN <COLUMNNAME>
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 new column name.
SP_RENAME ‘<TABLE NAME>.<OLD COLUMN NAME>’,‘NEW COLUMN NAME’
This command is also used to change a table name from the old table name to a new table name.
SP_RENAME ‘OLD TABLE NAME’,’NEW TABLE NAME’
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 truncate command will not support “where” clause.
The truncate command will delete rows but not the structure of the table.
TRUNCATE TABLE <TABLENAME>
TRUNCATE TABLE STUDENT //all rows are deleted
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.
DROP TABLE <OBJECT NAME>
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?
|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 will discuss Data Types in SQL Server.
In this article, I try to explain creating altering and deleting tables in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.