Sometimes we put the wrong data type in a column while creating a table in SQL Server, and later we want to update that column with a different data type, how do we do that? The first things that come to my mind are ALTER TABLE statement, but again I don't remember the exact syntax. Another thing that could go wrong is one ALTER statement working in MySQL may not work in SQL Server or Oracle. To deal with this, I am writing these tutorials to not only remember exact syntax but also about alternatives and other important details. By the way, while changing the data type of an existing column in a table, there are a couple of things consider like does your table has a huge amount of data? If yes, then it might take a long time to update the table structure, on the other hand in an empty table updating the data type of a column is instantaneous. It would hardly take a couple of milliseconds.
Coming back on how do we actually update the data type of an existing column on SQL Server, my first guess was right, we can change the data type by using ALTER TABLE table_name ALTER COLUMN column_name statement. You need to provide the name of the table and column.
How to modify (increase or decrease) data type of a column in SQL
Here is an example of changing the datatype of a column from int to varchar in SQL Server.
ALTER TABLE Instrument ALTER COLUMN lotSize varchar(50)
What else should you consider while changing the data type of an existing column in a table in Microsoft SQL Server 2008, 2012 or 2014 express edition:
1) Whether the table is empty or contains a lot of data? If empty then it's easy, otherwise, it will take some time.
2) Datatype should be compatible e.g. if you convert a varchar column to a numeric one, data must be numeric and easily convertible to numbers.
One more case where you want to change the data type of column is to increase the size of varchar or precision of numeric data type e.g. One of the columns in a somewhat large table (~10,000 records) is the data type DECIMAL(10,0). I'd like the values to be displayed to 2 decimal places, so I need to alter this to DECIMAL(10,2), without screwing up the table's existing records.
How could this be done?
The good thing about SQL Server is that it got an excellent tool in terms of SQL Server Management Studio, which can help you in almost all the things you want to do with your database, tables, and stored procedures.
The good thing about SQL Server is that it got an excellent tool in terms of SQL Server Management Studio, which can help you in almost all the things you want to do with your database, tables, and stored procedures.
By the way, you should remember that when you modify the data type of a column, Table Designer applies the default length of the data type you selected, even if you have already specified another. Always set the data type length to the desired value after specifying the data type.
Also If you attempt to change the data type of a column that relates to other tables, Table Designer will also ask you to confirm that the change should be made to the columns in the other dependent tables as well.
Here are the steps to change the data type of column using SQL Server Management Studio:
In Object Explorer, right-click the table with columns for which you want to change the scale and click Design.
Select the column for which you want to modify the data type.
In the Column Properties tab, click the grid cell for the Data Type property and choose a new data type from the drop-down list.
On the File menu, click Save table name.
In Object Explorer, right-click the table with columns for which you want to change the scale and click Design.
Select the column for which you want to modify the data type.
In the Column Properties tab, click the grid cell for the Data Type property and choose a new data type from the drop-down list.
On the File menu, click Save table name.
Things to remember while modifying existing column of a table having records
It's easy to modify, change the data type, size or rename an existing column in a new or empty table, but when it comes to updating the live database, containing tables with large records e.g. (10K to 10M), it's simply not easy, in the worst case this exercise may lead to data loss.You should consider the following things while updating the data type of the existing column in SQL Server:
1. Always remember that modifying the data type of a column that already contains data can result in the permanent loss of data when the existing data is converted to the new type. To avoid this take a dump of the production table in your UAT environment and run the query there. Compare before and after data for that column. By the way, this method cannot guarantee a full-proof success if your table data is updating on daily basis.
2. Apart from the risk of permanent data loss, code and applications that depend on the modified column may fail. These include queries, views, stored procedures, user-defined functions, and client applications. Note that these failures will cascade. For example, a stored procedure that calls a user-defined function that depends on the modified column may fail. Carefully consider any changes you want to make to a column before making it.
3 Always ask a database administrator to do the change in the production database and even simple changes like changing the column data type of empty table. Why? because you need ALTER permission on the table, which you might not have on a production database. Verify this before doing a release, as they usually happened on a weekend when support is low.
4. Make sure that your data can be converted into a new data type and fits into a given length before making a change. For example, you can change the type of a column from int to bool without losing existing data because any value of 0 will be converted into a 0 (BIT = false), and anything else will be turned into 1 (BIT = true).
5. Another safe approach to achieve the same effect of modifying the data type of column is to a new column of a new type, fill it in from the old column, and once you're done, drop the old column and rename the new one to the old name. That way, if something during the conversion goes wrong, you can always go back since you still have all the data.
1. Always remember that modifying the data type of a column that already contains data can result in the permanent loss of data when the existing data is converted to the new type. To avoid this take a dump of the production table in your UAT environment and run the query there. Compare before and after data for that column. By the way, this method cannot guarantee a full-proof success if your table data is updating on daily basis.
2. Apart from the risk of permanent data loss, code and applications that depend on the modified column may fail. These include queries, views, stored procedures, user-defined functions, and client applications. Note that these failures will cascade. For example, a stored procedure that calls a user-defined function that depends on the modified column may fail. Carefully consider any changes you want to make to a column before making it.
3 Always ask a database administrator to do the change in the production database and even simple changes like changing the column data type of empty table. Why? because you need ALTER permission on the table, which you might not have on a production database. Verify this before doing a release, as they usually happened on a weekend when support is low.
4. Make sure that your data can be converted into a new data type and fits into a given length before making a change. For example, you can change the type of a column from int to bool without losing existing data because any value of 0 will be converted into a 0 (BIT = false), and anything else will be turned into 1 (BIT = true).
5. Another safe approach to achieve the same effect of modifying the data type of column is to a new column of a new type, fill it in from the old column, and once you're done, drop the old column and rename the new one to the old name. That way, if something during the conversion goes wrong, you can always go back since you still have all the data.
I agree this is a longer process than directly updating the data type of a particular column but it's much safer if data loss is a premier concern. This can become even more tedious if you are modifying more than one column of the table, so choose what suits your need and convenience.
That's all about how to make a change on the data type of the existing column in SQL Server. You should now understand that there are many ways to do it depending on your preference, you can either use SQL script or Management Studio to do this change. Beware, though, with the risk of changing the data type of the existing column in a table that contains data. In the worst case, you may lose your data permanently if they are not compatible or cannot be converted into a new data type.
That's all about how to make a change on the data type of the existing column in SQL Server. You should now understand that there are many ways to do it depending on your preference, you can either use SQL script or Management Studio to do this change. Beware, though, with the risk of changing the data type of the existing column in a table that contains data. In the worst case, you may lose your data permanently if they are not compatible or cannot be converted into a new data type.
Always run your SQL Script on test or QA environment before running on production and make sure it behaves as expected. Changing data type can also break other application which depends upon modified column e.g. stored procedure, views, queries, and user-defined function, make sure you have identified dependency before modifying the column.
If you are doing this in production then you can also check the table structure before updating and table structure after updating. You can take the screenshot and save that in your PIT or post implementation test document.
The tips I have shared here can not only be used to modify existing column in SQL Server but also update existing column like to change size of an existing column, I mean increasing or decreasing the size of VARCHAR column or changing precision of NUMERIC column.
Other SQL Articles and Tutorials You may like
- How to join three tables in one single SQL query (solution)
- Write a SQL query to find all table names on a database in MySQL (solution)
- 5 Courses to learn Database and SQL Better (courses)
- The real difference between WHERE and HAVING clause in SQL? (answer)
- Top 5 Courses to learn Microsoft SQL Server in-depth (courses)
- What is the difference between View and Materialized View in Database? (answer)
- Difference between clustered and non-clustered indexes in SQL? (answer)
- Difference between Primary and Candidate key in the table? (answer)
- 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
- Difference between Self and Equi Join in SQL? (answer)
- How to migrate SQL queries from Oracle to SQL Server? (answer)
- Top 5 Websites to learn SQL online for FREE? (resource)
- What is the difference between UNION and UNION ALL in SQL? (answer)
- Write a SQL query to copy or backup a table in MySQL (solution)
- How do you find the duplicate rows in a table on a database? (solution)
- 5 Advanced SQL books to level up your SQL skills (books)
- Top 5 Courses to learn MySQL Database for Beginners (Courses)
- 4 Free Books to learn Microsoft SQL Server database (books)
- Top 5 Websites to learn SQL online for FREE? (websites)
- 5 Free Courses to learn Database and SQL (free courses)
- 5 Free Courses to learn Oracle and SQL Server? (courses)
- 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
- Difference between the Unique and Primary keys in the table? (answer)
- Top 5 Courses to learn PostgreSQL in-depth (courses)
- 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
- Difference between Primary and Foreign keys in the table? (answer)
Thanks for reading this article so far. If you like these SQL Server tutorial to update the size of existing column
then please share with your friends and colleagues. If you have any
questions or feedback then please drop a note.
P. S. - If you want to learn SQL Server in depth and looking for online courses and tutorials then you can also check out these best TSQL and SQL Server courses. These are the best online courses you can get to learn SQL Server and tools like SSMS in depth.
No comments :
Post a Comment