4

I ask because this is an older feature that I just discovered recently. The documentation mentions DateTime a few times but doesn't explicitly say that other date types are compatible or not.

I have a Data Warehouse that I believe could benefit from this, but 90% of the dates that matter are Date types, not DateTime. I am on SQL 2014, but information on any version would be helpful.

1

Yes, it works for both date and datetime columns. The experiment is rather simple - create a database with the option on, create tables with both types, insert data that satisfies a correlation pattern (like all dates in one table are after the dates in the other table), run some queries that exploit that pattern, and then observe the auto-created stats are the same for both sets of tables.

Create the database and turn on the option:

USE master;
GO
CREATE DATABASE splut;
GO
ALTER DATABASE splut SET DATE_CORRELATION_OPTIMIZATION ON;
GO
USE splut;
GO

Create two tables with correlated date columns, and two tables with correlated datetime columns:

CREATE TABLE dbo.d1(id int primary key, d date);
CREATE TABLE dbo.d2(id int not null foreign key references dbo.d1(id), d date);
CREATE TABLE dbo.d3(id int primary key, d datetime);
CREATE TABLE dbo.d4(id int not null foreign key references dbo.d3(id), d datetime);
GO

Insert some correlated data:

INSERT dbo.d1(id, d) SELECT DISTINCT ABS(object_id),'20190101' FROM sys.all_objects;
INSERT dbo.d2(id, d) SELECT id,DATEADD(DAY, ABS(id)%20, '20190101') FROM dbo.d1;
INSERT dbo.d3(id, d) SELECT DISTINCT ABS(object_id),'20190101' FROM sys.all_objects;
INSERT dbo.d4(id, d) SELECT id,DATEADD(DAY, ABS(id)%20, '20190101') FROM dbo.d3;

Now run a query that might benefit from correlation statistics (though, really, any join will do):

SELECT TOP (10) * FROM dbo.d1 INNER JOIN dbo.d2 ON d1.d = d2.d;
SELECT TOP (10) * FROM dbo.d3 INNER JOIN dbo.d4 ON d3.d = d4.d;

Now, check the stats:

SELECT OBJECT_NAME(s.object_id), s.name, s.stats_id, 
   sc.stats_column_id, c.name, c.system_type_id, t.name
FROM sys.stats AS s 
INNER JOIN sys.stats_columns AS sc
ON s.object_id = sc.object_id
INNER JOIN sys.columns AS c
ON sc.object_id = c.object_id AND sc.column_id = c.column_id
INNER JOIN sys.types AS t
ON c.system_type_id = t.system_type_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p
WHERE s.auto_created = 1 AND OBJECT_NAME(s.object_id) IN (N'd1',N'd2',N'd3',N'd4');

Results on my machine (your stats names will vary) show that stats are created both on (id, date/datetime column of parent) and (date/datetime column of child):

enter image description here

I'll leave it as an exercise for the reader if this holds true for time, datetime2, and datetimeoffset, or if it will also work if one column is date and the other is datetime.

| improve this answer | |

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.