indexed view with distinct keyword

Question

indexed view with distinct keyword

renuka.popli

SSC Enthusiast

Points: 187
More actions
June 22, 2009 at 6:31 am

#211201

Hi All ,
I have one view which uses the distinct keyword due to that I can not index that view , I have put the alternative to this i.e group by but it still give me error as "imprecise column" for the columns having float as data type , the view definition is as below -
Alter VIEW [dbo].[ForecastViewWithIdsTest]
WITH SCHEMABINDING
AS
SELECT dbo.Forecast.SrcPlant, dbo.InputMasterTable.SrcPlantID, dbo.Forecast.SellPlant, dbo.InputMasterTable.SellPlantID, dbo.Forecast.Region,
dbo.InputMasterTable.RegionID, dbo.Forecast.[Time Key], dbo.Forecast.CustId, dbo.InputMasterTable.InternalCustId, dbo.Forecast.MatId,
dbo.InputMasterTable.InternalMatID, (dbo.Forecast.Vol), dbo.Forecast.Sales, dbo.Forecast.ASP, dbo.Forecast.VolManual, dbo.Forecast.SalesManual,
dbo.Forecast.ASPManual, dbo.Forecast.CurrUnit, dbo.CurrencyUnit.CurrencyID, dbo.Forecast.VolUnit,COUNT_BIG(*) as CountBig
FROM dbo.Forecast INNER JOIN
dbo.InputMasterTable ON dbo.Forecast.SrcPlant = dbo.InputMasterTable.SrcPlant AND dbo.Forecast.SellPlant = dbo.InputMasterTable.SellPlant AND
dbo.Forecast.Region = dbo.InputMasterTable.Region AND dbo.Forecast.CustId = dbo.InputMasterTable.CustID AND
dbo.Forecast.MatId = dbo.InputMasterTable.MatID INNER JOIN
dbo.CurrencyUnit ON dbo.Forecast.CurrUnit = dbo.CurrencyUnit.CurrencyName
GROUP BY dbo.Forecast.SrcPlant, dbo.Forecast.SellPlant, dbo.Forecast.Region, dbo.Forecast.CustId, dbo.Forecast.MatId, dbo.Forecast.Vol, dbo.Forecast.Sales,
dbo.Forecast.ASP, dbo.Forecast.VolManual, dbo.Forecast.SalesManual, dbo.Forecast.ASPManual, dbo.Forecast.CurrUnit, dbo.CurrencyUnit.CurrencyID,
dbo.Forecast.VolUnit, dbo.Forecast.[Time Key], dbo.InputMasterTable.SrcPlantID, dbo.InputMasterTable.SellPlantID, dbo.InputMasterTable.RegionID,
dbo.InputMasterTable.InternalCustId, dbo.InputMasterTable.InternalMatID
GO
The only thing I want is distinct records , how I can achieve this , please help me .
Thanks & Regards
Renuka

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Why the requirement for it to be an indexed view? I'm asking as sometimes it just isn't possible based on the data that is being pulled together.

renuka.popli SSC Enthusiast Points: 187 More actions · Answer 2

Due to performance because more than 10,000 of queries gets fired on this view and each time a view will get executed and takes too much of time

Paul White SSC Guru Points: 150468 More actions · Answer 3

An indexed view isn't going to work for you if you absolutely have to include 'imprecise' data types in your query. 🙁

There's not even a half-decent hack or workaround that I can think of - at least none that avoid making another copy of the data and using 'precise' data types in that copy.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

On a totally different subject, but something you should starting thining about. You need to get away from using three (and possibly) four part naming conventions iin your SELECT lists and such.

Using three and four part naming has been depreciated in SQL Server and may not be supported in future versions. Here is what that means:

SELECT

dbo.tablename.columnname1

FROM

dbo.tablename

WHERE

dbo.tablename.columnname2 = 'somevalue';

may no longer work in future versions. You need to start looking at writing queries and such like this:

SELECT

tablealias.columnname1

FROM

dbo.tablename tablealias -- or dbo.tablename as tablealias

WHERE

tablealias.columnname2 = 'somevalue';