创建columnstore index遇到的错误

Msg 1939, Level 16, State 1, Line 19
Cannot create index on view 'vw_Invoice_chuck_005' because the view is not schema bound.

 

Msg 4512, Level 16, State 3, Procedure vw_Invoice_chuck_005, Line 5 [Batch Start Line 9]
Cannot schema bind view 'dbo.vw_Invoice_chuck_005' because name 'vw_Invoice_chuck_002_InvoiceAmounts' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

 

Msg 1940, Level 16, State 2, Line 16
Cannot create index on view 'dbo.vw_Invoice'. It does not have a unique clustered index.

 

Msg 1939, Level 16, State 1, Line 19
Cannot create index on view 'vw_Invoice' because the view is not schema bound.

 

Cannot create index on view 'View_Table_Name' because the view is not schema bound

回答1

There are a number of restrictions on indexed views: no subqueries, no unions, no outer joins, etc. See this article for more details. But for your case, you simply need to create the view with schema binding.

CREATE VIEW VW_Table_Name WITH SCHEMABINDING
AS
SELECT Col1,Col2,Col3 FROM Table_Name 
GO

 

回答2

Because you are trying to create an Indexed View or Materialized View. Its mandatory for a view to have "WITH SCHEMABINDING" option if you are creating a Clustered Index on top of it.

A view is nothing but a stored query, if you are going to create an index on it, then the index is going to use that query and execute it on that table, in this case you have to make sure that the table does not change underneath. Thus by enforcing this constraint SQL Server makes sure everything remains in sync.

 

Indexing views with a CTE

新的错误

Msg 10137, Level 16, State 1, Line 19
Cannot create index on view "" because it references common table expression "FundedAmounts". Views referencing common table expressions cannot be indexed. Consider not indexing the view, or removing the common table expression from the view definition.

 

回答1

1. You can't index a view with a CTE. Even though the view can have SCHEMABINDING. Think of it this way. In order to index a view, it must meet two conditions (and many others): (a) that it has been created WITH SCHEMABINDING and (b) that it does not contain a CTE. In order to schemabind a view, it does not need to meet the condition that it does not contain a CTE.

2. I'm not convinced there is a scenario where a view has a CTE and will benefit from being indexed. This is peripheral to your actual question, but my instinct is that you are trying to index this view to magically make it faster. An indexed view isn't necessarily going to be any faster than a query against the base tables - there are restrictions for a reason, and there are only particular use cases where they make sense. Please be careful to not just blindly index all of your views as a magic "go faster" button. Also remember that an indexed view requires maintenance. So it will increase the cost of any and all DML operations in your workload that affect the base table(s).

3. Schemabinding is not just for indexing views. It can also be used on things like UDFs to help persuade determinism, can be used on views and functions to prevent changes to the underlying schema, and in some cases it can improve performance (for example, when a UDF is not schema-bound, the optimizer may have to create a table spool to handle any underlying DDL changes). So please don't think that it is weird that you can schema-bind a view but you can't index it. Indexing a view requires it, but the relationship is not mutual.

For your specific scenario, I recommend this:

CREATE VIEW dbo.PatClassCounts
WITH SCHEMABINDING
AS
  SELECT pat_id, drug_class, 
      COUNT_BIG(*) AS counts
    FROM dbo.rx
    GROUP BY pat_id, drug_class;
GO
CREATE UNIQUE CLUSTERED INDEX ON dbo.PatClassCounts(pat_id, drug_class);
GO
CREATE VIEW dbo.ClaimSums
WITH SCHEMABINDING
AS
  SELECT pat_id, 
    SUM(c.std_cost) AS [Healthcare Costs], 
    COUNT_BIG(*) AS counts
  FROM dbo.claims
  GROUP BY pat_id;
GO
CREATE UNIQUE CLUSTERED INDEX ON dbo.ClaimSums(pat_id);
GO

Now you can create a non-indexed view that just does a join between these two indexed views, and it will utilize the indexes (you may have to use NOEXPAND on a lower edition, not sure):

CREATE VIEW dbo.OriginalViewName
WITH SCHEMABINDING
AS
    SELECT p.pat_id, p.drug_class, p.counts, c.[Healthcare Costs]
      FROM dbo.PatClassCounts AS p
      INNER JOIN dbo.ClaimSums AS c
      ON p.pat_id = c.pat_id;
GO

Now, this all assumes that it is worthwhile to pre-aggregate this information - if you run this query infrequently, but the data is modified a lot, it may be better to NOT create indexed views.

Also note that the SUM(std_cost) from the ClaimSums view will be the same for every pat_id + drug_class combination, since it's only aggregated to pat_id. I guess there might be a drug_class in the claims table that should be part of the join criteria too, but I'm not sure. If that is the case, I think this could be collapsed to a single indexed view.

 

作者:Chuck Lu    GitHub