Yeah, I know about the criteria, which leads me to a couple questions:
SQL BOL:
- The ANSI_NULLS option must have been set to ON for the execution of all
CREATE TABLE statements that create tables referenced by the view.
How do I know if ANSI_NULLS was on for the creation of all the referenced
tables? The property "ExecIsAnsiNullsOn" is only valid for stored
procedures, triggers, and views.
I am fairly certain that this is done, because I do all my table
modifications through query analyzer and my connection properties always are
set:
concat_null_yields_null
ansi_nulls
ansi_null_dflt_on
ansi_padding
ansi_warnings
quoted_identifier
The database property IsNumericRoundAbortEnabled (NUMERIC_ROUNDABORT) is
disabled
View does use GROUP BY, but index only references grouped columns
Index does not include any text, ntext, or image columns
So, assuming that all this is correct, can you explain why I am having to
force the query optimizer to use the clustered index? Below is my view, and
the query I am using to retrieve values from it
Brandon
--
"In the beginning the universe was created. This has made a lot of people
very angry, and has been widely regarded as a bad move." - Douglas Noel
Adams (1952-2001)
[Please remove "nospam_" from email address to reply.]
<-------- SQL Stuff -------->
CREATE VIEW dbo.ResourceTotals (Resource, [Date], ResourceType, DateType,
ChargeAmount, ChargeCount, PvtPayAmount, PvtPayCount, InsPayAmount,
InsPayCount, CrAdjAmount, CrAdjCount, DrAdjAmount, DrAdjCount, TranCount)
WITH SCHEMABINDING
AS SELECT CASE WHEN pass.x IN (1, 2) THEN PT_SProvKey ELSE PV_PrimProvKey
END AS Resource,
CASE WHEN pass.x IN (1, 3) THEN PT_StartDate ELSE PT_EDate END AS
[Date],
CAST(CASE WHEN pass.x IN (1, 2) THEN 'S' ELSE 'I' END AS char(1))
AS ResourceType,
CAST(CASE WHEN pass.x IN (1, 3) THEN 'S' ELSE 'E' END AS char(1))
AS DateType,
SUM(CASE ISNULL(PT_CrType, PT_DrType) WHEN 'c' THEN PT_Amount ELSE
0 END) AS ChargeAmount,
SUM(CASE ISNULL(PT_CrType, PT_DrType) WHEN 'c' THEN 1 ELSE 0 END)
AS ChargeCount,
SUM(CASE ISNULL(PT_CrType, PT_DrType) WHEN 'p' THEN PT_Amount ELSE
0 END) AS PvtPayAmount,
SUM(CASE ISNULL(PT_CrType, PT_DrType) WHEN 'p' THEN 1 ELSE 0 END)
AS PvtPayCount,
SUM(CASE ISNULL(PT_CrType, PT_DrType) WHEN 'i' THEN PT_Amount ELSE
0 END) AS InsPayAmount,
SUM(CASE ISNULL(PT_CrType, PT_DrType) WHEN 'i' THEN 1 ELSE 0 END)
AS InsPayCount,
SUM(CASE PT_CrType WHEN 'a' THEN PT_Amount ELSE 0 END) AS
CrAdjAmount,
SUM(CASE PT_CrType WHEN 'a' THEN 1 ELSE 0 END) AS CrAdjCount,
SUM(CASE PT_DrType WHEN 'a' THEN PT_Amount ELSE 0 END) AS
DrAdjAmount,
SUM(CASE PT_DrType WHEN 'a' THEN 1 ELSE 0 END) AS DrAdjCount,
COUNT_BIG(*) AS TranCount
FROM dbo.TPatient_Visit
JOIN dbo.TPatient_Transaction ON PT_VisitKey = PV_Key
AND PT_PostedDate IS NOT NULL
JOIN dbo.TPass AS pass ON pass.x BETWEEN 1 AND 4
GROUP BY CASE WHEN pass.x IN (1, 2) THEN PT_SProvKey ELSE PV_PrimProvKey
END,
CASE WHEN pass.x IN (1, 3) THEN PT_StartDate ELSE PT_EDate END,
CAST(CASE WHEN pass.x IN (1, 2) THEN 'S' ELSE 'I' END AS
char(1)),
CAST(CASE WHEN pass.x IN (1, 3) THEN 'S' ELSE 'E' END AS
char(1))
GO
CREATE UNIQUE CLUSTERED INDEX
IXUC__ResourceTotals__Resource_Date_ResourceType_DateType ON
dbo.ResourceTotals (Resource, [Date], ResourceType, DateType) ON [PRIMARY]
GO
-- This query refuses to use clustered index
-- Query runs in 6 seconds
SELECT *
FROM ResourceTotals
WHERE Resource = 11
AND [Date] = '7/26/2002'
AND ResourceType = 'i'
AND DateType = 'S'
-- Query plan for previous query
|--Stream Aggregate(DEFINE:([Expr1020]=Count(*), [Expr1010]=SUM(If
(isnull([TPatient_Transaction].[PT_CrType],
[TPatient_Transaction].[PT_DrType])='c') then
[TPatient_Transaction].[PT_Amount] else 0.00), [Expr1011]=SUM(If
(isnull([TPatient_Transaction].[
|--Parallelism(Gather Streams)
|--Filter(WHERE:((([Expr1006]=11 AND [Expr1007]='Jul 26 2002
12:00AM') AND [Expr1008]='i') AND [Expr1009]='S'))
|--Compute Scalar(DEFINE:([Expr1006]=If ([TPass].[x]=2 OR
[TPass].[x]=1) then [TPatient_Transaction].[PT_SProvKey] else
[TPatient_Visit].[PV_PrimprovKey], [Expr1007]=If ([TPass].[x]=3 OR
[TPass].[x]=1) then [TPatient_Transaction].[PT_Start
|--Nested Loops(Inner Join)
|--Hash Match(Inner Join,
HASH:([TPatient_Visit].[PV_Key])=([TPatient_Transaction].[PT_VisitKey]))
| |--Parallelism(Repartition Streams,
PARTITION COLUMNS:([TPatient_Visit].[PV_Key]))
| | |--Index
Scan(OBJECT:([Development].[dbo].[TPatient_Visit].[AI_PV_PrimProvKey]))
| |--Parallelism(Repartition Streams,
PARTITION COLUMNS:([TPatient_Transaction].[PT_VisitKey]))
| |--Clustered Index
Scan(OBJECT:([Development].[dbo].[TPatient_Transaction].[PK__TPatient_Transa
ction]), WHERE:([TPatient_Transaction].[PT_PostedDate]<>NULL))
|--Table Spool
|--Clustered Index
Seek(OBJECT:([Development].[dbo].[TPass].[PK__TPass]), SEEK:([TPass].[x] >=
1 AND [TPass].[x] <= 4) ORDERED FORWARD)
-- I have to force SQL Server to not expand the view. This query runs in
-- Query runs in .01 seconds
SELECT *
FROM ResourceTotals WITH (NOEXPAND)
WHERE Resource = 11
AND [Date] = '7/26/2002'
AND ResourceType = 'i'
AND DateType = 'S'
-- Query plan for previous query
|--Clustered Index
Seek(OBJECT:([Development].[dbo].[ResourceTotals].[IXUC__ResourceTotals__Res
ource_Date_ResourceType_DateType]), SEEK:([ResourceTotals].[Resource]=11 AND
[ResourceTotals].[Date]='Jul 26 2002 12:00AM' AND
[ResourceTotals].[ResourceType]=
by