Many people use the table designer component in SQL Management Studio for creating and altering objects in SQL Server. I've never used it and I'm more comfortable in using T-SQL queries for doing these activities. Anyways many of my colleagues uses it. Last day one of them came to me and told about an issue they were facing with it.
We had a table in our development database and they were trying to modify a table column. Suddenly they got the below error.
Since it was development server all had the access rights to drop,create and alter tables. So I was surprised why this error was popping up.
I went ahead with my approach to use query editor and was able to do the same change using ALTER TABLE..ALTER COLUMN syntax. This had me perplexed for a while as I didnt understand why table designer was not allowing the change to happen.
After some hours of research I found out the reason. The secret lies in one of the default settings within SQL Management Studio options shown below.
Go to Tools-> Options from top menu and navigate to Table and Database Designers settings coming under Designers and you'll see the below
See the setting in blue which is what prevents saving any changes through designer which causes table to be recreated. Clear the setting and you would be able to do above type of object modifications through the designer, though I myself doesn't prefer it to be done through designer.
We had a table in our development database and they were trying to modify a table column. Suddenly they got the below error.
I went ahead with my approach to use query editor and was able to do the same change using ALTER TABLE..ALTER COLUMN syntax. This had me perplexed for a while as I didnt understand why table designer was not allowing the change to happen.
After some hours of research I found out the reason. The secret lies in one of the default settings within SQL Management Studio options shown below.
Go to Tools-> Options from top menu and navigate to Table and Database Designers settings coming under Designers and you'll see the below
See the setting in blue which is what prevents saving any changes through designer which causes table to be recreated. Clear the setting and you would be able to do above type of object modifications through the designer, though I myself doesn't prefer it to be done through designer.
No comments:
Post a Comment