SQL SERVER – Change Column DataTypes

There are times when I feel like writing that I am a day older in SQL Server. In fact, there are many who are looking for a solution that is simple enough. Have you ever searched online for something very simple. I often do and enjoy doing things which are straight forward and easy to change.

In this blog post, we will see how to change the datatypes of the column.

We will create a sample table and change the column datatypes.

USE tempdb
GO
CREATE TABLE dbo.SampleTable
(ID INT, FirstCol VARCHAR(10), SecondCol DATETIME)
GO
ALTER TABLE dbo.SampleTable
ALTER COLUMN ID VARCHAR(100)
GO
ALTER TABLE dbo.SampleTable
ALTER COLUMN FirstCol VARCHAR(100)
GO
ALTER TABLE dbo.SampleTable
ALTER COLUMN SecondCol VARCHAR(100)
GO
sp_help 'SampleTable'
GO

In our example, it was possible to change the data types easily. If you face any errors, as per your error, you will have to adjust your business logic.

When running the last SP, you will note that you have successfully converted all the datatypes to another datatype.

Let me know what you think of this blog post and please share your thoughts in the comment section of the blog post.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – System Stored Procedure sys.sp_tables
Next Post
SQLAuthority News – SQL Server Performance Optimizations Seminar – Grand Success – Colombo, Sri Lanka – Oct 4 – 5, 2010

Related Posts

29 Comments. Leave new

  • Pinal it is easy to change any dattype to varchar but converting varchar to a numeric value is really painful thing as I was there before

    Reply
  • Abhimanyu Kumar Vatsa
    December 22, 2011 1:37 am

    thanks, saved my blog downtime. due to some reason ASP.NET Enterprise Manager changed my entire varchar datatype to text and it causes some section down in my blog but finally Google Maharaaz sent me here and fixed.

    Thanks

    Reply
  • Hi Pinal

    It’s looks good but how about chaning data type dynamically. for eg, I am creating a dynamic table and then i need to convert to user defined datatype. How i can change datatypes

    ALTER TABLE dbo.Tbl_Temp
    ALTER COLUMN IDD @DataType

    How i can achive this, without writing SET @Sqlstr = ‘ALTER TABLE DBO.TBL_TEMP ALTER COLUMN IDD ‘ + @DATATYPE’
    EXEC (@SQLSTR)

    I DON’T WANT TO WRITE DYANMIC GENRATING STRING. IS THERE ANY OTHER WAY TO DO IT ?

    THANKS

    Reply
  • Chandru (@vchandm23)
    July 20, 2012 7:23 am

    If i do an alter on column data type, will it automatically change the datatype that was referred in the any of the stored procedures? Or do we need to go and change them manually?

    Cheers,
    Chandru

    Reply
  • how to change datafield field values without using change,modify,alter commands

    Reply
  • Can you advise how I can change the column which is the primay key

    Reply
  • How can I change data type from decimal to decimal(2,3)

    Reply
  • What if you want to change a column type for varchar(50) to bit (default 0), where the existing data is either ”, NULL, or ‘Locked’.

    where ” or NULL should be set to 0
    and ‘Locked’ set to 1

    Reply
  • Anandkumar Devane
    April 3, 2013 5:25 pm

    Hi Pinal Dave,

    How to change the column type of a primary key column if it contains the data.

    Reply
  • nilesh chaubey
    May 27, 2013 1:19 pm

    How can we drop the primary key from the coloumn and also recreate the ppirmary key after change the datatype.

    how can we create the script for that particular changes

    Reply
  • what if i had already some data in that column say id column (int) value 10. Now i want to change datatype to var char without losing data.

    Reply
  • Hi Pinal ,
    I want to change most of the columns datatype…is there any script to change it in hole colums at a time..

    Reply
  • Vinod sharma
    June 24, 2013 2:50 pm

    I m satisfied from this command .

    Reply
  • hi pinal ,,,
    I want to create a procedure which contains sysobjects that should be stored in temp table .. at the same time it should perform operations like insert,alter,update, delete . Here the entire code should be in dynamic sql . Please give me reply for this ….

    thanks & regards

    Shah Gummaduru.

    Reply
    • Your requirement is not clear to me. I guess you should try in MSDN SQL forums as I might not get time to write a script for you.

      Reply
  • Hi Pinal,

    I want to alter the datatype of a primary key column without dropping the primary key constraint and recreating the constraint again.

    can you please help me on this…

    Reply
  • syedayaz@digitecsystems.com
    September 21, 2015 3:00 pm

    Error

    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

    Reply
  • Hi pinal
    if i want to add a new column named E-Mail to my SQL table and i want to add only E-Mail formats to this column and strictly forbidden any other entry;
    what data type or formula should i add here?
    thanks in advance for help!!

    Reply
  • Hi pinal,
    i try to change timestamp datatype to datetime. but i can’t change. i got below the error.

    Msg 4928, Level 16, State 1, Line 1
    Cannot alter column ‘timestamp’ because it is ‘timestamp’.

    Thanks in advance for help me!!

    Reply
  • Bangalee Kamara
    February 25, 2016 7:01 pm

    WOW…This is fantastic no doubt. What I do want to know is altering a primary key column. I have tried doing it but, I’m catching a little difficult time.

    Reply
  • Bangalee Kamara
    February 25, 2016 7:27 pm

    could I get regular courses from this site

    Reply

Leave a Reply

Menu