The following explanation is given in this Microsoft Technical Article:
Why does the first index on a view have to be CLUSTERED and UNIQUE?
It must be UNIQUE to allow easy lookup of records in the view by key value during indexed view maintenance, and to prevent creation of views with duplicates, which would require special logic to maintain. It must be clustered because only a clustered index can enforce uniqueness and store the rows at the same time.
SQL Server uses a system of delta algebra to keep indexed views in step with the base data. It also automatically incorporates view-maintenance query plan operators for each DML query that affects one or more indexed views. Having a unique clustered index on the view greatly simplifies the implementation details.
The current arrangement allows for fixed-shape maintenance operator tree shapes to be incorporated in the base DML query tree, providing orthogonality that also simplifies testing. Ultimately, indexed views could be enhanced one day to support non-unique clustered indexes, but then again all things are possible given unlimited time and boundless resources (neither of which apply to the SQL Server development team as of the time of writing).
For an example showing how complex update query plan building can get, and how easily subtle bugs can creep in, see this example of a bug that occurs with MERGE
and filtered indexes (a feature that has a close connection to indexed views).
It seems to ignore any index I put on it
Unless you're using SQL Server Enterprise Edition (or equivalently, Trial and Developer), you will need to use WITH (NOEXPAND)
on the view reference in order to use it. In fact, even if you are using Enterprise, there are good reasons to use that hint.
Without the hint, the query optimizer (in Enterprise Edition) may make a cost-based choice between using the materialized view or accessing the base tables. Where the view is as large as the base tables, this calculation may favour the base tables.
Another point of interest is that without a NOEXPAND
hint, view references are always expanded to the base query before optimization begins. As optimization progresses, the optimizer may or may not be able to match the expanded definition back to the materialized view, depending on previous optimization activity. This is almost certainly not the case with your simple query, but I mention it for completeness.
So, using the NOEXPAND
table hint is your main option, but you might also think about just materializing the base table keys and the columns needed for ordering in the view. Create a unique clustered index on the combined key columns, then a separate nonclustered index on the ordering columns.
This will reduce the size of the materialized view, and limit the number of automatic updates that must be made to keep the view synchronized with the base tables. Your query can then be written to fetch the top 1 keys in the required order from the view (ideally with NOEXPAND
), then join back to the base tables to fetch any remaining columns using the keys from the view.
Another variation is to cluster the view on the ordering columns and table keys, then write the query to manually fetch the non-view columns from the base table using the keys. The best option for you depends on the broader context. A good way to decide is to test it with the real data and workload.
Basic solution
CREATE VIEW VI_test
WITH SCHEMABINDING
AS
SELECT
t1.PK_ID1,
t1.something1,
t1.somethingelse1,
t2.PK_ID2,
t2.FK_ID1,
t2.something2,
t2.somethingelse2
FROM dbo.TB_test1 t1
INNER JOIN dbo.TB_test2 t2
ON t1.PK_ID1 = t2.FK_ID1;
GO
-- Brute force unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.VI_test
(somethingelse1, somethingelse2, PK_ID1, PK_ID2);
GO
SELECT TOP (1) *
FROM dbo.VI_test WITH (NOEXPAND)
ORDER BY somethingelse1,somethingelse2;
Execution plan:
Using a nonclustered index
-- Minimal unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.VI_test
(PK_ID1, PK_ID2)
WITH (DROP_EXISTING = ON);
GO
-- Nonclustered index for ordering
CREATE NONCLUSTERED INDEX ix
ON dbo.VI_test (somethingelse1, somethingelse2);
Execution plan:
There is a lookup in this plan, but it is only used to fetch a single row.
Minimal Indexed View
ALTER VIEW VI_test
WITH SCHEMABINDING
AS
SELECT
t1.PK_ID1,
t2.PK_ID2,
t1.somethingelse1,
t2.somethingelse2
FROM dbo.TB_test1 t1
INNER JOIN dbo.TB_test2 t2
ON t1.PK_ID1 = t2.FK_ID1;
GO
-- Unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.VI_test
(somethingelse1, somethingelse2, PK_ID1, PK_ID2);
Query:
SELECT TOP (1)
V.PK_ID1,
TT1.something1,
V.somethingelse1,
V.PK_ID2,
TT2.FK_ID1,
TT2.something2,
V.somethingelse2
FROM dbo.VI_test AS V WITH (NOEXPAND)
JOIN dbo.TB_test1 AS TT1 ON TT1.PK_ID1 = V.PK_ID1
JOIN dbo.TB_test2 AS TT2 ON TT2.PK_ID2 = V.PK_ID2
ORDER BY somethingelse1,somethingelse2;
Execution plan:
This shows the table keys being retrieved (a single row fetch from the view clustered index in order) followed by two single-row lookups on the base tables to fetch the remaining columns.
Best Answer
You need to use the NOEXPAND query hint (I'm assuming you're on Standard Edition of SQL Server, as Enterprise won't expand by default) in order for your query to use the
Indexed View
(instead of expanding it to the underlying table). This will solve your first issue.This is how you can use it in your query:
You can see more information in the Query Hints docs by Microsoft. But this is the relevant section to what you're currently experiencing:
This is one of the few query hints you can utilize without worrying about it being bad practice, and actually it's recommended to use for certain reasons as well.
As far as your issue with the "imprecise" error, this is as you guessed it, due to the
Loss
column being of type FLOAT (which effectively is nondeterministic across different CPU architectures). So even casting it won't help you, you'd have to change the underlying type to something that is precise so that value is materialized in the table before you can index it in the view. (E.g. if you can change the data type to DECIMAL, then you'd be able to add that column to your index.)