How can I change data type of a column in SQL Server?

Question

I am using SQL Server 2008 R2.

I want to change the data type of a column (Size) from decimal to int. First, I used designer of SQL Server but SQL Server did not allow me to do it. Please attention to the picture.

Then I decided to write a script. But I got an error.

1. How can I do it by using a SQL script?

2. How can I do it by db-migration?

   I've used Entity Framework 6.x, C# and code-first approach for creating database. I want to preserve my data in Tag table.

3. Is there better tool than db-migration? I think it's boring!

My model:

namespace Jahan.Blog.Model
{
   public class Tag : Entity, ITag
   {
    [Range(1, 4)]
    public virtual int Size { get; set; } //In db data type of Size is decimal.I want to change it to int.

    [Required]
    [StringLength(25)]
    public virtual string Title { get; set; }

    [StringLength(256)]
    public virtual string Description { get; set; }

    public virtual bool IsActive { get; set; }

    public virtual ISet<ArticleTag> ArticleTags { get; set; }       // FK

    public virtual ISet<ProjectTag> ProjectTags { get; set; }       // FK
   }
}

SQL script:

USE [Jahan-Blog]
GO

/****** Object:  Table [dbo].[Tag]    Script Date: 10/23/2014 16:17:09 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tag]') AND type in (N'U'))
DROP TABLE [dbo].[Tag]
GO

USE [Jahan-Blog]
GO

/****** Object:  Table [dbo].[Tag]    Script Date: 10/23/2014 16:17:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Tag](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](25) NOT NULL,
    [Description] [nvarchar](256) NULL,
    [IsActive] [bit] NOT NULL,
    [Size] [int] NOT NULL,
    [CreatedDate] [datetime] NULL,
    [ModifiedDate] [datetime] NULL,
 CONSTRAINT [PK_dbo.Tag] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

I got this error after running the script.

Msg 3726, Level 16, State 1, Line 4
Could not drop object 'dbo.Tag' because it is referenced by a FOREIGN KEY constraint.

Msg 2714, Level 16, State 6, Line 2
There is already an object named 'Tag' in the database.

DbMigrations configuration:

namespace Jahan.Blog.Web.Mvc.Migrations
{
using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;

//For knowing more information about db-migration please visit http://msdn.microsoft.com/en-gb/data/jj591621
internal sealed class Configuration 
    : DbMigrationsConfiguration<Jahan.Blog.Web.Mvc.Models.JahanBlogDbContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = false;
    }

    protected override void Seed(Jahan.Blog.Web.Mvc.Models.JahanBlogDbContext context)
    {
    }
   }
}

Answer 1

To be able to make your changes in SQL Server designer, on the Tools menu, click Options, expand Designers, and then click Table and Database Designers. Clear the Prevent saving changes that require the table to be re-created check box.

Answer 2

To change the type with SQL run the following

Alter table dbo.[Tag] alter column [Size] int NOT NULL

To change the type through an EF migration use

AlterColumn("dbo.Tag", "Size", c => c.Int(nullable: false));