Open In App

Table operations in MS SQL Server

Last Updated : 17 Sep, 2020
Like Article

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 –

Name Rollnumber Course
Aisha 111 CSE
Naina 112 ECE

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 –

Name Rollnumber Course Age
Aisha 111 CSE 18
Naina 112 ECE 19

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 –

Name Rollnumber  Course
Aisha 111 CSE
Naina  112 ECE

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

Like Article
Suggest improvement
Share your thoughts in the comments

Similar Reads