In this post will discuss changing database table columns without dropping the table. Let’s understand this with an example we will be using table TblDoctors for this example.
Notice that this table has got Id DoctorName, gender, salary, and SpecializationId columns.And I have used the below create table script to create that table.
And if you notice the salary column data type is nvarchar(50).
This is the SQL script to populate it with some sample data.
Let’s say based on the table, we want to write a query which is going to list the total salaries of doctors grouped by gender. So we want the output to be like the below image.
So let’s write a query for that.
if we try to execute the query, it will give an error.
So basically this error message is stating we cannot use the salary column, with Sum() aggregate function. because it’s data type is nvarchar. So we need to change the data type of salary column from nvarchar to float,int or decimal.
And one way to do that within the object explorer. Right-click on the table TblDoctors click design and notice the data type of the salary column is nvarchar, let’s change it to Integer and let’s try to save it and see what’s going to happen.
we get a warning. Saving changes is not permitted. Basically, this is telling us we need to drop and recreate the table.
And if you remember, this table has already got some data as well. So if we drop and recreate it, we are going to lose that data. So how do we get rid of this warning message?
Basically there are two ways.
we can use a SQL query to alter the column. so instead of using the SQL server designer, we can simply use a SQL query.
So let’s execute that and see if it works as expected. Look at that. It works as expected. So this is one option.
2 .Option 2 is basically to disable option prevent saving changes that require a table recreation in SQL server.
All we need to do is disable that option. And to do that, within the tool select options and then expand designers and under that table and database designers and uncheck the checkbox, prevent saving changes that require table creation.
- Now you can change the column datatype from designer also. let’s try
Right-click on table ->select designer
- and then let’s try to change the salary datatype to decimal.
- Let’s save the changes and see where it works.
The post How to change database table columns without dropping the table appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?