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)
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
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
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
No there is no way other than dynamic sql
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
Chandru that would be nice, but I don’t think its that easy just yet. Maybe some day.
how to change datafield field values without using change,modify,alter commands
Did you mean datatype or column values?
Can you advise how I can change the column which is the primay key
How can I change data type from decimal to decimal(2,3)
Alter table table_name alter column column_name decimal(12,2)
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
I think a case statement would be helpful in this case..right..?
Hi Pinal Dave,
How to change the column type of a primary key column if it contains the data.
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
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.
Hi Pinal ,
I want to change most of the columns datatype…is there any script to change it in hole colums at a time..
I m satisfied from this command .
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.
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.
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…
you can’t have two primary keys.
Error
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
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!!
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!!
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.
could I get regular courses from this site