Table operations in MS SQL Server

In a relational database, the data is stored in the form of tables and each table is referred to as a relation. A table can store a maximum of 1000 rows. Tables are a preferred choice as:

• Tables are arranged in an organized manner. 
• We can segregate the data according to our preferences in the form of rows and columns.
• Data retrieval and manipulation become easier.
• You can identify if there are any repetitive data. 
• You can also add new columns without interrupting the previous data in the table.

A table has a combination of rows and columns. Rows are called records and columns are called fields. In MS SQL Server, the tables are being designated within the database and schema names. 

The syntax for naming the table –

create table[db_name] [schema_name] table_name col1 datatype,......); 

Example –

create table student 
(
name varchar2(30),
rollnumber int primary key, 
course varchar2(10)
);

A table with the name student will be created in the database. A few values can be inserted as follows.

insert into student values('Aisha', 111, 'CSE');
insert into student values('Naina', 112, 'ECE');

Output –

The values are now inserted using the insert keyword. There are other operations that can be performed on the table:

Alter table add column :

A student table has so many attributes in it. In case a user wants to add a new column, it can be done as follows.

alter table table_name add column_name datatype column_constraint;

Suppose a user wants to add age of student in the student table, it can be done as follows.

alter table student add age int; 

The age column will be created. The user can insert age in the table as follows:

insert into student values('Aisha', 111, 'CSE', 18);
insert into student values('Naina', 112, 'ECE', 19);

Output –

Alter table drop column :

There are some columns that remain unused or rarely during the life cycle of a database. In order to delete them from the table, It has to be done as follows.

alter table table_name drop column column_name;

In the student table, age is one attribute that is unused. To delete the column, the query must be given as follows:

alter table student drop column age;

Output –

It removes the data from database records. It means the data is permanently removed and cannot be retrieved again.