다음을 통해 공유


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

So in Part II of this article I had created a View so that I could then create an index on this view to see if that was going to give me the expected results. I did create the view, but not the index. So you probably already know. These columns are non-deterministic and as a result the create index statement fails.

CREATE

UNIQUE CLUSTERED INDEX [IDX1] ON [dbo].[vDateSelection]

(

[ID]

ASC

)

WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]

GO

Msg 1963, Level 16, State 1, Line 1

Cannot create index on view "TESTDATABASE.dbo.vDateSelection". The view contains a convert that is imprecise or non-deterministic.

So that ends the questions...for now about indexed views and the effect of the results.

So here comes the results of how this works with no changes to the actual syntax of the query. I will introduce them one a time and then we will take a deep dive into why this behavior works sometimes and not all. And if this is correct behavior, is there the possibility that something that is executed today could fail in the future.

So looking at the scripts from my previous post there are some obvious elements missing form the schema. How about a primary key on each of the tables...and lets make these clustered unique as well.

CREATE

TABLE [dbo].[testTable1](

[ID] [int]

IDENTITY(1,1) NOT NULL,

[LookupTypeID] [int]

NOT NULL,

[LookupValue] [varchar]

(50) NOT NULL,

CONSTRAINT [PK_testTable1] PRIMARY KEY CLUSTERED

(

[ID]

ASC

)

WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

)

ON [PRIMARY]

GO

CREATE

TABLE [dbo].[testTable2](

[ID] [int]

NOT NULL,

[LookupType] [varchar]

(50) NOT NULL,

CONSTRAINT [PK_testTable2] PRIMARY KEY CLUSTERED

(

[ID]

ASC

)

WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

)

ON [PRIMARY]

GO

We can then also add the foreign key constraint for the ID. As you can see I have modified testTable1 by adding an Identity columns for my primary key.

ALTER

TABLE dbo.testTable1 WITH NOCHECK ADD CONSTRAINT

FK_testTable1_testTable2

FOREIGN KEY

(

LookupTypeID

) REFERENCES dbo.testTable2

(

ID

) ON UPDATE NO ACTION

ON DELETE NO ACTION

NOT FOR REPLICATION

GO

ALTER

TABLE dbo.testTable1

NOCHECK CONSTRAINT FK_testTable1_testTable2

GO

With these changes we are close, Well I thought...but I am still getting the same error.

PRINT

'Simple Query WITH FILTER'

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

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

WHERE

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

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

GO

PRINT

'DERIVED TABLE WITH FILTER'

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

.[LookupTypeID] = b.[ID]

AND b.[LookupType] = 'Date'

) AS DateSelection

WHERE

DateSelection

.MonthPart = 1 AND

DateSelection

.YearPart = 2007

GO

PRINT

'COMMON TABLE EXPRESSION WITH FILTER'

GO

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

.[LookupTypeID] = b.[ID]

AND b.[LookupType] = 'Date'

)

SELECT

ID

,MonthPart,YearPart

FROM

DateSelection

WHERE

DateSelection

.MonthPart = 1 AND

DateSelection

.YearPart = 2007

GO

PRINT

'VIEW WITH FILTER'

SELECT

ID

,MonthPart,YearPart

FROM

vDateSelection DateSelection

WHERE

DateSelection

.MonthPart = 1 AND

DateSelection

.YearPart = 2007

GO

The results were the same.

So lets go the full distance now and add the index to the LookupTypeID Field to complete the cycle.

--Add Non Clustered Index.

CREATE

NONCLUSTERED INDEX [IDX_testTable1_01] ON [dbo].[testTable1]

(

[LookupTypeID]

ASC

)

WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]

GO

And BAM. Still the same results. What could be going on here...In the article I will try to understand this myself as well as get some commentary from some insiders...

Stay Tuned.

Comments

  • Anonymous
    January 01, 2007
    This was an interesting break down Patrick.  I enjoyed following  along with you as you worked through this problem.

  • Anonymous
    March 04, 2009
    This is an interesting article. I am building a derived  query as  such in the form below with added joins at each level  to try and build a view, Why;  sql 2005 refused my use of temp tables in view and now wondering if I should have gone the route of CTE? select g.* , from table3, (select f.* from Table1, (select E.* from table2,( select....) G join table4  H on  H.ID=G.ID where G.ID > 5

  • Anonymous
    March 04, 2009
    This is an interesting article. I am building a derived  query as  such in the form below with added joins at each level  to try and build a view, Why;  sql 2005 refused my use of temp tables in view and now wondering if I should have gone the route of CTE? select g.* , from table3, (select f.* from Table1, (select E.* from table2,( select....) G join table4  H on  H.ID=G.ID where G.ID > 5