T-SQL: Can't Convert Dates in Indexed Views

Just thought i'd drop here an example of a view and how using CONVERT on a date in a view can invalidate it as a candidate to be indexed. Some CONVERT styles are classified as non-deterministic and it just so happens that the default style is one of these so if you use the CONVERT function in your views, you'll need to ensure you specify a valid style.

This is run on SQL2008.

CREATE DATABASE TEST
USE Test
GO
-- set up the sample DDL
CREATE TABLE dbo.a (Id INT NOT NULL PRIMARY KEY, dt DATETIME)
CREATE TABLE dbo.b (Id INT NOT NULL, sdt VARCHAR(100))
GO
-- and add some sample DML
INSERT INTO dbo.a SELECT 1, GETDATE()
INSERT INTO dbo.a SELECT 2, GETDATE()
INSERT INTO dbo.a SELECT 3, GETDATE()
INSERT INTO dbo.b SELECT Id, CAST(dt AS VARCHAR) FROM dbo.a
GO
-- create a basic view which is able to be indexed
CREATE VIEW dbo.vwAB
WITH SCHEMABINDING
AS
SELECT a.Id, a.dt, b.sdt
FROM dbo.a
INNER JOIN dbo.b
ON a.Id = b.Id
GO
-- this works
CREATE UNIQUE CLUSTERED INDEX CIDX_vwAB_Id
ON dbo.vwAB (Id);
GO
-- change the view to add a convert
ALTER VIEW dbo.vwAB
WITH SCHEMABINDING
AS
SELECT a.Id, a.dt, CONVERT(DATE, b.sdt) AS sdt
FROM dbo.a
INNER JOIN dbo.b
ON a.Id = b.Id
GO
-- now this doesn't work as the CONVERT is non deterministic!!
CREATE UNIQUE CLUSTERED INDEX CIDX_vwAB_Id
ON dbo.vwAB (Id);
GO
-- change the view to use a deterministic CONVERT style
ALTER VIEW dbo.vwAB
WITH SCHEMABINDING
AS
SELECT a.Id, a.dt, CONVERT(DATE, b.sdt,103) AS sdt
FROM dbo.a
INNER JOIN dbo.b
ON a.Id = b.Id
GO
-- now this works as we're using a deterministic CONVERT style!!
CREATE UNIQUE CLUSTERED INDEX CIDX_vwAB_Id
ON dbo.vwAB (Id);
GO


BOL has an article which explains what the requirements are for using Indexed Views here.

And you can view more on the CONVERT function and which styles are deterministic here.