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...
Comments
Anonymous
December 15, 2006
The problem I think lies with how filtering works. It is kindof like use AND vs AndAlso within visual basic. In the select clause (columns that are output), the conversion to shortDateTime only takes place on records that pass the filter criteria. However the conversion within the WHERE clause is not short-circuited. The conversion occurs on all rows in the table. Since only rows with lookup type 2 (Date) can be converted, when the other rows are processed an error occurs. Also, if I run this on SQL 2000 I still get the error. It is possible that SQL 2000 can sometimes use an alternative plan short-circuiting the where clause conversion but you would have to talk to the SQL 2000 team in Microsoft for that.Anonymous
December 29, 2009
I don't see any MonthPart and YearPart function in SQL Server , DateSelection.MonthPart = 1 AND DateSelection.YearPart = 2007 If use Month(DateSelection)=1 and Year(DateSelection)=2007 Then it should work fine.Anonymous
August 16, 2010
CTEs(Common Table Expressions) are one of the beautiful and the most powerful feature of SQL Server and are boon to SQL Server developers. These not only simplifies most of the complex operations in T-SQL but also do a lot more than one can imagine. Follow the link to know the details… www.sqllion.com/.../common-table-expressions-cte