Compartir a través de

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

  • Artículo

I am calling this Part I because I know I still have some more details to discover about something I learned today. Not sure I fully understand it right now, but by the time I get to Part N, I am confident I will know way to much about the subject. I am not going to spill all the details out right now, I want you to think about what I am going to present and see if you come to the same conclusion that I did.

Here is the script for the tables and data that I am using for my examples.

USE

[tempdb]
GO

IF

EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[testTable1]') AND type in (N'U'))
DROP TABLE [dbo].[testTable1]
GO

IF

EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[testTable2]') AND type in (N'U'))
DROP TABLE [dbo].[testTable2]
GO

CREATE

TABLE [dbo].[testTable1](
[ID] [int] NOT NULL,
[LookupValue] [varchar](50) NOT NULL)
GO

CREATE

TABLE [dbo].[testTable2](
[ID] [int] NOT NULL,
[LookupType] [varchar](50) NOT NULL)
GO

INSERT

INTO [testTable1] VALUES (1, '1')
INSERT INTO [testTable1] VALUES (2, '1/1/2007 12:00:00 AM')
INSERT INTO [testTable1] VALUES (3, 'Test Data 1')
INSERT INTO [testTable1] VALUES (1, '2')
INSERT INTO [testTable1] VALUES (2, '2/1/2008 12:00:00 AM')
INSERT INTO [testTable1] VALUES (3, 'Test Data 2')
INSERT INTO [testTable1] VALUES (1, '3')
INSERT INTO [testTable1] VALUES (2, '3/1/2009 12:00:00 AM')
INSERT INTO [testTable1] VALUES (3, 'Test Data 3')
GO

DECLARE

@count INT
SET @count = 0
    WHILE @count < 10
    BEGIN
    INSERT INTO [testTable1] SELECT * FROM [testTable1]
    SET @count = @count + 1
    END
GO

SELECT

COUNT(*) FROM [testTable1]
INSERT INTO [testTable2] VALUES (1 , 'Integer')
INSERT INTO [testTable2] VALUES (2 , 'Date')
INSERT INTO [testTable2] VALUES (3 , 'Text')
GO

Now executing a simple query, I get the results I was expecting.

--Simple Query

SELECT

      a.[ID] AS LookupValueID

      ,b.[ID] AS LookupTypeID

      ,a.[LookupValue]

      ,b.[LookupType]

FROM

      [dbo].[testTable1] a INNER JOIN

      [dbo].[testTable2] b ON a.[ID] = b.[ID]

LookupValueID LookupTypeID LookupValue LookupType

------------- ------------ -------------------------------------------------- ------------------

1 1 1 Integer

2 2 1/1/2007 12:00:00 AM Date

3 3 Test Data 1 Text

1 1 2 Integer

2 2 2/1/2008 12:00:00 AM Date

3 3 Test Data 2 Text

1 1 3 Integer

2 2 3/1/2009 12:00:00 AM Date

3 3 Test Data 3 Text

1 1 1 Integer

2 2 1/1/2007 12:00:00 AM Date

3 3 Test Data 1 Text

1 1 2 Integer

2 2 2/1/2008 12:00:00 AM Date

3 3 Test Data 2 Text

9216 records found

Now I am adding a filter so that I only return 'Date' values.

--Simple Query 2

SELECT

      a.[ID] AS LookupValueID

      ,b.[ID] AS LookupTypeID

      ,a.[LookupValue]

      ,b.[LookupType]

FROM

      [dbo].[testTable1] a INNER JOIN

      [dbo].[testTable2] b ON

            a.[ID] = b.[ID] AND b.[LookupType] = 'Date'

 

LookupValueID LookupTypeID LookupValue LookupType

------------- ------------ -------------------------------------------------- -------------------

2 2 1/1/2007 12:00:00 AM Date

2 2 2/1/2008 12:00:00 AM Date

2 2 3/1/2009 12:00:00 AM Date

2 2 1/1/2007 12:00:00 AM Date

2 2 2/1/2008 12:00:00 AM Date

2 2 3/1/2009 12:00:00 AM Date

2 2 1/1/2007 12:00:00 AM Date

2 2 2/1/2008 12:00:00 AM Date

3072 records found

So now that I have only dates in my resultset, I want to extract the month and the year...

--Simple Query 3

SELECT

a.[LookupValue]

,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'

LookupValue MonthPart YearPart

-------------------------------------------------- ----------- -----------

1/1/2007 12:00:00 AM 1 2007

2/1/2008 12:00:00 AM 2 2008

3/1/2009 12:00:00 AM 3 2009

1/1/2007 12:00:00 AM 1 2007

2/1/2008 12:00:00 AM 2 2008

3/1/2009 12:00:00 AM 3 2009

1/1/2007 12:00:00 AM 1 2007

3072 records found

This is where it starts to get interesting...

So at this point, I am thinking if I want to limit my results to only dates with the month of 1 and year of 2007, I am pretty much there....

SELECT

a.[LookupValue]

,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'

WHERE

DATEPART(MONTH,CONVERT(SMALLDATETIME,a.[LookupValue])) = 1 AND

DATEPART(YEAR,CONVERT(SMALLDATETIME,a.[LookupValue])) = 2007;

LookupValue MonthPart YearPart

-------------------------------------------------- ----------- -----------

Msg 295, Level 16, State 3, Line 1

Conversion failed when converting character string to smalldatetime data type.

hmmm... 

So I ignore the most obvious ways to solve this problem and proceed down the path of trying different approaches to see why the optimizer might select one path vs another and how depending on what appraoch you take can give you mixed results.

Stay tuned.

 

SQL Server 2005 Derived Tables, Common Table Expressions and Indexed Views Query Processing Part I.sql

Comments