0

I want to change my column data type from "Datetime" to "Datetime2(7)". If I try to do it with the GUI SQL Server 2014 asks to save all FKs table dependencies and then I get a lock timeout because this table has millions of rows. Is this possible while not losing the FKs dependencies and any data?

Table Design

It is a Test environment. The reason I want to do this is because I think I'm getting locks because of datetime precision (milliseconds). I have many rows with the same datetime and not ordered.

  • Have you tried using T-SQL statement? ALTER TABLE [TABLE_NAME] ALTER COLUMN [COLUMN_NAME] datetime2; – Rajesh Ranjan Sep 12 at 10:54
  • I'm getting locks because of datetime precision - are you joining on a datetime column, or filtering for an exact point in time? I'm trying to understand how more precision is going to solve the problem (as opposed to just making it slightly different). Anyway the usual approach to something like this is to drop the foreign keys, then change the tables, then put the foreign keys back (and you can do this one by one to temper impact). How viable that is will depend on whether you can take any downtime at all or how much you trust your processes to not insert bad data in the meantime. – Aaron Bertrand Sep 12 at 11:04
  • 1
    datetime to datetime2 should be an online operation, because there is no data loss, so it should work like increasing the size of a varchar column, but that's not how it works today. In any case, doing it one by one should be faster than any process that tries to protect all the tables involved simultaneously (and, honestly, never try to do this kind of thing with the GUI, especially with big tables). – Aaron Bertrand Sep 12 at 11:19
  • 1
    @AaronBertrand, I haven't tested this either but I think it is a fundamental data type change because datetime2 has a different structure than datetime. That will require updating every row. I completely the GUI isn't the right tool for this job. – Dan Guzman Sep 12 at 11:30
  • @Dan yeah you might be right about the structure change. – Aaron Bertrand Sep 12 at 12:06
3

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.

  • Is it possible to do this if the modified column is part on a FK constraint? – ypercubeᵀᴹ Sep 12 at 17:50
  • 1
    @ypercubeᵀᴹ No, you would have to drop the FK first. – Josh Darnell Sep 12 at 18:20
  • 1
    Thnx. But you can do it inside a transaction, right? (Drop the FK, Alter the 2 columns' type, Add the FK back). Asking because I'm not sure. – ypercubeᵀᴹ Sep 12 at 18:24
  • 1
    @ypercubeᵀᴹ you'd also have to drop the unique or primary key constraint on the table that the FK points to. But yeah, you can do all of it in a transaction! – Josh Darnell Sep 12 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.