SQL Server 2005 Derived Tables, Common Table Expressions and Indexed Views Query Processing Part I
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.