If you’re fairly new to SQL Server and you’re working in the SQL Server Management Studio, you might run into this problem when you try and make a change to the structure of a table.
Some changes are allowed, in which case you won’t see this message, but if you try and change the settings for Nulls, change the data type of a column or add a new column, you’ll probably fall foul of it. The first thing to know is that this message – and the banning of the proposed change – is there for a good reason. Depending on other factors, these kind of changes can cause the loss of data and/or metadata. So, annoying though you might find it at first, it’s there to help you.
OK, that’s all well and good, but you’re still sat there wanting to make some change and you certainly don’t want to start recreating your database all over from scratch just to do that. Let’s look at a couple of options.
First of all, let’s assume that you are still in the stage where you’re building a new database and you haven’t actually entered any data. Maybe you looked at the columns and thought to yourself “I don’t need 50 chars for the Zip or PostCode column, so I’ll reduce it”. This will be a change of data type and so will cause the error message above. In this particular situation where you are still building the database structure, you can tweak one of the SSMS options to allow you to make the change.
The steps are as follows: First select Tools –> Options from the SSMS main menu:
Then, in the Options dialog that appears, select the Table and Database Designers item and uncheck the Table Option of ‘Prevent saving changes that require table re-creation’. Then click OK.
If you now try and make the change, SSMS will allow you to make it and save it. Please make sure you re-check the option after you’ve made your change.
In all other circumstances, I believe that it’s better not to use this option. The recommended approach is to use a T-SQL query that will make the alteration you need. Now, if you’re really new to SQL Server, your head’s probably already reeling from all the things that can (and often do) go wrong just getting things set up. So you might not want to have to go and learn yet something new. But in this case it’s worth the effort.
If by some fantastic coincidence, you’ve landed on this blog because you do actually want to change the data type of a column, well you’re in luck, because here’s the query you would use to make the change:
In this case I’m altering the data type of the column named Zip so that it’s a varchar(10).
The first line identifies the database name. This isn’t always necessary, because you can select the database in SSMS as shown here:
But if you’re like me and often forget to change this from the default, then it’s worth including that first line.
The second line identifies the table that’s to be altered. The third line makes the change as I’ve described above. The final line is probably redundant in this case, but I like to include it on a just-in-case basis, in case I later expand a query and want this first part actioned before it moves on to other tasks.
Some changes are allowed, in which case you won’t see this message, but if you try and change the settings for Nulls, change the data type of a column or add a new column, you’ll probably fall foul of it. The first thing to know is that this message – and the banning of the proposed change – is there for a good reason. Depending on other factors, these kind of changes can cause the loss of data and/or metadata. So, annoying though you might find it at first, it’s there to help you.
OK, that’s all well and good, but you’re still sat there wanting to make some change and you certainly don’t want to start recreating your database all over from scratch just to do that. Let’s look at a couple of options.
First of all, let’s assume that you are still in the stage where you’re building a new database and you haven’t actually entered any data. Maybe you looked at the columns and thought to yourself “I don’t need 50 chars for the Zip or PostCode column, so I’ll reduce it”. This will be a change of data type and so will cause the error message above. In this particular situation where you are still building the database structure, you can tweak one of the SSMS options to allow you to make the change.
The steps are as follows: First select Tools –> Options from the SSMS main menu:
Then, in the Options dialog that appears, select the Table and Database Designers item and uncheck the Table Option of ‘Prevent saving changes that require table re-creation’. Then click OK.
If you now try and make the change, SSMS will allow you to make it and save it. Please make sure you re-check the option after you’ve made your change.
In all other circumstances, I believe that it’s better not to use this option. The recommended approach is to use a T-SQL query that will make the alteration you need. Now, if you’re really new to SQL Server, your head’s probably already reeling from all the things that can (and often do) go wrong just getting things set up. So you might not want to have to go and learn yet something new. But in this case it’s worth the effort.
If by some fantastic coincidence, you’ve landed on this blog because you do actually want to change the data type of a column, well you’re in luck, because here’s the query you would use to make the change:
In this case I’m altering the data type of the column named Zip so that it’s a varchar(10).
The first line identifies the database name. This isn’t always necessary, because you can select the database in SSMS as shown here:
But if you’re like me and often forget to change this from the default, then it’s worth including that first line.
The second line identifies the table that’s to be altered. The third line makes the change as I’ve described above. The final line is probably redundant in this case, but I like to include it on a just-in-case basis, in case I later expand a query and want this first part actioned before it moves on to other tasks.
0 comments:
Post a Comment