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
- Syntax
- Create Table
- Insert Records
- Check Table Schema
- Modify Column Data Type
- Data type cannot be alter
- Download
Syntax
ALTER TABLE [TABLE NAME]
ADD [COLUMN_NAME] [DATA TYPE]
- [TABLE NAME]
- Table name to modify the existing column
- [COLUMN_NAME]
- [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