CODEWITHSUNDEEP
sql-server
cshah
cshah

Reputation: 325

SQL Server cannot create indexed view

I am using SQL Server 2005 SP3 Standard Edition but cannot create an indexed view (trying to create a clustered index on MeasurementDataID). I know standard edition does not use indexed views in a query unless noexpand query hint is used.

Error message -- because view reference an unknown value sum aggregate of nullable expression

Also, why do I need count_big(*) ??

CREATE VIEW [CPSync].[vwGetColorChars] WITH SCHEMABINDING
AS
    SELECT MC.MeasurementDataID
       , SUM(CASE WHEN CC.ColorCharID = 11 THEN [ColorCharsValue] END ) AS [Gray_a*] 
       , SUM(CASE WHEN CC.ColorCharID = 12 THEN [ColorCharsValue] END) AS [Gray_b*]
       , SUM(CASE WHEN CC.ColorCharID = 4 THEN [ColorCharsValue] END) AS [ΔF]
       , SUM(CASE WHEN CC.ColorCharID = 9 THEN [ColorCharsValue] END) As DotGain
       , SUM(CASE WHEN CC.ColorCharID = 10 THEN     [ColorCharsValue] END) As [Gray_L*]
       , SUM(CASE WHEN CC.ColorCharID = 13 THEN     [ColorCharsValue] END) As [G7NPDC]  
       , COUNT_BIG(*) AS CountBig 
FROM CPSync.Measurement 
INNER JOIN CPSync.MeasurementData ON  Measurement.MeasurementID = MeasurementData.MeasurementID
INNER JOIN CPSync.MeasuredColorChar As MC ON MC.MeasurementDataID = MeasurementData.MeasurementDataID
INNER JOIN CPSync.ColorChar AS CC ON MC.ColorCharsID =CC.ColorCharID
Group By MC.MeasurementDataID

Upvotes: 0

Views: 2454

Answers (2)

A-K
A-K

Reputation: 17090

Alternatively, you can replace this:

SELECT MC.MeasurementDataID
   , SUM(CASE WHEN CC.ColorCharID = 11 THEN [ColorCharsValue] END ) AS [Gray_a*] 
   , SUM(CASE WHEN CC.ColorCharID = 12 THEN [ColorCharsValue] END) AS [Gray_b*]
   , SUM(CASE WHEN CC.ColorCharID = 4 THEN [ColorCharsValue] END) AS [ΔF]
   , SUM(CASE WHEN CC.ColorCharID = 9 THEN [ColorCharsValue] END) As DotGain
   , SUM(CASE WHEN CC.ColorCharID = 10 THEN     [ColorCharsValue] END) As [Gray_L*]
   , SUM(CASE WHEN CC.ColorCharID = 13 THEN     [ColorCharsValue] END) As [G7NPDC]  
   , COUNT_BIG(*) AS CountBig

with this simpler code:

SELECT MC.MeasurementDataID,
   , CC.ColorCharID
   , SUM([ColorCharsValue] ) AS [yourColumnName] 
   , COUNT_BIG(*) AS CountBig

and pivot it when you select from your indexed view

Upvotes: 0

gbn
gbn

Reputation: 432712

Change the lines like 

SUM(CASE WHEN CC.ColorCharID = 11 THEN [ColorCharsValue] END ) AS [Gray_a*]

to 

SUM(CASE WHEN CC.ColorCharID = 11 THEN [ColorCharsValue] ELSE 0 END ) AS [Gray_a*]

COUNT_BIG: if allows the materialised rows to be tracked. Like the "uniquifier" in a non-unqiue clustered index. COUNT_BIG allows for 32 bit overflow for large views.

Upvotes: 6

Related Questions