SqlDataReader.GetSqlDateTime does not support DATETIME2

[bjornbouetsmith]

Describe the bug

It seems like DATETIME2 sql server datatype is not fully or correctly implemented in SqlDataReader.

using method GetDateTime - it handles DATETIME2 correctly

using method GetSqlDateTime - it throws an
System.InvalidCastException:

Exception message: Specified cast is not valid 
Stack trace:  at Microsoft.Data.SqlClient.SqlBuffer.get_SqlDateTime()

To reproduce

 [TestMethod]
        public void DateTime2Mapping()
        {
            string Statement = $@"SELECT CAST('2020-12-11 09:17:12.123456' AS DATETIME2) AS [MyDate]";
            
            var builder = new SqlConnectionStringBuilder()
            {
                InitialCatalog = "master",
                DataSource = @"any sql connection",
                IntegratedSecurity = true,
            };

            var sqlConnection = new SqlConnection(builder.ConnectionString);
            sqlConnection.Open();
            using (SqlCommand command = new SqlCommand(Statement))
            {
                command.CommandType = CommandType.Text;
                command.Connection = sqlConnection;
                command.CommandText = Statement;
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var dtColDate = reader.GetSqlDateTime(0);
                        Assert.IsFalse(dtColDate.IsNull);
                        Console.WriteLine(dtColDate.Value);
                    }
                }
            }
        }

Expected behavior

I expect that GetSqlDateTime handles DATETIME2 and returns a SqlDateTime struct

Further technical details

Microsoft.Data.SqlClient version: 2.1.0
.NET target: Framework 4.7.2
SQL Server version: 15.0.4079.3 (2019)
Operating system: Windows 10

Additional context
Why I found this issue, is because I was profiling our code and saw that SqlDataReader.IsDbNull took up a lot of time since we need to check every column for null, since GetXX methods does not handle DBNull - and then I found SqlDataReader.GetSqlXX methods which handles DBNull.

Basically trying to get rid of the overhead in SqlDataReader

 override public bool IsDBNull(int i)
        {
            {
                CheckHeaderIsReady(columnIndex: i);

                SetTimeout(_defaultTimeoutMilliseconds);

                ReadColumnHeader(i);    // header data only
            }

            return _data[i].IsNull;
        }

by swithing to
The IsNull property in the SqlTypes

i.e.

  public bool IsNull => !this.m_fNotNull;

[JRahnama]

@bjornbouetsmith DateTime2 cannot be casted as SqlDateTime. The source of the issue comes from the difference in DateTime2 and SqlDateTime. I tested some changes in the driver to convert the dates and since the DateTime2 Range is from 0001-01-01 through 9999-12-31 to an accuracy of 100 nanoseconds and SqlDateTime Range is January 1, 1753 to December 31, 9999 to an accuracy of 3.33 milliseconds and there is no exact conversion in C# to do so I got wrong results. The SqlDateTime property in SqlBuffer inside the driver casts the value to SqlDateTime and that throws the exception, However you can get the value using GetDateTime with no problem. In addition, the casting can happen in server side or in your command as explained here.

[bjornbouetsmith]

Hi,
That might be true, but then you need a corresponding .NET struct that supports the extended range, i.e. a SqlDateTime2.
Right now the support for DATETIME2 is by my opinion only partially implemented, since there are a lot of advantage to using the GetSqlXX methods instead of using the GetXX methods.

The former do not throw exceptions if the column is NULL, GetXX methods do - so I would still suggest that you either "Fix" SqlDateTime to handle the extended range of DATETIME2 - or implement a SqlDateTime2 to handle the DATETIME2 and then naturally add a GetSqlDateTime2 method.

When reading millions of rows from the database where every single column can be null, it is a major performance degredation to use GetXX methods instead of GetSqlXX methods, since you need to check every single column on ever single row with first IsDBNull and then do something depending on the return value. With GetSqlXX methods they never fail because the row is null and you can call the IsNull property directly on the return value, which is a more than an order of 10 times faster because of the overhead in IsDBNull.

Most of the code to handle DATETIME2 is already implemented in GetDateTime, so it should be only a matter of either implementing a GetSqlDateTime2 or fixing GetSqlDateTime to handle the extra range by extending SqlDateTime.

I would prefer you implemented a GetSqlDateTime2 - since that would complete the API, since there is a DATETIME2 data type in the database without a corresponding Sql class in .NET. Which would probably require adding a SqlDateTime2 property to the SqlBuffer struct.

I will be happy to submit a pull request to this effect.

And casting to DateTime is not really an option on the SQL server, since that would truncate the precision.

[ErikEJ]

There are APIs to get the nullability from a reader before calling the get methods, so you can avoid calling IsDbNull

[bjornbouetsmith]

@ErikEJ What API is that?

SqlDataReader.IsDbNull is what I am using, and that is not performant, since it does a lot of checks.

And I need to call for each field on every row, so I have found no API, except SqlDataReader.IsDbNull, Convert.IsDbNull and the IsNull property of the SqlXX types.

Convert.IsDbNull is useless, since that will BOX any value type, which will cause garbage, SqlDataReader is a performance HOG because it needs to check for the correct state of the reader before accessing the field. Which unless I have missed something leaves me with the IsNull property of the SqlXX types.

I am not interested in an API that can tell me whether or not a column "can" be null - I already know ALL columns can be null, but a performant way to check if any field is null.

[ErikEJ]

GetColumnSchema or GetSchemaTable

[bjornbouetsmith]

@ErikEJ Yes, but that will only tell me the schema of the table, which I don't need and cannot use - I already know all columns can be null - I need to handle null values because SqlClient will throw exception if the field is null. Where the GetSqlXX methods will not throw exceptions, but instead return a struct with a IsNull property. So the only real way to manage null values if you care about performance and garbage collections.

[Wraith2]

Can you provide an isolated test case that exhibits the behaviour you see around null checks and direct type access causing more garbage than using the sql types. I've always assumed when working on the internals that the sql types would be more costly to use but if they're not I can investigate if there are ways to equalize the performance of the paths.

[bjornbouetsmith]

@Wraith2 I never said my issue was around garbage - but only with performance, since the SqlClient is missing a SqlDateTime2 or equivalent class to represent DATETIME2.

if you use DATETIME2 - you are forced to use SqlDataReader.GetDateTime - and since that method does not gracefully handle null, you are then forced to first call SqlDataReader.IsDbNull.

The codepath for IsDbNull does a lot of work, which is required because it needs to check if the state is correct to be able to check for null. And then it ends up by accessing a property on SqlBuffer which is basically just a field accessor telling it whether or not data is null. If there was a SqlDateTime2 struct/GetSqlDateTime2, I would not have to call SqlDataReader.IsDbNull and then GetSqlDateTime - I could just use the performant way of just retrieving the SqlDateTime2 struct and call the IsNull property and if false, return the data contained in the struct.

using my "testcase" from above - I am sure you will be able to see the issue running this code below.
Of course the code below is silly, but imagine it being a 200 column row all having the possiblity of null and having 50k rows.

for (int x = 0; x < 10000000; x++)
{
	DateTime dateTime;
	if (!reader.IsDBNull(0))
	{
		// Do something
		dateTime = reader.GetDateTime(0);
	}
	else
	{
		dateTime = default;
	}

}
//vs below code which fails with an InvalidCastException
// so you have to imagine calling a GetSqlDateTime2 which returned a SqlDateTime2
// or imagine that SqlDateTime would be able to handle the extended range of DATETIME2 and not throw exceptions

for (int x = 0; x < 10000000; x++)
{
	DateTime dateTime;
	SqlDateTime sqlDateTime = reader.GetSqlDateTime(0);
	if (!sqlDateTime.IsNull)
	{
		dateTime = sqlDateTime.Value;
	}
	else
	{
		dateTime = default;
	}

}

[roji]

@bjornbouetsmith

When reading millions of rows from the database where every single column can be null, it is a major performance degredation to use GetXX methods instead of GetSqlXX methods, since you need to check every single column on ever single row with first IsDBNull and then do something depending on the return value.

Is this something you're actually seeing in production, or have benchmarked? Because a quick benchmark seems to show that the perf of GetSqlDateTime is the same as IsDBNull + GetDateTime (see below). After all, I'd expect GetSqlDateTime to have to do more or less the same null checking internally as you're doing externally when calling GetDateTime.

Benchmark results:

BenchmarkDotNet=v0.12.1, OS=ubuntu 20.04
Intel Core i7-9750H CPU 2.60GHz, 1 CPU, 12 logical and 6 physical cores
.NET Core SDK=5.0.101
  [Host]     : .NET Core 3.1.10 (CoreCLR 4.700.20.51601, CoreFX 4.700.20.51901), X64 RyuJIT
  DefaultJob : .NET Core 3.1.10 (CoreCLR 4.700.20.51601, CoreFX 4.700.20.51901), X64 RyuJIT

Method	IsNull	Mean	Error	StdDev
GetDateTime	False	139.7 us	2.68 us	4.48 us
GetSqlDateTime	False	137.2 us	2.73 us	5.58 us
GetDateTime	True	124.4 us	2.38 us	3.25 us
GetSqlDateTime	True	124.2 us	2.45 us	3.35 us

Benchmark code

public class Program
{
    const string ConnectionString = @"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0";

    [Params(true, false)]
    public bool IsNull { get; set; }

    SqlConnection _connection;
    SqlCommand _command;

    [GlobalSetup]
    public void Setup()
    {
        _connection = new SqlConnection(ConnectionString);
        _connection.Open();
        _command = new SqlCommand(IsNull
                ? "SELECT CAST(NULL AS datetime)"
                : "SELECT CAST('2020-01-01' AS datetime)",
            _connection);
    }

    [GlobalCleanup]
    public void Cleanup()
    {
        _connection.Dispose();
        _command.Dispose();
    }

    [Benchmark]
    public DateTime GetDateTime()
    {
        using var reader = _command.ExecuteReader();
        reader.Read();
        return reader.IsDBNull(0) ? default : reader.GetDateTime(0);
    }

    [Benchmark]
    public SqlDateTime GetSqlDateTime()
    {
        using var reader = _command.ExecuteReader();
        reader.Read();
        return reader.GetSqlDateTime(0);
    }

    static void Main(string[] args)
        => BenchmarkRunner.Run<Program>();
}

[ErikEJ]

Should it not be:

      _command = new SqlCommand(IsNull
            ? "SELECT CAST(NULL AS datetime2)"
            : "SELECT CAST('2020-01-01' AS datetime2)",

[bjornbouetsmith]

@ErikEJ Yes that would have been great - except using GetSqlDateTime does not support DATETIME2 - which is my whole problem.

[bjornbouetsmith]

@roji Yes I have profiled this and could see that IsDbNull was the method using the most time.

And I have no idea how that benchmark you made runs, but if you call ExecuteReader, and only read one field from that reader, the isolated performance of IsDbNull will drown in the connection overhead etc.

So I would suggest if you want to test like that, that you open the reader once, and then call reader.IsDbNull/GetDateTime 10million times inside a loop and do the same with GetSqlDateTime.

that would give you the performance testing in question - and only measure the time of reader.IsDbNull/GetDateTime - not the time it takes to open the connection and execute the query.

Using this simple test shows a huge performance difference on my machine:

IsDbNull:00:00:04.1302114
GetSqlDateTime:00:00:02.6796905
IsDbNull:00:00:04.1251925
GetSqlDateTime:00:00:02.7111852

And reading 10million+ very wide rows where all fields can be null for performance reasons, then yes it makes a big difference.

I considered "fixing" the issue by making my stored procedure use ISNULL, but that would defeat the purpose of having null for performance reasons, since then suddenly the sql server would have to transfer a value instead of just a bit stating the field was null - so we would use more bandwith/time just reading the data. So I am still convinced that having a GetSqlDateTime method that either handles DATETIME2 - or having a GetSqlDateTime2 method is the correct solution.

string Statement = $@"SELECT CAST('2020-12-11 09:17:12.123' AS DATETIME) AS [MyDate]";

var builder = new SqlConnectionStringBuilder()
{
    InitialCatalog = "master",
    DataSource = @"(LocalDb)\AggService",
    IntegratedSecurity = true,
};
int count=100000000;
var sqlConnection = new SqlConnection(builder.ConnectionString);
sqlConnection.Open();
using (SqlCommand command = new SqlCommand(Statement))
{
    command.CommandType = CommandType.Text;
    command.Connection = sqlConnection;
    command.CommandText = Statement;
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            var dummy=reader.GetDateTime(0);
            var dummy2 = reader.GetSqlDateTime(0);
            Stopwatch sw = Stopwatch.StartNew();

            for (int x = 0; x < count; x++)
            {
                DateTime dt;
                if (!reader.IsDBNull(0))
                {
                    dt = reader.GetDateTime(0);
                }
                else
                {
                    dt = default;
                }
            }
            sw.Stop();
            Console.WriteLine("IsDbNull:{0}",sw.Elapsed);
            sw = Stopwatch.StartNew();
            for (int x = 0; x < count; x++)
            {
                
                var sqlDt = reader.GetSqlDateTime(0);
                DateTime dt;
                if (!sqlDt.IsNull)
                {
                    dt = sqlDt.Value;
                }
                else
                {
                    dt = default;
                }
            }
            sw.Stop();
            Console.WriteLine("GetSqlDateTime:{0}",sw.Elapsed);
            sw = Stopwatch.StartNew();

            for (int x = 0; x < count; x++)
            {
                DateTime dt;
                if (!reader.IsDBNull(0))
                {
                    dt = reader.GetDateTime(0);
                }
                else
                {
                    dt = default;
                }
            }
            sw.Stop();
            Console.WriteLine("IsDbNull:{0}",sw.Elapsed);
            sw = Stopwatch.StartNew();
            for (int x = 0; x < count; x++)
            {

                var sqlDt = reader.GetSqlDateTime(0);
                DateTime dt;
                if (!sqlDt.IsNull)
                {
                    dt = sqlDt.Value;
                }
                else
                {
                    dt = default;
                }
            }
            sw.Stop();
            Console.WriteLine("GetSqlDateTime:{0}",sw.Elapsed);
        }
    }
}

[Wraith2]

I'd expect GetSqlDateTime to have to do more or less the same null checking internally as you're doing externally when calling GetDateTime.

That's my expectation based on the code as well. The first field access on the row will create the SqlBuffer and then it's used by the subsequent access. the Sql* versions should actually do more work because they're creating an additional object rather than passing back data. This will mean that profiles show most of the time spend in IsDbNull because that's where the value is resolved and it changes the Get* call to a simple lookup, however the overall time shared between them should differ only in the extra time taken to setup callstacks and do duplicated arg checking.

The Sql* types are considered legacy currently as far as I'm aware. They're from .net 1.0 when there were no generics and thus no way to express struct nullability, mostly it was for DataTable as far as I know. Because the types are defined in terms of SQL Server's capabilities other providers have to ignore them, provide their own conversions or provider replacements. It seems unlikely that there will be a new type added to the runtime (dotnet/runtime) at this point but it might be possible to add it directly to sqlclient. I'd rather make the check-null-then-maybe-get pattern faster if it's causing a perf problem in comparison to the Sql type version.

Idealogically I think the separation of null checking and field getting is a purer mapping of the distinction between nulls and values in SQL than a nullable clr type, I consider sql nulls "stronger" than language nulls because of their untyped and uncomparable nature.

[bjornbouetsmith]

@Wraith2 I was not aware that the SqlTypes were considered legacy - if that is the case, I would suggest they would be marked as obsolete - and if they are not obsolete, they should really be extended with support for the new data types in SQL Server.

If you can fix the performance issue of IsDbNull - or handle null better in GetXX methods - I am fine by that.

But it seems "strange" that an API was chosen that cannot handle null and requires that caller has to call another method to check for null

the GetSqlXX methods is much easier to use and are more equivalent to what you would expect in my opinion from something that reads from a database.

Imagine SQL server not being able to return NULL from a column and you had to always wrap every column in a ISNULL check before accessing it.

Getting a value back without having to check for null first, will always perform better than having to check for null and then going through almost the same work again just to get the value.

Compare the two methods:

 virtual public SqlDateTime GetSqlDateTime(int i)
        {
            ReadColumn(i);
            return _data[i].SqlDateTime;
        }


        /// <include file='../../../../../../../doc/snippets/Microsoft.Data.SqlClient/SqlDataReader.xml' path='docs/members[@name="SqlDataReader"]/GetDateTime/*' />
        override public DateTime GetDateTime(int i)
        {
            ReadColumn(i);

            DateTime dt = _data[i].DateTime;
            // This accessor can be called for regular DateTime column. In this case we should not throw
            if (_typeSystem <= SqlConnectionString.TypeSystem.SQLServer2005 && _metaData[i].IsNewKatmaiDateTimeType)
            {
                // TypeSystem.SQLServer2005 or less

                // If the above succeeds, then we received a valid DateTime instance, now we need to force
                // an InvalidCastException since DateTime is not exposed with the version knob in this setting.
                // To do so, we simply force the exception by casting the string representation of the value
                // To DateTime.
                object temp = (object)_data[i].String;
                dt = (DateTime)temp;
            }

            return dt;
        }

Almost the same work in both methods - both call ReadColumn and both return data from the SqlBuffer - the difference is that the DateTime property throws an exception if the field is null, where the SqlDateTime returns a struct that can tell you if the field was null - similar to a nullable datetime.

A much better API in my opinion

SqlDateTime takes up

        private bool m_fNotNull;    // false if null
        private int m_day;      // Day from 1900/1/1, could be negative. Range: Jan 1 1753 - Dec 31 9999.
        private int m_time;     // Time in the day in term of ticks

Where Datetime takes up:

       private UInt64 dateData;

So almost similar storage requirements in memory.

A solution could be to return nullable types from a range of new methods, since a Nullable should perform similar to the SqlDateTime, since it has:

 private bool hasValue; 
  internal T value;

i.e. a full range of:

GetNullableXX that would simply do the same as the normal methods, but instead of blindly accessing the "Value" field of SqlBuffer it would first call IsNull, i.e.

override public DateTime? GetNullableDateTime(int i)
{
	ReadColumn(i);
	if (_data[i].IsNull) 
	{
		return (DateTime?)null;
	}
	DateTime dt = _data[i].DateTime;
	// This accessor can be called for regular DateTime column. In this case we should not throw
	if (_typeSystem <= SqlConnectionString.TypeSystem.SQLServer2005 && _metaData[i].IsNewKatmaiDateTimeType)
	{
		// TypeSystem.SQLServer2005 or less

		// If the above succeeds, then we received a valid DateTime instance, now we need to force
		// an InvalidCastException since DateTime is not exposed with the version knob in this setting.
		// To do so, we simply force the exception by casting the string representation of the value
		// To DateTime.
		object temp = (object)_data[i].String;
		dt = (DateTime)temp;
	}

	return dt;
}

[roji]

But it seems "strange" that an API was chosen that cannot handle null and requires that caller has to call another method to check for null

I'd recommend not confusing the discussion around DATETIME2 support and SqlClient's SqlTypes with a general discussion on how ADO.NET behaves around nulls - this is simply how the .NET database API works. I wouldn't consider this behavior around nulls a good reason to introduce a GetSqlDateTime2.

The ADO.NET behavior around nulls was a design decision for the entire API (regardless of what we think about it today) - database nulls are generally not represented by .NET nulls, but rather by DBNull (for generic methods this is a problem). There's some relevant discussion on this in dotnet/runtime#26511, even if that's about a proposed generic ExecuteScalar, rather than readere methods.

In any case, as there isn't a performance aspect here, it's trivial to write a simple GetNullable<T>() extension method yourself, which performs the IsDBNull internally and returns null accordingly.