aspx Tutorial

.NET Articles,jQuery demo, asp.net with jQuery, online tutorial,Jquery, SilverLight, Javascript, asp.net,JSON, MVC,.NET Articles,demo, Web Services, .NET articles, Sharepoint 2010, visual studio 2010,Aamir Hasan,IT, Building Your First Web Application Project
Advertise Here

Toolbar

Get our toolbar!

Advertize



Posted by Aamir Hasan   on Thursday, July 1, 2010 Total Views:  

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


Protected by Copyscape Online Plagiarism Tool

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Advertizement 1
Advertizement 2
Advertizement 3
Advertizement 4
Advertizement 5