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 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));