SQL SERVER – Change Column DataTypes

There are times when I feel like writing that I am a day old in SQL Server. In fact, there are many who are looking for 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 for 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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

19 thoughts on “SQL SERVER – Change Column DataTypes

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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.

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

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #051 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s