View: dbo.Employee_Taxable_Wage_Adjustment_Date_View
View definition
--Employee_Taxable_Wage_Adjustment_Date_View is used to create a materialized
--SMALLDATETIME version of the adjustment_date column of Employee_Taxable_Wage_Adjustment.
--There are instances where Employee_Taxable_Wage_Adjustment is unioned with tables such
--as Bank_Transaction and adjustment_date aligns with transaction_date. If adjustment_date is
--DATETIME and transaction_date is SMALLDATETIME we run into an issue. In this case the query
--optimizer converts transaction_date to DATETIME making it non-sargable and disallowing the
--query optimizer from using any statistics.
--
--Eventually adjustment_date and transaction_date should be migrated to be of type DATE.
--WI 1310935 deals with the clean-up of this indexed view once it is no longer needed.
CREATE VIEW dbo.Employee_Taxable_Wage_Adjustment_Date_View
WITH SCHEMABINDING
AS
SELECT
etwa.employee_taxable_wage_adjustment_KEY
, CAST(etwa.adjustment_date AS SMALLDATETIME) AS adjustment_date_cast
FROM dbo.Employee_Taxable_Wage_Adjustment AS etwa;