Sql-server – Changing Column Data Type with FKs

It's possible to change the data type without data loss or breaking FK dependencies. It depends on whether you have some downtime where you can execute the required change though.

Changing from datetime to datetime2(7) will require every row in the table / indexes to be updated, since these two types are stored differently on disk. This can take a little time, depending on how many rows your table has (you said "millions").

The change would look like this in T-SQL (you should definitely do things like this in T-SQL rather than the GUI, by the way):

ALTER TABLE dbo.YourTable ALTER COLUMN CreationMoment datetime2(7);

The ALTER statement here requires SCH-M (schema modification) locks: on the table being changed, and on the tables that have foreign key relationships to this one. That means that this statement will wait until there are no concurrent transactions before it's able to run.

Once it starts running, it will also block any concurrent activity on the tables until it completes.

Here's a toy example that creates a table with 10,000,000 rows with dates, and another table with 1,000 rows referencing the first table:

USE [master];
GO
IF EXISTS (SELECT null FROM sys.databases d WHERE d.[name] = '248612') 
    DROP DATABASE [248612];
GO

CREATE DATABASE [248612];
GO
USE [248612];

CREATE TABLE dbo.DateTest
(
    Id int IDENTITY PRIMARY KEY NOT NULL,
    SomeDate datetime NOT NULL
);

CREATE TABLE dbo.OtherTable
(
    Id int IDENTITY PRIMARY KEY NOT NULL,
    DateTestId int NOT NULL,

    CONSTRAINT FK_OtherTable_DateTestId 
        FOREIGN KEY (DateTestId) 
        REFERENCES dbo.DateTest (Id)
);

INSERT INTO dbo.DateTest
SELECT TOP (10000000)
    DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 364 ), '2019-01-01')
FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2
    CROSS JOIN master.dbo.spt_values v3;

INSERT INTO dbo.OtherTable
SELECT TOP (1000)
    Id
FROM dbo.DateTest;

That code takes about 30 seconds to run on my machine. With no other concurrent activity, the ALTER statement takes about 10-15 seconds:

ALTER TABLE dbo.DateTest ALTER COLUMN SomeDate datetime2(7);

Ideally, you could do this kind of thing on a test system to assess about how long the blocking will be in production once the ALTER begins.

Regarding my "concurrent activity" statements, if I open a transaction and update a row in dbo.DateTest, the ALTER won't even start. It sits and waits on the SCH-M lock that it needs, which you can see by running sp_WhoIsActive @get_locks = 1:

<Database name="248612">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="DateTest" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

If I commit or rollback that update transaction, the ALTER starts, and I can see that it takes out locks on the DateTest and OtherTable tables (abbreviated XML here):

<Database name="248612">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="DateTest" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="FK_OtherTable_DateTestId" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="OtherTable" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

This will block writes on the tables involved until the ALTER completes.