Can I prevent SQL Server from consolidating histogram steps in statistics?

Hi Utwigg,

just to confirm for the RULEOFF and my initial remove filtered stat option I was seeing accurate estimated rowcounts which I should have stated.

Having run your cursor, I can see it changes between accurate/inaccurate with no particular rhyme or reason to it.

A quick refactor to create a surrogate key for the datumId, then store that in the fact appears to give consistently accurate rowcounts, but as to whether that's a design change you want to make is up to you:

USE BugHistogramConsolidation
GO

-- Add an identity column to a table
ALTER TABLE DimDatum ADD rowId INT IDENTITY
GO

-- Make it the primary key
ALTER TABLE DimDatum DROP CONSTRAINT PK_DimDatum
ALTER TABLE DimDatum ADD CONSTRAINT PK_DimDatum PRIMARY KEY ( rowId )
GO

ALTER TABLE FaktMVaR ADD rowId INT
GO

UPDATE f
SET f.rowId = d.rowId
FROM dbo.DimDatum d
        INNER JOIN dbo.FaktMVaR f ON d.DatumID = f.DatumID
GO

DROP INDEX IX_DatumID_Risikofaktor1ID_Risikofaktor2ID ON FaktMVaR
GO
ALTER TABLE FaktMVaR DROP COLUMN DatumId
GO

CREATE CLUSTERED INDEX IX_DatumID_Risikofaktor1ID_Risikofaktor2ID ON FaktMVaR ( rowId, Risikofaktor1ID, Risikofaktor2ID )
GO

--!!TODO put fk back

SELECT *
FROM dbo.[FaktMVaR] f
        INNER JOIN dbo.DimDatum AS dd ON  f.rowId = dd.rowId
                AND dd.LastVersion = 1
WHERE dd.DatumID >= 2012052100 AND dd.DatumID < 2012052200
OPTION ( RECOMPILE )