Alter Table Columns And Datatypes Without Deleting The Table

Introduction

There is no need to delete a whole table in case of further updates in Table column datatypes and any new added columns.

Description

In most cases, real time scenarios are based on the requirement that the new columns should be added or change the data type or data type range values.

 

Steps to follow

Create a table. 

Create Table tblContribution
(
 Article nvarchar(50),
 Author nvarchar(50),
 Counts int
)

Insert some dummy records.

 

 

 

Then execute the below query.

select * from tblContribution

There are a total of 3 columns. I need an extra  "TodayDate" column with data type "datetime" except SQL query." Now, there is a column with data type "datetime" except SQL query.

 

 

 

We got some error, as shown above during new added columns.

 

In this case, we should write some SQL query. 

alter table tblContribution add TodayDate datetime

Now, execute the query given below. We will get a new column with the data type.

select * from tblContribution

 

 

Again, without SQL query, we should change the data type of "TodayDate" column data type "datetime" to "datetimeoffset". 

 

 

We got some error like above during column datatype change and range value.

 

In this case, we should write some SQL query.

ALTER TABLE [tblContribution] ALTER COLUMN [TodayDate] datetimeoffset

See the table structure, what has happened.

 

 

By executing SQL statement mentioned below, we will get the details of the table updated status.

sp_help 'tblContribution'

 

Summary

1. Why we are getting error messages while adding new columns, changing datatype and range value of an existing table.
2. How to prevent this by writing SQL query.
3. How to alter table columns and datatypes and range values without deleting the table.