ALTER TABLE COLUMN DATA TYPE command rename the existing column datatype in a table.Only table owner, DBA,SYSADMIN and a user with ALTER privilege can excute the ALTER command. How to change the datatype of column using Query.If a table contain more than 20,000 rows and you can not delete it or you can not create a new existing table.So, there are different ways to change the data type of column but the best one is ALTER COLUMN statement.

Overview

1. Syntax
2. Create Table
3. Insert Records
4. Check Table Schema
5. Modify Column Data Type
6. Data type cannot be alter
7. Download
   

Syntax

ALTER TABLE [TABLE NAME]
ADD [COLUMN_NAME]  [DATA TYPE]

1. [TABLE NAME]
   •   Table name to modify the existing column
2. [COLUMN_NAME]
   
   •  column name to rename
3. [DATA TYPE]
   
   • new data type for alter column

Create Table

Create CREDITCARD_INFORMATION table. You have notice in Table struce that charges data type is INT , that mean that you can store only interger values in a charges column

--DROP TABLE CREDITCARD_INFORMATION

CREATE TABLE CREDITCARD_INFORMATION
    (
      [ID] BIGINT IDENTITY ,
      [FIRSTNAME] VARCHAR(50) ,
      [LASTNAME] VARCHAR(50) ,
      [COUNTRY] VARCHAR(100) ,
      [DATE] DATETIME ,
      CHARGES INT
    )

Insert records

Let's Insert some record in CreditCard_Information Table

INSERT INTO CREDITCARD_INFORMATION VALUES('AAMIR','HASAN','PAKISTAN',GETDATE(),1)
INSERT INTO CREDITCARD_INFORMATION VALUES('AWAIS','AHMED','PAKISTAN',GETDATE(),22)
INSERT INTO CREDITCARD_INFORMATION VALUES('BILL','GATE','AMERICA',GETDATE(),33.3333)
INSERT INTO CREDITCARD_INFORMATION VALUES('SOBIA','AHMED','PAKISTAN',GETDATE(),6.546)
INSERT INTO CREDITCARD_INFORMATION VALUES('IMRAN','KHAN','INDIAN',GETDATE(),9000)
INSERT INTO CREDITCARD_INFORMATION VALUES('SANA','HINA','CANADA',GETDATE(),65.0222)
INSERT INTO CREDITCARD_INFORMATION VALUES('OMARI','ZAHRA','AFRICA',GETDATE(),69.36)
INSERT INTO CREDITCARD_INFORMATION VALUES('AJANI','ZAHRA','AMERICA',GETDATE(),45.36)
INSERT INTO CREDITCARD_INFORMATION VALUES('ASHIA','HASAN','AFRICA',GETDATE(),66.66)
INSERT INTO CREDITCARD_INFORMATION VALUES('TANESHA','DAKARAI','INDIA',GETDATE(),5599.99)

SELECT * FROM CREDITCARD_INFORMATION

As you see, that  some float values are inserted into charges column but it store only interger values becasue  data type of charges column is INT as showing in below figure.

Check Table Schema

SP_HELP CREDITCARD_INFORMATION

Modify Column Data Type

ALTER TABLE CREDITCARD_INFORMATION
ALTER COLUMN CHARGES DECIMAL(17,2)

INSERT INTO CREDITCARD_INFORMATION VALUES('ASHIA','HASAN','AFRICA',GETDATE(),86.696)
INSERT INTO CREDITCARD_INFORMATION VALUES('TANESHA','DAKARAI','INDIA',GETDATE(),55699.99)

SELECT * FROM CREDITCARD_INFORMATION

SP_HELP CREDITCARD_INFORMATION

Data type cannot be Alter

Text, image, ntext, or timestamp data type can not be Altered. IMAGE Data type can not be modify to NVARCHAR  as you see in below figure.

Download

Alter column data type.sql (1.93 kb)

For more information Visit Mircosoft MSDN

http://msdn.microsoft.com/en-us/library/aa275462%28SQL.80%29.aspx