SQL Server 2005 Derived Tables, Common Table Expressions and Indexed Views Query Processing Part II
I have now started the process of evaluating the other possible ways of executing the query. These include using a derived table, Common Table Expression and an Indexed View. My intent is to not use any functions that could twist my results. Below is the script that I used and the result.
SELECT ID,MonthPart,YearPart FROM
(
SELECT
a.[ID] AS ID
,DATEPART(MONTH,CONVERT(SMALLDATETIME,a.[LookupValue])) AS MonthPart
,DATEPART(YEAR,CONVERT(SMALLDATETIME,a.[LookupValue])) AS YearPart
FROM
[dbo].[testTable1] a INNER JOIN
[dbo].[testTable2] b ON
a.[ID] = b.[ID]
AND b.[LookupType] = 'Date'
) AS DateSelection
GO
ID MonthPart YearPart
----------- ----------- -----------
2 1 2007
2 2 2008
2 3 2009
2 1 2007
2 2 2008
3072 Records Found
But again as I try to filter the data, the compilation error occurs.
SELECT ID,MonthPart,YearPart FROM
(
SELECT
a.[ID] AS ID
,DATEPART(MONTH,CONVERT(SMALLDATETIME,a.[LookupValue])) AS MonthPart
,DATEPART(YEAR,CONVERT(SMALLDATETIME,a.[LookupValue])) AS YearPart
FROM
[dbo].[testTable1] a INNER JOIN
[dbo].[testTable2] b ON
a.[ID] = b.[ID]
AND b.[LookupType] = 'Date'
) AS DateSelection
WHERE
DateSelection.MonthPart = 1 AND
DateSelection.YearPart = 2007
GO
ID MonthPart YearPart
----------- ----------- -----------
Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.
I will now try to use a CTE and then an indexed view. They examples will be shown below, all with the same results. After this we will explore why this is happening and how without any changes to the query, these same statements will compile and execute successfully.
Here is the example with the CTE.
WITH DateSelection (ID,MonthPart,YearPart)
AS
(
SELECT
a.[ID] AS ID
,DATEPART(MONTH,CONVERT(SMALLDATETIME,a.[LookupValue])) AS MonthPart
,DATEPART(YEAR,CONVERT(SMALLDATETIME,a.[LookupValue])) AS YearPart
FROM
[dbo].[testTable1] a INNER JOIN
[dbo].[testTable2] b ON
a.[ID] = b.[ID]
AND b.[LookupType] = 'Date'
)
SELECT
ID,MonthPart,YearPart
FROM
DateSelection
GO
COMMON TABLE EXPRESSION NO FILTER
ID MonthPart YearPart
----------- ----------- -----------
2 1 2007
2 2 2008
2 3 2009
2 1 2007
2 2 2008
2 3 2009
2 1 2007
2 2 2008
3072 Records returned
But again with the filter the same error.
WITH DateSelection (ID,MonthPart,YearPart)
AS
(
SELECT
a.[ID] AS ID
,DATEPART(MONTH,CONVERT(SMALLDATETIME,a.[LookupValue])) AS MonthPart
,DATEPART(YEAR,CONVERT(SMALLDATETIME,a.[LookupValue])) AS YearPart
FROM
[dbo].[testTable1] a INNER JOIN
[dbo].[testTable2] b ON
a.[ID] = b.[ID]
AND b.[LookupType] = 'Date'
)
SELECT
ID,MonthPart,YearPart
FROM
DateSelection
WHERE
DateSelection.MonthPart = 1 AND
DateSelection.YearPart = 2007
GO
COMMON TABLE EXPRESSION WITH FILTER
ID MonthPart YearPart
----------- ----------- -----------
Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.
Here is the example using the Indexed View.
SET
NUMERIC_ROUNDABORT
OFF;
SET
ANSI_PADDING,
ANSI_WARNINGS,
CONCAT_NULL_YIELDS_NULL,
ARITHABORT,
QUOTED_IDENTIFIER,
ANSI_NULLS
ON;
GO
CREATE VIEW vDateSelection
WITH SCHEMABINDING
AS
SELECT
a.[ID] AS ID
,DATEPART(MONTH,CONVERT(SMALLDATETIME,a.[LookupValue])) AS MonthPart
,DATEPART(YEAR,CONVERT(SMALLDATETIME,a.[LookupValue])) AS YearPart
FROM
[dbo].[testTable1] a INNER JOIN
[dbo].[testTable2] b ON
a.[ID] = b.[ID]
AND b.[LookupType] = 'Date'
GO
SELECT
ID,MonthPart,YearPart
FROM
vDateSelection DateSelection
WHERE
DateSelection.MonthPart = 1 AND
DateSelection.YearPart = 2007
GO
ID MonthPart YearPart
----------- ----------- -----------
Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.
So here is where I start to wonder why this is not working, and furthermore, how can this be working on SQL Server 2000? Is this a bug? Has the Query optimizer been rewritten so that this would be valid in 2000 and not 2005? So the last test I did was to re-run the script with SQL 2005 in 80 compatibility mode and see if this had any affect on the query. The answer to that question was no. I get the exact same error.
So now is the time that I take a deeper dive and try to figure out the what and the why.
Stayed tuned...