intellectually constipated

patrick gallucci's technology brain drain

SQL Server 2005 Derived Tables, Common Table Expressions and Indexed Views Query Processing Part II

SQL Server 2005 Derived Tables, Common Table Expressions and Indexed Views Query Processing Part II

Rate This
  • Comments 3

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
  • 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.

  • 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.

  • 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

Page 1 of 1 (3 items)
Leave a Comment
  • Please add 7 and 3 and type the answer here:
  • Post